Do Until each worksheet

[gittymoe]

Guys, Not sure if this is enough information but I am trying to get the below code in yellow to loop through each worksheet and the data retrieved (stacked) in columns A:C on the CommercialInvoiceConsolidation worksheet. Is it possible to run a loop through each worksheet?

Dim CS As Worksheet, ws1 As Worksheet, SH As Worksheet, Ws As Worksheet
Dim LR1 As Long, NR As Long, sCol As Long
Dim LR99 As Double
Dim sName As Boolean, SortStr As String
Dim wb As Workbook
Dim DestSh As Worksheet
Dim Ls As Integer, Lw As Integer
Dim LastRow As Long, LastRowA As Long, LastRowB As Long, LastRowC As Long, LastRowD As Long, LastA As Long, LastD As Long, C As Long
Dim i As Integer
Application.ScreenUpdating = False

    Set wb = ThisWorkbook
    Set DestSh = wb.Sheets("CommercialInvoiceConsolidation")
 
    ' Loop through worksheets that start with the name "20"
    i = 2
  
'Setup
Set CS = ActiveWorkbook.Sheets("CommercialInvoiceConsolidation")
NR = 2

'Process each data sheet
  
    For Each ws1 In Worksheets
         If ws1.Name <> CS.Name And _
            ws1.Name <> "CommercialInvoiceConsolidation" Then
                   
            LR1 = ws1.Range("C" & ws1.Rows.Count).End(xlUp).Row
            LR99 = LR1 - 29
            numPO = Cells(22, 4).Value
            numInvoice = Cells(7, 9).Value
            DatDate = Cells(8, 9).Value
                 
           If NR = 1 Then
           End If
               
           X = 2
            Do Until X = LR99 + 2
            If Cells(X, 1).Value = "" Then
            Cells(X, 1).Value = numPO
            Cells(X, 2).Value = numInvoice
            Cells(X, 3).Value = DatDate
            End If
            X = X + 1
            Loop
     
       End If
       Next ws1
          
End Sub

 

[steve the fish]

The immediate problem with the cells part is that you arent qualifying them with a sheet so excel is going to use the activesheet at the time. Id imagine not what you imagined.

 

[gittymoe]

If I understand you correctly, the "CommercialInvoiceConsolidation" worksheet is my master sheet where the data needs to end up and all other worksheets are the once the data should pull from

 

[Zack Barresse]

I'm not sure I follow what exactly is supposed to be happening, but it appears that, in your loop, you're still referencing the active sheet. For example:

numPO = Cells(22, 4).Value

The Cells method doesn't have a sheet explicitly referenced, so Excel assumes it should use the activesheet as it's parent object. This is one good example of how VBA will let you get away with (code) murder. Instead, what you should do is reference or qualify that range object with its parent object - a worksheet in this case. You would do the same for the worksheet and qualify its parent object - a workbook, if you were working with multiple workbooks.

numPO = ws1.Cells(22, 4).Value

My assumption of the above line of code is that these range objects inside your loop should be looking at the worksheet being iterated upon.

 

[gittymoe]

Zack---You absolutely nailed it. You took my crummy explanation/question and got me what I needed!

numPO = ws1.Cells(22, 4).Value


Thanks!

 

[Zack Barresse]

Very glad to help! Steve nailed it too. I just didn't see his response before I posted mine lol.