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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,214,523
Messages
6,120,034
Members
448,940
Latest member
mdusw

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