[RESOLVED] Looping through sheets problems and setting of variables - vba

I am doing this simple routine - checking for values that contain prefix IK column A in all sheets except the IK ouput which is the ouput sheet only.

I am having difficulty setting the varaible in the correct way and getting it to work.

Please see comments which explains some of the difficulties.

Can anyone help debug??

VB Code:

Sub IKlist()
 
 
Dim wksSheet As Worksheet
Dim i As Integer
Dim thiscell As String
Dim Filelength As Integer
Dim obook As Workbook
Dim xlapp As Excel.Application
Dim j As Integer
 
' CREATE OUTPUT SHEET IK Output
 
'Count Worksheets
sheetcount = Worksheets.Count
 
'Add worksheets after the last sheet
Worksheets.Add After:=Sheets(sheetcount)
 
'Count Worksheets
sheetcount = Worksheets.Count
 
'Name the sheet and add the number of sheets
Worksheets(sheetcount).Name = "IK output"
 
'set varaibles to work with the active workbook and any sheet except the ouput sheet
 
Set xlapp = Excel.Application    'work with active excel application
Set obook = xlapp.ActiveWorkbook    'work with active workbook - not specific
Set wksSheet = ??????????????  'set wksheet to be any worksheet in the active workbook. This is because I am looping through each worksheet
 
' Loop through each worksheet in active workbook - not a specific work book- looking for values that contain prefix IK  and copying it to the IK output sheet
 
' The output sheet  is the ouput sheet only - therefore we should not loop through this sheet - its only used for poulating results
 
 
Filelength = wksSheet.usedrange.Rows.Count
   j = 1
For Each wksSheet In obook.Worksheets
 
    For i = 1 To Filelength
    thiscell = Cells(i, 1)
    If Left(thiscell, 2) = "IK" Then
    Worksheets("IK output").Cells(j, 1).Value = Cells(i, 1).Value
    End If
    j = j + 1
    Next i
Next wksSheet
 
 
 
End Sub

A couple of suggestions:

You can set wksSheet to be Worksheets(1) - there is always a first sheet in any workbook.

If you move the line: Filelength = wkssheet.usedrange... inside the For loop then it will recalculate this for each sheet, which presumably is what you need....


zaza

I have made the changes below - it runs for about 2 seconds but then stops. don't think its looping through any of the sheets, whats wrong buddy?? :confused:
All its doing at mow is creating the sheet.

Also the sheet IK ouput gets created previously - its not meant to loop to this sheet - this is the output only.

VB Code:

Sub IKlist()
 
 
Dim wksSheet As Worksheet
Dim i As Long
Dim thiscell As String
Dim Filelength As Integer
Dim obook As Workbook
Dim xlapp As Excel.Application
Dim j As Long
 
' CREATE OUTPUT SHEET IK Output
 
'Count Worksheets
sheetcount = Worksheets.Count
 
'Add worksheets after the last sheet
Worksheets.Add After:=Sheets(sheetcount)
 
'Count Worksheets
sheetcount = Worksheets.Count
 
'Name the sheet and add the number of sheets
Worksheets(sheetcount).Name = "IK output"
 
Set xlapp = Excel.Application    'work with active excel application
Set obook = xlapp.ActiveWorkbook    'work with active workbook - not specific
Set wksSheet = obook.Worksheets(1)  'set wksheet to be any worksheet in the active workbook. This is because I am looping through each worksheet
 
' Loop through each worksheet in active workbook - not a specific work book- looking for values that contain prefix IK  and copying it to the IK output sheet
 
 
   j = 1
For Each wksSheet In obook.Worksheets
Filelength = wksSheet.usedrange.Rows.Count
    For i = 1 To Filelength
    
    thiscell = Cells(i, 1)
    If Left(thiscell, 2) = "IK" Then
    Worksheets("IK output").Cells(j, 1).Value = Cells(i, 1).Value
        j = j + 1
    Else
    End If
    Next i
Next wksSheet
 
 
 
End Sub

Give this a try

VB Code:

Dim k as Integer
 
For k = 1 to Sheets.Count - 1
 
Set wksSheet = Sheets(k)
 
Filelength = wksSheet.usedrange.Rows.Count
    For i = 1 To Filelength
    
    thiscell = wksSheet.Cells(i, 1)
    If Left(thiscell, 2) = "IK" Then
    Worksheets("IK output").Cells(j, 1).Value = thiscell.Value
        j = j + 1
    Else
    End If
    Next i
