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
 
Sort by date Sort by votes

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
You must log in or register to reply here.

Similar threads

J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
166
shknbk2
S
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
105
willow1985
willow1985
S
  • Solved
VBA does not continue to copy after blank cell
Replies
8
Views
525
jolivanes
jolivanes
D
  • Question
VBA optimization advice
Replies
3
Views
101
bobsan42
bobsan42
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
173
pennbowl
P

Forum statistics

Threads
1,214,985
Messages
6,122,605
Members
449,089
Latest member
Motoracer88

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