Looping over multiple worksheets

B

Blockhead15

New Member
Joined
Jun 13, 2022
Messages
17
Office Version
  1. 365
Platform
  1. Windows
I have my macro set up on my sample data file but it's not clean at all. I tried to implement a looping structure over the worksheets to simplify but could not get it to work. So long as the input file meets a certain condition, I add some sheets and populate. I know there's a simpler way instead of all the clicks back and forth but frustration is hampering the thought process. Any suggestions on simplest way to clean this up?

VBA Code: 
Sub GetData()
'
' GetData Macro
'

'
    Application.ScreenUpdating = False 'To avoid screen flickering
    
    Windows("TEST_XYZ.xlsx").Activate
    Sheets("Run Info").Copy After:=Workbooks("TestSample.xlsm").Sheets(Workbooks("TestSample.xlsm").Sheets.Count)
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Data"
    Windows("TEST_XYZ.xlsx").Activate
    Range("A1:B17").Select
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Range("C7").Select
    Windows("TEST_XYZ.xlsx").Activate
    Range("D12:D17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("B12").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("A19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Windows("TEST_XYZ.xlsx").Activate
    Range("A19").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("B20").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("A26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("C20").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("A33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("D20").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("A42").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("E20").Select
    ActiveSheet.Paste
    Range("H18").Select
    ' Worksheet 1
    Windows("TEST_XYZ.xlsx").Activate
    Sheets("X99X_GroupB").Select
    Range("D20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("B22").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("D31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("C22").Select
    ActiveSheet.Paste
    Windows("TestSample.xlsm").Activate
    Application.CutCopyMode = False
    Range("E22").Select
    Windows("TEST_XYZ.xlsx").Activate
    Range("D40").Select
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("D22").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("D45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("X99X_GroupB").Select
    Windows("TestSample.xlsm").Activate
    Range("E22").Select
    ActiveSheet.Paste
    ' Worksheet 2
    Windows("TEST_XYZ.xlsx").Activate
    Sheets("X99X_GroupC").Select
    Range("D20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("B23").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("D31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("C23").Select
    ActiveSheet.Paste
    Windows("TestSample.xlsm").Activate
    Application.CutCopyMode = False
    Range("E23").Select
    Windows("TEST_XYZ.xlsx").Activate
    Range("D40").Select
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("D23").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("D45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("X99X_GroupC").Select
    Windows("TestSample.xlsm").Activate
    Range("E23").Select
    ActiveSheet.Paste
    ' Worksheet 3
    Windows("TEST_XYZ.xlsx").Activate
    Sheets("X99X_GroupA").Select
    Range("D20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("B21").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("D31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("C21").Select
    ActiveSheet.Paste
    Windows("TestSample.xlsm").Activate
    Application.CutCopyMode = False
    Range("E22").Select
    Windows("TEST_XYZ.xlsx").Activate
    Range("D40").Select
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("D21").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("D45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("X99X_GroupA").Select
    Windows("TestSample.xlsm").Activate
    Range("E21").Select
    ActiveSheet.Paste
    ' Worksheet 3
    Windows("TEST_XYZ.xlsx").Activate
    Sheets("X99X_GroupD").Select
    Range("D20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("B24").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("D31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("C24").Select
    ActiveSheet.Paste
    Windows("TestSample.xlsm").Activate
    Application.CutCopyMode = False
    Range("E22").Select
    Windows("TEST_XYZ.xlsx").Activate
    Range("D40").Select
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("D24").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("D45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("X99X_GroupD").Select
    Windows("TestSample.xlsm").Activate
    Range("E24").Select
    ActiveSheet.Paste
    
    'SheetNames
    Windows("TEST_XYZ.xlsx").Activate
    Sheets(Array("X99X_GroupA", "X99X_GroupB", "X99X_GroupC", "X99X_GroupD")).Select
    Range("H1").Select
    ActiveCell.Formula2R1C1 = _
        "=MID(CELL(""filename"",R[-8]C[-5]),FIND(""]"",CELL(""filename"",R[-8]C[-5]))+1,255)"
    Range("H2").Select
    Sheets("Run Info").Activate
    Sheets("X99X_GroupA").Activate
    Range("H1").Select
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("A21").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("TEST_XYZ.xlsx").Activate
    Sheets("X99X_GroupB").Select
    Range("H1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("A22").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("TEST_XYZ.xlsx").Activate
    Sheets("X99X_GroupC").Select
    Range("H1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("A23").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("TEST_XYZ.xlsx").Activate
    Sheets("X99X_GroupD").Select
    Range("H1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("A24").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("D11").Select
    
    Columns.AutoFit
    
    Application.ScreenUpdating = True
End Sub
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Hi and welcome to MrExcel!

Some parts of your macro I don't understand, it would be nice if you explain step by step what you want to do.

With the following example I explain how you can simplify your code.

Your code:
VBA Code: 
    ' Worksheet 1
    Windows("TEST_XYZ.xlsx").Activate
    Sheets("X99X_GroupB").Select
    Range("D20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("B22").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("D31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("C22").Select
    ActiveSheet.Paste
    Windows("TestSample.xlsm").Activate
    Application.CutCopyMode = False
    Range("E22").Select
    Windows("TEST_XYZ.xlsx").Activate
    Range("D40").Select
    Selection.Copy
    Windows("TestSample.xlsm").Activate
    Range("D22").Select
    ActiveSheet.Paste
    Windows("TEST_XYZ.xlsx").Activate
    Range("D45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D45").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("X99X_GroupB").Select
    Windows("TestSample.xlsm").Activate
    Range("E22").Select
    ActiveSheet.Paste

This could be a simple structure to copy and paste:

Source --------------> Destination
book.sheet.Range -----> book.sheet.Range

So:
VBA Code: 
 Workbooks("TEST_XYZ.xlsx").Sheets("X99X_GroupB").Range("D20").Copy Workbooks("TestSample.xlsm").Sheets("??").Range("B22")
 Workbooks("TEST_XYZ.xlsx").Sheets("X99X_GroupB").Range("D31").Copy Workbooks("TestSample.xlsm").Sheets("??").Range("C22")
 Workbooks("TEST_XYZ.xlsx").Sheets("X99X_GroupB").Range("D40").Copy Workbooks("TestSample.xlsm").Sheets("??").Range("D22")
 Workbooks("TEST_XYZ.xlsx").Sheets("X99X_GroupB").Range("D45").Copy Workbooks("TestSample.xlsm").Sheets("??").Range("E22")

"??" --- I put that because I don't know the name of the target sheet, it's the part I don't understand.

And in order not to have the name of the workbook and the sheet repeated several times, you can set the name in a variable (object) and simplify like this:

VBA Code: 
  Dim sh1 As Worksheet, sh2 As Worksheet
  Set sh1 = Workbooks("TEST_XYZ.xlsx").Sheets("X99X_GroupB")
  Set sh2 = Workbooks("TestSample.xlsm").Sheets("??")
  sh1.Range("D20").Copy sh2.Range("B22")
  sh1.Range("D31").Copy sh2.Range("C22")
  sh1.Range("D40").Copy sh2.Range("D22")
  sh1.Range("D45").Copy sh2.Range("E22")

"??" --- I put that because I don't know the name of the target sheet, it's the part I don't understand.


With 2 sheets:

VBA Code: 
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim shD As Worksheet
    Set sh1 = Workbooks("TEST_XYZ.xlsx").Sheets("X99X_GroupB")  'Source1
    Set sh2 = Workbooks("TEST_XYZ.xlsx").Sheets("X99X_GroupC")  'Source2
    
    Set shD = Workbooks("TestSample.xlsm").Sheets("??")         'Destination
    ' Worksheet 1
    sh1.Range("D20").Copy sh2.Range("B22")
    sh1.Range("D31").Copy sh2.Range("C22")
    sh1.Range("D40").Copy sh2.Range("D22")
    sh1.Range("D45").Copy sh2.Range("E22")
  
    ' Worksheet 2
    sh2.Range("D20").Copy shD.Range("B23")
    sh2.Range("D31").Copy shD.Range("C23")
    sh2.Range("D40").Copy shD.Range("D23")
    sh2.Range("D45").Copy shD.Range("E23")
"??" --- I put that because I don't know the name of the target sheet, it's the part I don't understand.


Try to do it in your code and test.
 
Upvote 0
Thanks for the suggestions. Here's what I am trying to do. My input worksheets all have similar format as shown below. The GroupA-D worksheets will be dynamic - in my test scenario, I had 4 but there could be up to 100. This is where looping through the a counter of the different sheets would be helpful as opposed to looing for a specific sheet name. On each worksheet, I am pulling out specific pieces of data. In my test scenario, it's 4 measures but it could be more.

1655256558159.png


which I am trying to summarize in the worksheet image below. So basically accumulating the important data onto a single worksheet instead of split across multiple worksheets. Aside from flickering when my macro runs, it gets me he correct result, although it's not clean.

1655256705761.png
 
Upvote 0
NOTE XL2BB:

It would help greatly if you could give us the sample data in a form that we can copy to test with, rather that a picture.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
This is the input data from Group A worksheet. All input files will have same layout, just different values in the measures.

TEST_XYZ.xlsx
ABCD
1Info1
2Info2
3Info3
4Info4
5Info5
6Info6
7Info7
8Info8
9Info9
10Info10GroupA
11
12Year20102010
13Column14
14NeededNoYes
15Description
16Final?
17Int4.000%6.000%
18
19Measure 1
20Etot400.00400.00
21A10.000.00
22A2200.00200.00
23I1150.00150.00
24C0.000.00
25
26Measure 2
27A10.000.00
28A22453.342193.28
29Atot2453.342193.28
30I2874.892570.15
31Grand Total5328.224763.43
32
33Measure 3
34A10.000.00
35A2561.61527.91
36Atot561.61527.91
37I11612.381515.63
38I21262.511186.76
39Itot2874.892702.39
40Grand Total3436.503230.31
41
42Measure 4
43A10.000.00
44A2107.50134.60
45Atot107.50134.60
46
X99X_GroupA




This is the output file already populated

TestSamplePopulated.xlsm
ABCDE
1Info1
2Info2
3Info3
4Info4
5Info5
6Info6
7Info7
8Info8
9Info9
10Info10Total
11
12Year2010
13Column4
14NeededYes
15Description
16Final?
17Int6.000%
18
19
20Measure 1Measure 2Measure 3Measure 4
21X99X_GroupA400.004763.433230.31134.60
22X99X_GroupB400.005164.903597.16190.05
23X99X_GroupC400.004624.733884.91174.85
24X99X_GroupD400.004471.144205.69100.57
25
Data
 
Upvote 0
Your examples are not consistent, neither those in the image nor those in xl2bb.

The cells to get the data are always the same or is there a pattern to determine which data to get?

For group A,
measure 1 comes from row "etot" (400),
measure 2 comes from row "Grand Total" (4763.43),
measure3 comes from row "Grand Total" (3230.31),
measure4 comes from row "atot" (134.60)

:unsure:

Are there always 4 measures? What would be the cell for each measurement?
 
Upvote 0
The two books "TEST_XYZ" and "TestSample" should be open, the results on the "Data" sheet in book "TestSample".

Try this:

VBA Code: 
Sub Looping_worksheets()
  Dim sh1 As Worksheet
  Dim i As Long
  
  i = 21
  For Each sh1 In Workbooks("TEST_XYZ.xlsx").Sheets
    Select Case sh1.Name
      Case "Run Info", "X99X_Total_Total"           'ignore these sheets
      Case Else
        Workbooks("TestSample").Sheets("Data").Range("A" & i).Resize(1, 5).Value = _
          Array(sh1.Name, sh1.[D20], sh1.[D31], sh1.[D40], sh1.[D45])
        i = i + 1
    End Select
  Next
End Sub
 
Upvote 0
Solution
Each worksheet will have same layout. My example has 5 worksheets (GroupA, GroupB, GroupC and Group D, along with a Total that I need to skip over). Another workbook may have 12 worksheets. But the value I'm referencing for Measure 1 will always be in same cell - in this case, it's etot (row 20). Same for measure 2, grandtot (row 31), Measure 3 is in row 40 and Measure 4 is row 43. Since the cells are always the in the same location for every worksheet/workbook, that's why I used the exact cell reference in my macro.

While there are two columns in my test workfile, I am only using the data in column D. Ultimately, there will be a userform that asks for input to determine what column of values to use, but I thought trying to get a cleaned-up version would help facilitate that user choice.

unfortunately, the sections that derive the different measures are all different, so there,s no consistent labelling pattern, and so specific cells is the chosen path.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Tidy up simple code?
Replies
9
Views
113
MOB
M
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
429
willow1985
willow1985
D
  • Solved
extract to another workbook and save using desktop path
2
Replies
12
Views
274
jolivanes
jolivanes
S
  • Question Question
Macro Formatting to all WS
Replies
4
Views
385
MARK858
MARK858
B
  • Question Question
need recorded macro to copy from whatever workbook i currently have open and active rather than specific named workbook. without Dim objects or...
Replies
1
Views
321
Anthony47
A

Forum statistics

Threads
1,225,477
Messages
6,185,210
Members
453,283
Latest member
Shortm88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top