Next k

ska

ska67can's code may work, you could also try the following which has
been modified from your code..

Notes
I am assumeing that you are running this in the Workbook you want to
have the results displayed in. Hence I have added ThisWorkbook. around the place.

VB Code:

Sub IKlist()
Dim wksSheet As Worksheet
Dim i As Long
Dim Filelength As Integer
Dim j As Long
 
' CREATE OUTPUT SHEET IK Output
 
'Count Worksheets
sheetcount = ThisWorkbook.Worksheets.Count
 
'Add worksheets after the last sheet
ThisWorkbook.Worksheets.Add After:=Sheets(sheetcount)
 
' ReCount Worksheets, Could have used [U]sheetcount=sheetcount+1[/U] instead
sheetcount = ThisWorkbook.Worksheets.Count
 
'Name the sheet
ThisWorkbook.Worksheets(sheetcount).Name = "IK output"
 
' Loop through each worksheet in active workbook
' not a specific work book- looking for values that contain
' prefix IK and copying it to the IK output sheet
 
 
   j = 1
For Each wksSheet In ThisWorkbook.Worksheets
    If wksSheet.Name <> "IK output" Then ' Only work on sheets not named IK Output
        Filelength = wksSheet.usedrange.Rows.Count
        For i = 1 To Filelength ' Run through all used rows 
            If Left(wksSheet.Cells(i, 1),2) = "IK" Then
              ' If the cell in the A Column Starts with IK then add to output.
               Thisworkbook.Worksheets("IK output").Cells(j, 1).Value = wksSheet.Cells(i, 1).Value
                j = j + 1
           End If
        Next i
    End If ' wksSheet.Name <> "IK output"
Next wksSheet
 
End Sub

The following are not needed, ThisWorkbook. replaces the obook
We do not need to set wksSheet as that is taken care of in the For Each loop

VB Code:

Set xlapp = Excel.Application    'work with active excel application
Set obook = xlapp.ActiveWorkbook    'work with active workbook - not specific
Set wksSheet = obook.Worksheets(1)  'set wksheet to be any worksheet in the active workbook.
'  This is because I am looping through each worksheet

Hi,

Thanks. A slight problem has occured.

It produces error. Sheetcount does not seem to be counting right.

The sheet can vary - but there are 4 sheet at the mow.

It's increasing by 1 or 2 each time the script is run.

The below error apprears in the line in bold below.

Run-time error 1004.

Cannot rename a sheet to the same name as another sheet, a referenced object library or
a workbook referenced by viusal basic

VB Code:

Sub IKlist()
Dim wksSheet As Worksheet
Dim i As Long
Dim Filelength As Integer
Dim j As Long
Dim sheetcount As Integer
 
' CREATE OUTPUT SHEET IK Output
 
 
'Count Worksheets
sheetcount = ThisWorkbook.Worksheets.Count
 
'Add worksheets after the last sheet
ThisWorkbook.Worksheets.Add After:=ThisWorkbook.Sheets(sheetcount)
 
' ReCount Worksheets, Could have used sheetcount=sheetcount+1 instead
sheetcount = ThisWorkbook.Worksheets.Count
 
'Name the sheet
[B]ThisWorkbook.Worksheets(sheetcount).Name = "IK output"[/B]
 
 
 
' Loop through each worksheet in active workbook
' not a specific work book- looking for values that contain
' prefix IK and copying it to the IK output sheet
 
 
   j = 1
For Each wksSheet In ThisWorkbook.Worksheets
    If wksSheet.Name <> "IK output" Then ' Only work on sheets not named IK Output
        Filelength = wksSheet.usedrange.Rows.Count
        For i = 1 To Filelength ' Run through all used rows
            If Left(wksSheet.Cells(i, 1), 2) = "IK" Then
              ' If the cell in the A Column Starts with IK then add to output.
               ThisWorkbook.Worksheets("IK output").Cells(j, 1).Value = wksSheet.Cells(i, 1).Value
                j = j + 1
           End If
        Next i
    End If ' wksSheet.Name <> "IK output"
Next wksSheet
 
End Sub

Are you just printing or exporting sheet IK output or do you need to keep it after you've run your code?

ska

I need to keep it or use it after I run my code.

