Repetitive Code Elimination

[xcelnovice]

Hi, the below code copies from one workbook a few different ranges in one workbook and pastes them in another workbook. It works so my skill set says repeat the same code, change the tab names in the code for the other 20 tabs. Is there a better way to repeat this code so it captures all the other sheets.

sub CopyPaste()

Dim x As Workbook
Dim y As Workbook
Set x = Workbooks.Open(xxxx)
Set y = Workbooks.Open(xxxx)

x. Sheets(“Sheet1”).Range(“D13:L62”).Copy

y.Sheets(“SheetA”).Range(“D13:L62”).PasteSpecial

End Sub

 

[RayFrye]

I believe this will get you started.

Sub WorksheetLoop()

    Dim WS_Count As Integer
    Dim I As Integer
   
    ' Set WS_Count equal to the number of worksheets in the active
    ' workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count
   
    ' Begin the loop.
    For I = 1 To WS_Count
   
        ' Insert your code here.
        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.
   
        MsgBox ActiveWorkbook.Worksheets(I).Name
   
    Next I

End Sub

 

[xcelnovice]

I believe this will get you started.

Sub WorksheetLoop()

    Dim WS_Count As Integer
    Dim I As Integer
  
    ' Set WS_Count equal to the number of worksheets in the active
    ' workbook.
    WS_Count = ActiveWorkbook.Worksheets.Count
  
    ' Begin the loop.
    For I = 1 To WS_Count
  
        ' Insert your code here.
        ' The following line shows how to reference a sheet within
        ' the loop by displaying the worksheet name in a dialog box.
  
        MsgBox ActiveWorkbook.Worksheets(I).Name
  
    Next I

End Sub

Awesome! Thank you. On follow up, is there a way to limit the range of sheets it counts. For example set a range of Worksheets4 to Worksheet10? Thanks Again.

 

[Joe4]

is there a way to limit the range of sheets it counts. For example set a range of Worksheets4 to Worksheet10? Thanks Again.

If your sheets were added in order, you can use their indexes like this:
(this is just a simple macro to show you how to loop through sheets 4-10, by returning a message box with the name of each):

    Dim i As Long
    
    For i = 4 To 10
        MsgBox Sheets(i).Name
    Next i

 

[xcelnovice]

If your sheets were added in order, you can use their indexes like this:
(this is just a simple macro to show you how to loop through sheets 4-10, by returning a message box with the name of each):

    Dim i As Long
   
    For i = 4 To 10
        MsgBox Sheets(i).Name
    Next i

This is great, thank you!

 

[Joe4]

You are welcome.