Is there a way to create a loop that loops through a set of code then offsets a certain number on the next loops

Question

I am new to VBA. I am trying to input values from multiple sheets into a "header" block that I have created on my master spreadsheet. I have multiple sheets with the same information but specific to that set of data. I have figured out how to do the first header block from the first sheet of data. Now I am wondering if I can create a loop that offsets the information a certain amount and input the information for the next remaining sheets. If possible I want it to not depend on how many sheets are imported. Whether 1 sheet or 50 sheets. Thanks!

Here is my code:

Private Sub Generate_Click()
'Set Header info for Raw Data


'Program Name Entry Sheet 2
 ActiveSheet.Next.Activate

Dim Part As Range

Set Part = ActiveSheet.Range("B1:B10").Find("Part Name")

Part.Offset(0, 1).Select
Selection.Copy
    Sheets("Variable Data").Select

Dim PName As Range

Set PName = ActiveSheet.Range("A1:AA10").Find("Program Name")

PName.Offset(0, 1).Select
ActiveSheet.Paste

'Program Rev Entry Sheet 2
ActiveSheet.Next.Activate
Dim Rev As Range

Set Rev = ActiveSheet.Range("B1:B10").Find("Revision Number")

Rev.Offset(0, 1).Select
Selection.Copy

Sheets("Variable Data").Select

Dim PRev As Range

Set PRev = ActiveSheet.Range("A1:AA10").Find("Program Rev")

PRev.Offset(0, 1).Select
ActiveSheet.Paste

'Program Date Entry Sheet 2
ActiveSheet.Next.Activate

Dim PDate As Range

Set PDate = ActiveSheet.Range("B1:B10").Find("Date")

PDate.Offset(0, 1).Select
Selection.Copy

Sheets("Variable Data").Select

Dim RDate As Range

Set RDate = ActiveSheet.Range("A1:AA10").Find("Run Date")

RDate.Offset(0, 1).Select
ActiveSheet.Paste

'Program Lot Entry Sheet 2
ActiveSheet.Next.Activate

Dim Serial As Range

Set Serial = ActiveSheet.Range("B1:B10").Find("Serial Number")

Serial.Offset(0, 1).Select
Selection.Copy

Sheets("Variable Data").Select

Dim Lot As Range

Set Lot = ActiveSheet.Range("A1:AA10").Find("Lot Number")
Lot.Offset(0, 1).Select
ActiveSheet.Paste

End Sub

sounds like you want to do a [nested loop](https://excelmacromastery.com/vba-for-loop/#Using_Nested_For_Loops). but first, read [this answer about avoiding select/activate](https://stackoverflow.com/a/10717999/2727437) — Marcucciboy2, Nov 08 '19 at 16:08
I have a list of fields that I would like filled out form multiple sheets. I want them to list out in multiple separate headers. Would this allow me to do this? — Brandon Campbell, Nov 08 '19 at 18:30

Tim Williams · Answer 1 · 2019-11-08T19:07:56.327

EDIT: you can take an approach like this

Private Sub Generate_Click()
'Set Header info for Raw Data

    Dim wb As Workbook, ws As Worksheet, wsVD As Worksheet
    Dim rngVDHeaders As Range, rowOffset As Long

    Set wb = ThisWorkbook

    'summary sheet and headers
    Set wsVD = wb.Worksheets("Variable Data")
    Set rngVDHeaders = wsVD.Range("A1:AA10")

    rowOffset = 1 'starting offset from header row

    'loop over all worksheets
    For Each ws In wb.Worksheets
        'excluding the summary sheet
        If ws.Name <> wsVD.Name Then
            'find and copy values to summary sheet
            With ws.Range("B1:B10")
                .Find("Part Name").Copy rngVDHeaders.Find("Program Name").Offset(rowOffset, 0)
                .Find("Revision Number").Copy rngVDHeaders.Find("Program Rev").Offset(rowOffset, 0)
                .Find("Date").Copy rngVDHeaders.Find("Run Date").Offset(rowOffset, 0)
                .Find("Serial Number").Copy rngVDHeaders.Find("Lot Number").Offset(rowOffset, 0)
            End With

        End If
        rowOffset = rowOffset + 1 'next line down
    Next ws


End Sub

Thanks! The information is set up in the same way. Just on different sheets. — Brandon Campbell, Nov 08 '19 at 18:25
Is the code in the workbook which has the data, or a separate workbook? — Tim Williams, Nov 08 '19 at 18:30
I have a button that Imports csv files as mulitple sheets. All of the data is in the same workbook. — Brandon Campbell, Nov 08 '19 at 18:33

Is there a way to create a loop that loops through a set of code then offsets a certain number on the next loops

1 Answers1