I just need an output sheet - don't think the worksheet code is working as well - because I added the sheet manually and then run just the sheet element - but its not working.:

When I hover over the this ThisWorkbook below, it says object variable or with block variable not set.

For Each wksSheet In ThisWorkbook.Worksheets

Cheers

Any ideas, torc code is not working at all. Appreciate if someone can help, fix it. please see the comment above. :confused:

Quote:

---

Originally Posted by gphillips

The below error apprears in the line in bold below.

Run-time error 1004.

Cannot rename a sheet to the same name as another sheet, a referenced object library or
a workbook referenced by visual basic

---

I think that the problem is occurring because the code that I wrote did not check to see if the Worksheet already existed.

The function below will check to see if the named worksheet already exists.
Note : I convert the name of the worksheet I am testing and the name it is being
checked against to Upper Case as the Worksheet names in Excel are not
Case Sensistive.

VB Code:

Function WkShtExists(WName As String) As Boolean
' Returns True if a worksheet WName exists in Workbook TWbk
Dim Counter As Long
Dim WkSheet As Worksheet
    WkShtExists = False
    For Each WkSheet In ThisWorkbook.Worksheets
        If UCase(WName) = UCase(WkSheet.Name) Then ' Worksheet Names are not Case Sensitive
            WkShtExists = True
            Exit For
        End If
    Next WkSheet
End Function

Using this we can then modify your IKList sub, although You will need to
decide how you want to handle the situation.

In the modified code below I am assuming that you want to clear the contents of the IK Output sheet if it already exists.

VB Code:

Sub IKlist()
Dim wksSheet As Worksheet
Dim i As Long
Dim Filelength As Integer
Dim j As Long
Dim sheetcount As Integer
Const IKListSheet = "IK Output"
 
    If WkShtExists(IKListSheet) Then
        ThisWorkbook.Worksheets(IKListSheet).Cells.ClearContents
    Else
        ' Create Output Sheet  IK Output
        ' Count Worksheets
        sheetcount = ThisWorkbook.Worksheets.Count
         'Add worksheets after the last sheet
         ThisWorkbook.Worksheets.Add After:=ThisWorkbook.Sheets(sheetcount)
          ' ReCount Worksheets, Could have used sheetcount=sheetcount+1 instead
          sheetcount = ThisWorkbook.Worksheets.Count
          'Name the sheet
          ThisWorkbook.Worksheets(sheetcount).Name = IKListSheet
    End If
 
 
    ' Loop through each worksheet in active workbook
    ' not a specific work book- looking for values that contain
    ' prefix IK and copying it to the IK output sheet
    j = 1 ' j is the Line we write to on IK Output
    For Each wksSheet In ThisWorkbook.Worksheets
        If wksSheet.Name <> IKListSheet Then ' Only work on sheets not named IK Output
            Filelength = wksSheet.usedrange.Rows.Count
            For i = 1 To Filelength ' Run through all used rows
                If Left(wksSheet.Cells(i, 1), 2) = "IK" Then
                   ' If the cell in the A Column Starts with IK then add to output.
                   ThisWorkbook.Worksheets(IKListSheet).Cells(j, 1).Value = wksSheet.Cells(i, 1).Value
                    j = j + 1
                End If
            Next i
        End If ' wksSheet.Name <> IKListSheet
    Next wksSheet
End Sub

:afrog:

That's strange - I'm certain the sheet did not exit in first place.

That why I added code to add the sheet IK output in my previous post.

I don't think the worksheet IK output code works either i.e to populate results.

I still need to create the sheet IK output - I think the roots are deeper than this.

Any ideas. :confused:
Thnaks :)

I have just checked my code, you may need to change to get it to work. :o

I called the function to check if the Worksheet Exists WkShtExists, but when
I call it in the IKList Sub I called it WkSheetExists.

When you make that correction you will find that the code is populating the
Output sheet as required.

:afrog:

When I paste the code in one module including the function and sub combined its stops and says function not defined.



How do I use this function not sure where it does go and what exactly do I do, as I obnly create sub routines in vba.

Thanks.

I have added the Test spreadsheet that I used,
try it out and let me know the results.
:afrog:

It does not work on the attached sheet - there can be huge blank gaps between the next IK Code - on even no IK- prefix on one sheet but some on another. :confused:

In this case you should find IK- prefix data on the 3rd and 4th sheet from end.
But it could any random place in column A.

