Looping a routine through every Worksheet after Sheet1 & Sheet2.

[xlyfe]

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?

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

 

[Momentman]

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

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]

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?

Sheet3.Range("P6:P" & LmbrPrd).Copy

To something like this?

Lp.Range("P6:P" & LmbrPrd).Copy

 

[Momentman]

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?

Sheet3.Range("P6:P" & LmbrPrd).Copy

To something like this?

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

Worksheets(Lp).Range(:P6:P" &.......)

 

[xlyfe]

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

Worksheets(Lp).Range(:P6:P" &.......)

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

 

[Momentman]

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"