Group worksheets

[Fred]

Hi
I'm trying to group worksheets, and not having much luck. I have tried this
with and without arrays. I am using the sheet code names, not the tab names,
and this is when I started having problems. The tab names worked until
different tab names occured. Where am I going wrong?

With MyBook
Sheets(array("Sheet13", "Sheet14", "Sheet15", "Sheet16", "Sheet17",
"Sheet18", "Sheet19", "Sheet20", "Sheet21", "Sheet22", "Sheet23",
"Sheet24")).Select
Sheets("sheet13").Activate
Call UpdateVlookup
Sheets("Sheet1").Activate
Sheets("Sheet16").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End With

Thanks for your help
Fred

 

[Jim Thomlinson]

I'm not too sure what you are trying to do with this code but perhaps this???

With MyBook
Sheets(array(Sheet13.name, Sheet14.name, Sheet15.name, Sheet16.name, _
Sheet17.name, Sheet18.name, Sheet19.name, Sheet20.name, Sheet21.name, _
Sheet22.name, Sheet23.name, Sheet24.name)).Select
sheet13.Activate
Call UpdateVlookup
Sheet1.Activate
Sheet16.Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End With

 

[Fred]

Thanks Jim, but the code didn't work.

I'm trying to group 12 worksheets together, then select the first of the 12,
run some code to fix formulas (which works great), then select the 4th sheet
of the 12, save and close the workbook, and then repeat with all the
workbooks in the folder (code not shown, but also works). Does this help?

Fred

 

[Jim Thomlinson]

Grouped worksheets may not be dong what you think it is. In VBA code can only
operate on one sheet at a time. Even if the sheets are grouped you will only
be modifying one sheet. If you want to update formulas on multiple sheets
then you need to pass each sheet to a procedure and run the updates.

 

[Fred]

Interesting. I did have it working and updating all grouped worksheets when
I was using the sheet tab names.

So, I should be able to use a loop statement with the sheet code names,
however, this didn't work. Any suggestions?

For x = 13 to 24 'where x is the sheet code names
Sheet(x).activate
run other code....
Next x

Fred

 

[john]

Like Jim I am not really too sure what you are trying to do but I can offer a
slightly more elegant way to select the sheets. You can adapt as required.

Hope useful.

Dim ShArr() As String
'Create an Array
'of all Sheets
Dim sh As Long
With ThisWorkbook.Worksheets
ReDim ShArr(1 To .Count)
For sh = 1 To .Count
ShArr(sh) = .Item(sh).Name
Next sh
End With
Worksheets(ShArr).Select

 

[Jim Thomlinson]

Since you have posted none of your other code it is hard to comment. The one
thing I notice is that you are relying on active sheet to run your code. When
you are accessing multiple workbooks this kind of thing can be combersome and
dangerous.

Also note that what you are refering to as code anme has nothing to do with
the sheets code name. The loop you have is using the index property of the
sheet. Once again that is a very dangerous way to proceed.

Perhaps post your UpdateVlookup code and we will be able to point you in the
right direction... Additionally are all of the sheets that need to be
modified identical or not?

 

[Fred]

Here is all the code. I have moved it all into one macro instead of two.
All of the worksheets are identical, otherwise I couldn't do this. Each
workbook has 13 worksheets in it, with one being named "Info sheet", which I
don't want modified. The other twelve are either "Jan 07" - "Dec 07" or "Jan
08" - "Dec 08", which I do want modified.

Also, this code runs slow, taking about 90 seconds to run on 5 workbooks. I
have maybe 1,000 workbooks to run this on, maybe more. Can it be sped up?

Thanks
Fred

Sub ChangeWorksheets()

Dim MyBook As Workbook
Dim MyFilePath As String
Dim i As Integer

' Search for the Excel files in the Main Folder

With Application.FileSearch
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
End With

' when files are found: copy and paste them in a different destination

If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set MyBook = Workbooks.Open(.FoundFiles(i), True)
With MyBook
For Each x In Worksheets
x.Activate
If ActiveSheet.Name = "Info sheet" Then
Range("B1").Select
Else
Range("AA10:AC10").Select
Selection.AutoFill
Destination:=Range("AA10:AC20"), Type:=xlFillDefault
Range("H114").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(R[-113]C[-4],R[-113]C[19]:R[-94]C[21],2)"
Range("H115").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(R[-114]C[-4],R[-114]C[19]:R[-95]C[21],3)"
Range("B1").Select
End If
Next x
Worksheets(5).Activate
ActiveWindow.DisplayZeros = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End With
Next i
End If
End With
MsgBox "All done."
End Sub

 

[Fred]

Hi
Any luck with the code or ways to speed it up?

Here is all the code. I have moved it all into one macro instead of two.
All of the worksheets are identical, otherwise I couldn't do this. Each
workbook has 13 worksheets in it, with one being named "Info sheet", which I
don't want modified. The other twelve are either "Jan 07" - "Dec 07" or "Jan
08" - "Dec 08", which I do want modified.

Also, this code runs slow, taking about 90 seconds to run on 5 workbooks. I
have maybe 1,000 workbooks to run this on, maybe more. Can it be sped up?

Thanks
Fred

Sub ChangeWorksheets()

Dim MyBook As Workbook
Dim MyFilePath As String
Dim i As Integer

' Search for the Excel files in the Main Folder

With Application.FileSearch
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
With Application.FileDialog(msoFileDialogFolderPicker)
.Show
End With

' when files are found: copy and paste them in a different destination

If .Execute() > 0 Then
For i = 1 To .FoundFiles.Count
Set MyBook = Workbooks.Open(.FoundFiles(i), True)
With MyBook
For Each x In Worksheets
x.Activate
If ActiveSheet.Name = "Info sheet" Then
Range("B1").Select
Else
Range("AA10:AC10").Select
Selection.AutoFill
Destination:=Range("AA10:AC20"), Type:=xlFillDefault
Range("H114").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(R[-113]C[-4],R[-113]C[19]:R[-94]C[21],2)"
Range("H115").Select
ActiveCell.FormulaR1C1 =
"=VLOOKUP(R[-114]C[-4],R[-114]C[19]:R[-95]C[21],3)"
Range("B1").Select
End If
Next x
Worksheets(5).Activate
ActiveWindow.DisplayZeros = False
ActiveWorkbook.Save
ActiveWorkbook.Close
End With
Next i
End If
End With
MsgBox "All done."
End Sub

Since you have posted none of your other code it is hard to comment. The one
thing I notice is that you are relying on active sheet to run your code. When
you are accessing multiple workbooks this kind of thing can be combersome and
dangerous.

Also note that what you are refering to as code anme has nothing to do with
the sheets code name. The loop you have is using the index property of the
sheet. Once again that is a very dangerous way to proceed.

Perhaps post your UpdateVlookup code and we will be able to point you in the
right direction... Additionally are all of the sheets that need to be
modified identical or not?