I have not put the code in the attached - but just tried you code.
I am woking with massive spreadsheets.

I created a Module in your spreadsheet, and then pasted my code in, when I
ran it I ended up with an output sheet with 811 entries, the first four of
which where :-

IK-10003-166
IK-10016-002
IK-10159-108
IK-10159-124

Is this the result you want?

May be a bit late to ask this but what version of Excel are you using?

I am using excel 2003. I am running this on C drive using all my macros saved in personal xls.


It is still not working - I added a msgbox finish at the end of code.

Its zooms to the msgbox that but with no errors.

It does not even create the sheet.

Weird,

It works for me.

I removed Sheet1_1 in order to get this in as an attachment.

:afrog:

Hia,

My personal xls Microsoft excel sheet object does not seem to mapping to the sheet I am running the code on. I thought this would be automatic.


where as yours do as you saved yours to VBAProject test.xls.


How do I correct. Think its not working becuase its not regognising my excel objects.

My personal xls contain 16 Microsoft excel sheet objects . :confused:

Just to check,

Do you mean that you have put the macro into a spreadsheet called personal.xls
but are trying to run it for data in another spreadsheet, i.e. ResultsData.xls

:afrog:

I have just tried other macros and they seem to work on other spreadsheets - from personal.xls. This suggest that its not this.


Personal.xls is where all the macros are stored saved. Its standard VB place for storing macros on c drive.


Perhaps there is disavantages to using thisworkbook. None of my other macros use thisworkbook.


It work's on the file your spreadsheet you sent on file -but when I run it exactly the same code on personal xls it does not.

I need to run this code on any random spreadsheet that I want to run it on.

:confused:

I understand your problem now, I wrote the macro to be run from the
spreadsheet you held your data in, not from another spreadhseet.

That is why I used ThisWorkbook, which references the workbook the macro
is being run from.

I suggest that you check the spreadsheet personal.xls, it should have a
sheet called IK Output if you have run the macro from it.

I will consider ways to modify the macro to be run from another spreadsheet.

Will get back to you with an answer soon. :wave:
:afrog:

Hia,personal.xls is not a spreadsheet, it is the Project explorer tree structure.

VBAProject (Personal).xls.


This is a global macro storage place where macros are stored on C Drive . It is the defualt place for global macros to be stored.
It is also should be your global place - its certainly the same on my home computer by default.


I found out that this code works fine as long as you insert module in the specific VBA project workbook tree structure you want to use it on i.e.

VBAProject (Test).xls

Its does not appear to be for global macro use which might be a limitation of thisworkbook.


After attempting to run it on personal xls tree structure it seems to be have changed Microsoft excel object sub tree structure sheet names to sheet2(IK output) and IK outputss(my 2nd attempt).

I am worried about long term damage here becuase it does not seem to be removing itself - and am worried other global macros would expect i.e sheet2 to be named IK Output otherwise it will screw it up.

Help would be appreciated here.

