Repetitive Code Elimination

xcelnovice

Board Regular
Joined
Dec 31, 2011
Messages
81
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.

VBA Code:
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

RayFrye

Board Regular
Joined
Jan 31, 2005
Messages
81
Office Version
  1. 365
  2. 2019
I believe this will get you started.

VBA Code:
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
 
Last edited by a moderator:

xcelnovice

Board Regular
Joined
Dec 31, 2011
Messages
81
I believe this will get you started.

VBA Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
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):
VBA Code:
    Dim i As Long
    
    For i = 4 To 10
        MsgBox Sheets(i).Name
    Next i
 

xcelnovice

Board Regular
Joined
Dec 31, 2011
Messages
81
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):
VBA Code:
    Dim i As Long
   
    For i = 4 To 10
        MsgBox Sheets(i).Name
    Next i
This is great, thank you!
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,957
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,810
Messages
5,638,483
Members
417,027
Latest member
wlknspc7

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
Top