Cycle throuhg rows on a sheet

**TMS** · 03-11-2014, 05:19 PM

The original example code demonstrated looping through rows on each sheet. The second example just gets one row of data from each sheet, which appeared to be what was (not) present in your sample workbook.

I suggest you have a go at combining the row loop into the sheet loop.

Regards, TMS

**Sparkplug90** · 03-11-2014, 06:28 PM

Ok im lost woith what to do from her..i can get them to combine well

Option Explicit

Sub SampleLoop()

Const sSSName As String = "Labels"
Dim sh As Worksheet
Dim lLR As Long
Dim i As Long

For Each sh In ThisWorkbook.Worksheets
    With sh 'do this to each sheet
        If sh.Name <> sSSName Then ' if the sheet doesnt equal label sheet
        With Sheets(sSSName) ' label sheet
            lLR = .Range("A" & .Rows.Count).End(xlUp).Row 'bottom row on the labels sheet
            For i = 1 To lLR
                .Range("A" & i).Value = sh.Range("A" & (i + 3)) & Chr(10) & _
                                        sh.Range("B" & (i + 3)) & Chr(10) & _
                                        sh.Range("C" & (i + 3)) & Chr(10)
                If sh.Range("D" & (i + 3)) = "" Then
                    .Range("A" & i).Value = .Range("A" & i).Value & "Det. 2014"
                Else
                    .Range("A" & i).Value = .Range("A" & i).Value & sh.Range("D" & (i + 3)) & _
                                                                    Chr(10) & _
                                                                    "Det. R. G. Ahlers 2014"
                End If 'Sheets(sSSName).Range("D4")
            Next 'i
            End With
        End If 'sh.Name
    End With 'sh
Next 'sh

End Sub