I need this macro to be global - not specific
I need any potential problems as a result of running it globally on personal.xls to be fixed.The problems are Microsoft Excel Objects sheet names in personal xls.(If long term damage has been done I don't know).

Thanks

As far as I am aware neither long term damage, nor short term damage will
have been done.

I do not see VBA Project (Personal.xls) in my Project tree structure.

I think that if you have a hunt around then you will probably find a
spreadsheet called Personal.xls somewhere on your C drive.

As far as turning my code into "Global" code is concerned I have come up
with two ways.

For the sake of clarity I will separate them into two different posts.

In this first one the spreadsheet is opened using Workbooks.Open and the full
path name, We then call the IKList Sub, passing just the File name, with
extension; but without any Path.

We then use this to reference the Workbook we are working on by using
Workbooks(WkBkName) in place of ThisWorkbook

VB Code:

Option Explicit
Sub Test()
    Workbooks.Open "f:\test.xls"
    IKlist "test.xls"
    Workbooks("test.xls").Close SaveChanges:=True
End Sub
Function WkShtExists(ByVal WkBkName As String, ByVal WName As String) As Boolean
' Returns True if a worksheet WName exists in Workbook TWbk
Dim Counter As Long
Dim WkSheet As Worksheet
    WkShtExists = False
    For Each WkSheet In [B]Workbooks(WkBkName)[/B].Worksheets
        If UCase(WName) = UCase(WkSheet.Name) Then ' Worksheet Names are not Case Sensitive
            WkShtExists = True
            Exit For
        End If
    Next WkSheet
End Function
Sub IKlist(ByVal WkBkName As String)
Dim wksSheet As Worksheet
Dim i As Long
Dim Filelength As Integer
Dim j As Long
Dim sheetcount As Integer
Const IKListSheet = "IK Output"
 
    If WkShtExists(WkBkName, IKListSheet) Then
        Workbooks(WkBkName).Worksheets(IKListSheet).Cells.ClearContents
    Else
        ' Create Output Sheet  IK Output
        ' Count Worksheets
        sheetcount = Workbooks(WkBkName).Worksheets.Count
         'Add worksheets after the last sheet
         Workbooks(WkBkName).Worksheets.Add After:=Workbooks(WkBkName).Sheets(sheetcount)
          ' ReCount Worksheets, Could have used sheetcount=sheetcount+1 instead
          sheetcount = Workbooks(WkBkName).Worksheets.Count
          'Name the sheet
          Workbooks(WkBkName).Worksheets(sheetcount).Name = IKListSheet
    End If
 
 
    ' Loop through each worksheet in active workbook
    ' not a specific work book- looking for values that contain
    ' prefix IK and copying it to the IK output sheet
    j = 1 ' j is the Line we write to on IK Output
    For Each wksSheet In Workbooks(WkBkName).Worksheets
        If wksSheet.Name <> IKListSheet Then ' Only work on sheets not named IK Output
            Filelength = wksSheet.UsedRange.Rows.Count
            For i = 1 To Filelength ' Run through all used rows
                If Left(wksSheet.Cells(i, 1), 2) = "IK" Then
                   ' If the cell in the A Column Starts with IK then add to output.
                   Workbooks(WkBkName).Worksheets(IKListSheet).Cells(j, 1).Value = wksSheet.Cells(i, 1).Value
                    j = j + 1
                End If
            Next i
        End If ' wksSheet.Name <> IKListSheet
    Next wksSheet
End Sub

:afrog:

In this version of the code Instead of passing over teh name of the Workbook
we pass over an actual Workbook variable which we use in place of
ThisWorkbook

VB Code:

Option Explicit
Sub Test()
    Workbooks.Open "f:\test.xls"
    IKlist Workbooks("test.xls")
    Workbooks("test.xls").Close SaveChanges:=True
End Sub
Function WkShtExists(ByVal [B]WkBk As Workbook[/B], ByVal WName As String) As Boolean
' Returns True if a worksheet WName exists in Workbook TWbk
Dim Counter As Long
Dim WkSheet As Worksheet
    WkShtExists = False
    For Each WkSheet In [B]WkBk[/B].Worksheets
        If UCase(WName) = UCase(WkSheet.Name) Then ' Worksheet Names are not Case Sensitive
            WkShtExists = True
            Exit For
        End If
    Next WkSheet
End Function
Sub IKlist(ByVal WkBk As Workbook)
Dim wksSheet As Worksheet
Dim i As Long
Dim Filelength As Integer
Dim j As Long
Dim sheetcount As Integer
Const IKListSheet = "IK Output"
 
    If WkShtExists(WkBk, IKListSheet) Then
        WkBk.Worksheets(IKListSheet).Cells.ClearContents
    Else
        ' Create Output Sheet  IK Output
        ' Count Worksheets
        sheetcount = WkBk.Worksheets.Count
         'Add worksheets after the last sheet
         WkBk.Worksheets.Add After:=WkBk.Sheets(sheetcount)
          ' ReCount Worksheets, Could have used sheetcount=sheetcount+1 instead
          sheetcount = WkBk.Worksheets.Count
          'Name the sheet
          WkBk.Worksheets(sheetcount).Name = IKListSheet
    End If
 
 
    ' Loop through each worksheet in active workbook
    ' not a specific work book- looking for values that contain
    ' prefix IK and copying it to the IK output sheet
    j = 1 ' j is the Line we write to on IK Output
    For Each wksSheet In WkBk.Worksheets
        If wksSheet.Name <> IKListSheet Then ' Only work on sheets not named IK Output
            Filelength = wksSheet.UsedRange.Rows.Count
            For i = 1 To Filelength ' Run through all used rows
                If Left(wksSheet.Cells(i, 1), 2) = "IK" Then
                   ' If the cell in the A Column Starts with IK then add to output.
                   WkBk.Worksheets(IKListSheet).Cells(j, 1).Value = wksSheet.Cells(i, 1).Value
                    j = j + 1
                End If
            Next i
        End If ' wksSheet.Name <> IKListSheet
    Next wksSheet
End Sub

Hope that one of these two methods will work for you.
:afrog:

HiA,

Thanks, for this but I will be running my code on an already opened spreadhsheet of any random name.


The spreadsheet will be openned manually - then I need to run the code.



Thnaks very much for your time :)

