Looping a routine through every Worksheet after Sheet1 & Sheet2.

xlyfe

Board Regular
Joined
Aug 28, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Hello,
I'm looking for suggestions on the best way to get this routine to loop itself through every sheet after Sheet2.
So, starting with Sheet3 and on, I'm wanting this routine to go through each Sheet and copy the values from the following ranges, ("P6:P"), ("R6:T"), ("V6:V"), and then paste those values in a long consolidated list on Sheet1, or mpl.

Below is the code that currently works as intended for copying values from Sheet3, and pasting to Sheet1.
How do I get "Sheet3" to be replaced with all Sheets 3 and up?

VBA Code:
Sub loop_thru_sheet3_up()

    Dim LmbrPrd As Long, LmbrDim, LmbrLen
    Dim LmbrPrdPst As Range, LmbrDimPst, LmbrLenPst
    Dim LastRow As Integer
    Dim mpl As Worksheet
   
    Set mpl = Worksheets("Master Pricing List")

    With mpl

        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            LastRow = .Cells.Find(What:="*", After:=.Range("A1"), _
                Lookat:=xlPart, LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                MatchCase:=False).Row + 1
        Else
            LastRow = 1
        End If
       
        Set LmbrPrdPst = mpl.Range("A" & LastRow)
        Set LmbrDimPst = mpl.Range("B" & LastRow)
        Set LmbrLenPst = mpl.Range("E" & LastRow)
   
        LmbrPrd = Sheet3.Range("P300").End(xlUp).Row
        Sheet3.Range("P6:P" & LmbrPrd).Copy
        LmbrPrdPst.PasteSpecial xlPasteValues
   
        LmbrDim = Sheet3.Range("P300").End(xlUp).Row
        Sheet3.Range("R6:T" & LmbrDim).Copy
        LmbrDimPst.PasteSpecial xlPasteValues
       
        LmbrLen = Sheet3.Range("P300").End(xlUp).Row
        Sheet3.Range("V6:V" & LmbrLen).Copy
        LmbrLenPst.PasteSpecial xlPasteValues

    End With
   
End Sub
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I am assuming that Sheet 1and sheet2 are in positions 1 and 2, so the Loop will start from the sheet in position 3 and so on

I haven't tested the code, so please try on a copy of your data
Essentially , you need a Loop to replicate the actions from sheet "3" on all other sheets

VBA Code:
Sub loop_thru_sheet3_up()

    Dim LmbrPrd As Long, LmbrDim, LmbrLen
    Dim LmbrPrdPst As Range, LmbrDimPst, LmbrLenPst
    Dim LastRow As Integer
    Dim mpl As Worksheet
    Dim Lp As Long
  
    Set mpl = Worksheets("Master Pricing List")

    With mpl
        For Lp = 3 To ActiveWorkbook.Worksheets.Count
            If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
                LastRow = .Cells.Find(What:="*", After:=.Range("A1"), _
                Lookat:=xlPart, LookIn:=xlFormulas, _
                SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
                MatchCase:=False).Row + 1
            Else
                LastRow = 1
            End If
      
            Set LmbrPrdPst = mpl.Range("A" & LastRow)
            Set LmbrDimPst = mpl.Range("B" & LastRow)
            Set LmbrLenPst = mpl.Range("E" & LastRow)
            
            LmbrPrd = Worksheets(Lp).Range("P300").End(xlUp).Row
            Sheet3.Range("P6:P" & LmbrPrd).Copy
            LmbrPrdPst.PasteSpecial xlPasteValues
        
            LmbrDim = Worksheets(Lp).Range("P300").End(xlUp).Row
            Sheet3.Range("R6:T" & LmbrDim).Copy
            LmbrDimPst.PasteSpecial xlPasteValues
            
            LmbrLen = Worksheets(Lp).Range("P300").End(xlUp).Row
            Sheet3.Range("V6:V" & LmbrLen).Copy
            LmbrLenPst.PasteSpecial xlPasteValues
            
            LastRow = LastRow + 1
         Next Lp
    End With
  
End Sub
 

xlyfe

Board Regular
Joined
Aug 28, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Essentially , you need a Loop to replicate the actions from sheet "3" on all other sheets

This is correct.

With that said, would I need to modify the "Sheet3.Range( ).Copy" references for the loop?

Examples below:

Would I need to change this?
VBA Code:
Sheet3.Range("P6:P" & LmbrPrd).Copy
To something like this?
VBA Code:
Lp.Range("P6:P" & LmbrPrd).Copy
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
This is correct.

With that said, would I need to modify the "Sheet3.Range( ).Copy" references for the loop?

Examples below:

Would I need to change this?
VBA Code:
Sheet3.Range("P6:P" & LmbrPrd).Copy
To something like this?
VBA Code:
Lp.Range("P6:P" & LmbrPrd).Copy
Did you check the code I posted? I think I forgot to update some of the "Sheet 3"s, but thats the general idea

To answer your question, you would change it in this case to
VBA Code:
Worksheets(Lp).Range(:P6:P" &.......)
 

xlyfe

Board Regular
Joined
Aug 28, 2020
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Did you check the code I posted? I think I forgot to update some of the "Sheet 3"s, but thats the general idea

To answer your question, you would change it in this case to
VBA Code:
Worksheets(Lp).Range(:P6:P" &.......)

Just finished testing this, and it looks like it's working great!
Thank you so much Momentman!
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,066
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Just finished testing this, and it looks like it's working great!
Thank you so much Momentman!
You are welcome. I hope it moves from "looking like" to actually "Working great" :cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,126,996
Messages
5,622,079
Members
415,875
Latest member
Tarali

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