CODEWITHSUNDEEP
vba
DRod
DRod

Reputation: 21

VBA - Copy data across worksheets

I'm looking to copy data across multiple worksheets. The names of the worksheets are in column L, I want to pick up the data from columns N:R, for that particular line, and then copy that into cells D17:D21 in the corresponding sheet.

Any assistance would be great.

Cheers DRod

Sub Macro2()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim wsGet As Worksheet
    Dim LCell As Range
    Dim sDataCol As String
    Dim lHeaderRow As Long

    sDataCol = "L"  'Change to be the column you want to match sheet names agains
    lHeaderRow = 5  'Change to be what your actual header row is

    Set wb = ActiveWorkbook
    Set ws = wb.Sheets("Sheet1")    'Change this to be your Summary sheet

    'Check for values in sDataCol
    With ws.Range(sDataCol & lHeaderRow + 1, ws.Cells(ws.Rows.Count, sDataCol).End(xlUp))
        If .Row <= lHeaderRow Then Exit Sub 'No data

        'Loop through sDataCol values
        For Each LCell In .Cells
        If LCell.Text <> "" Then
            'Check if sheet named that value exists
            If Evaluate("ISREF('" & LCell.Text & "'!A1)") Then
                'Found a matching sheet, copy data from columns N:R to cells D17:D21 in the corresponding sheet
                Set wsGet = wb.Sheets(LCell.Text)
                wsGet.Range("N[ ]:R[ ]").Copy
                LCell.Activate
                Range("D17:D21").PasteSpecial Paste:=xlPasteValues, Transpose:=True
            End If
        End If
        Next LCell
    End With

End Sub

Upvotes: 0

Views: 139

Answers (1)

user3598756
user3598756

Reputation: 29421

there are some mistakes

  • wsGet.Range("N[ ]:R[ ]") is not a valid syntax.

    while wsGet.Range("N:R")is.

    still, that way you get the entire columns, and not just the row you need of them.

    you could use "Instersect()" method or the "Resize()" method on that range to get the range you need

  • with wsGet.Range("N[ ]:R[ ]").Copy you're using ".Copy" method on a "wsGet" sheet range.

    use it on the same range (corrected as per the preceeding suggestion) of the "ws" sheet instead

  • with LCell.Activate, you're activating a "cell" instead of a "sheet".

    you should use wb.Sheets(LCell.Text).Activate instead

    but you don't need any sheet activation since you have already set "wsGet" as the "destination" sheet, so simply use ".PasteSpecial" method on it

Upvotes: 1

Related Questions