Either method will work depends.

The first method might be easier as it just needs to be passed the
spreadsheet name of an open spreadsheet.

i.e. you open data.xls, then you call the macro passing data.xls as a parameter.

This could either be performed in a macro for a Command button, or by
writing the following in the immediate window.

IKList "data.xls"
:afrog:

Does this mean I have to keep on changing the spreadsheet name in the first open code each time for different spreadsheets I want to run it on.

Is there a way around this?:

It would be nice if could automatically change it for you. i,e changing test.xls to the workboook you want to run it on

Sub Test()
Workbooks.Open "f:\test.xls"
IKlist Workbooks("test.xls")
Workbooks("test.xls").Close SaveChanges:=True
End Sub


Not sure what to do here m8 for the way around.

How do you run macros in the Personal spreadsheet at present

Personal xls is the windows unhide place on excel. It may be a form of a spreadsheet where are macros are stored.

If you click windows unhide - you should have personal xls or some other global place.


This is a bit of code to delete duplicates - and can be run on any spreadsheet.

It does not involve openning a spreadsheet 1st via code before use.
Hope this explains

VB Code:

Sub DeleteDuplicates()
 
Dim i As Integer
Dim j As Integer
Dim xlapp As Worksheet
 
 
Set xlapp = Sheets("Sheet 1")
 
For i = xlapp.usedrange.Rows.Count To 1 Step -1
    For j = i + 1 To xlapp.usedrange.Rows.Count
        If Cells(i, 1) = Cells(j, 1) And Cells(i, 5) = Cells(j, 5) Then
            Rows(j).Delete
            Debug.Print i
        End If
        
    Next j
Next i
 
 
Set xlapp = Nothing
 
End Sub

I am using Excel 2002, I cannot see a Personal.xls file at all when coding macros.

All three of the methods that I have posted work with my setup.

Unfortunately I have never heard of Excel behaving in the way that yours
does, using a Personal.xls spreadsheet.

You could try re-writing my code, removing the references to the workbooks
and sheets andreplacing them with references that you know work, like the xlapp in your example.

Other than that I am stumped, maybe someone else can offer a suggestion.
:afrog:

To try to clear things up a bit here; Personal.xls is the file Excel generates if you elect to store macros in 'Personal Macro Workbook' when you record macros. Actually, I normally store them in 'This Workbook' but there you go. It is just a workbook like any other, and loads up with Excel so that you have access to your previously saved macros when you are running other projects.

The DeleteDuplicates sub can be run from any book because it is added in. However, you will have trouble with it if you don't have a sheet named "Sheet1", because you've hardcoded it into the sub.


If you want to run the code below, don't run the Test sub. Run IKList and pass the current workbook as a parameter (ActiveWorkbook) to run it from any sheet. Although I haven't scrutinised it, it doesn't look as though there is hardcoding in there that would prevent it from running on any workbook.


If you want to run it on any xls file on your hard drive, you're going to have to open it first though. Excel can't figure out what's in the Workbook and hence take coded decisions without opening the file and having a look. You can run it in the background, so that you don't have to make the books visible, but you still have to give Excel the access.


zaza

Torc - thanks for the code m8- I only have to change the workbook name in the code for it to work :) . However, I feel I have openned up a debate with the thisworkbook code.



Ref : earlier solution coding by torc proposed using thisworkbook.

Zaza, thanks for the insight into the difference between:


why the delete duplicates code (personal.xls stored macro) works on any already previously opened spreadsheet and why the looping between different sheets code(personal.xls) does not work on an already opened spreadsheet. The looping between sheet worked if you stored the macro in a module within the specific workbook i.e. not personal.xls.


Any more insight - you mention activeworkbook.

Thnaks everyone, sorry for the late reply its a excellent post. :) :wave: