macro to loop and copy multiple cells and paste in a row on another sheet

[Reiss]

I have a workbook with multiple sheets which are exactly the same(User Input templates). Each sheet has about 100 cells of information which I am copying by looping through the worksheets and then pasting into a summary file called "Master".

I would like to loop through all the worksheets copy the cells and paste starting in "A2" of the "Master" sheet. I also want to know if there is a limit on how many cells I can put into the range as there may be more than 100.

I am using this solution which I found in another forum but doesn't exactly match what I need and I am not sure how to go about adjusting it

Any help is greatly appreciated.

Sub tgr()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wsDest As Worksheet
    Dim rCell As Range
    Dim aData() As Variant
    Dim sCells As String
    Dim i As Long, j As Long


    Set wb = ActiveWorkbook
    Set wsDest = wb.Sheets("Master")
    
    sCells = "c4,c7,c9,c11" ' I have more than a 100 cells that I need to copy
    
    ReDim aData(1 To wb.Sheets.Count - 1, 1 To wsDest.Range(sCells).Cells.Count)


    i = 0
    For Each ws In wb.Sheets
    'If ws.Name <> "Control" Then
        If ws.Name <> wsDest.Name Then
            'ws.Cells(RwNum, 1).Value = ws.Name
            i = i + 1
            j = 0
            For Each rCell In ws.Range(sCells).Cells
                j = j + 1
                aData(i, j) = rCell.Value
            Next rCell
        'End If
    End If
    Next ws


    wsDest.Range("B1").Resize(UBound(aData, 1), UBound(aData, 2)).Value = aData


End Sub

 

[dmt32]

Hi,
I don’t have definitive answer to your question but one way you may be able to do is create several ranges & then use Application.Union to combine them as a single range.

You can read more about this in VBA helpfile.

Question though, if your Users are entering data & saving a copy each time, have you considered using one input template for all users & then writing the input data to a single worksheet (database)?

Dave