Copying and pasting between two workbooks

[Gliffix101]

Hello -

I am attempting to copy certain cells from Workbook1 and paste them into the last row of Workbook2. I have tried a few different ways but I keep getting stuck at the same spot. Any help would be appreciated (as I'm sure there's an easier way to do this). It keeps getting hung up when I attempt to define Column A's last row. The code lives in Workbook 1 and opens Workbook 2 from the desktop:

' Defines variables
Dim wb1 As Workbook, wb2 As Workbook, LastRow1 As Long, LastRow2 As Long, LastRow3 As Long, LastRow4 As Long, LastRow5 As Long, LastRow6 As Long, LastRow7 As Long

' Disables screen updating to reduce flicker
Application.ScreenUpdating = False

' Sets wb1 as the main workbook and opens wb2
Set wb1 = ThisWorkbook
Workbooks.Open ("C:\Users\sample\Desktop\Status_Log.xlsx")
Set wb2 = ThisWorkbook
LastRow1 = wb2.Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row + 1
LastRow2 = wb2.Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row + 1
LastRow3 = wb2.Sheets("Sheet1").Cells(Rows.Count, "C").End(xlUp).Row + 1
LastRow4 = wb2.Sheets("Sheet1").Cells(Rows.Count, "D").End(xlUp).Row + 1
LastRow5 = wb2.Sheets("Sheet1").Cells(Rows.Count, "E").End(xlUp).Row + 1
LastRow6 = wb2.Sheets("Sheet1").Cells(Rows.Count, "F").End(xlUp).Row + 1
LastRow7 = wb2.Sheets("Sheet1").Cells(Rows.Count, "G").End(xlUp).Row + 1

wb1.Activate

'Set Variables needed
RequestType = Range("A1").Text
RequestID = Range("D3").Text
Client = Range("D8").Text 'may need to be D8 based on sheet
Brand = Range("D9").Text  'may need to be D9 based on sheet
Urgency = Range("D2").Text
DateToday = Date

wb2.Sheets("Sheet1").Range("A" & LastRow1).Value = RequestType & " - " & RequestID & " - " & Client & " - " & Brand & " - " & Urgency & " - " & DateToday
wb2.Sheets("Sheet1").Range("B" & LastRow2).Value = RequestType
wb2.Sheets("Sheet1").Range("C" & LastRow3).Value = RequestID
wb2.Sheets("Sheet1").Range("D" & LastRow4).Value = Client
wb2.Sheets("Sheet1").Range("E" & LastRow5).Value = Brand
wb2.Sheets("Sheet1").Range("F" & LastRow6).Value = Urgency
wb2.Sheets("Sheet1").Range("G" & LastRow7).Value = DateToday

wb2.Close True

 

[rlv01]

Perhaps something like this (not tested)

' Defines variables
    Dim wb1 As Workbook, wb2 As Workbook, LastRow As Long
    Dim RequestType, RequestID, Client, Brand, Urgency, DateToday

    ' Disables screen updating to reduce flicker
    Application.ScreenUpdating = False

    ' Sets wb1 as the main workbook and opens wb2
    Set wb1 = ThisWorkbook

    On Error Resume Next
    Set wb2 = Workbooks.Open("C:\Users\sample\Desktop\Status_Log.xlsx")
    On Error GoTo 0

    If wb2 Is Nothing Then
        MsgBox "Problem opening workbook 'C:\Users\sample\Desktop\Status_Log.xlsx'"
        Exit Sub
    End If

    'Set Variables needed
    RequestType = wb1.Range("A1").Text
    RequestID = wb1.Range("D3").Text
    Client = wb1.Range("D8").Text                     'may need to be D8 based on sheet
    Brand = wb1.Range("D9").Text                      'may need to be D9 based on sheet
    Urgency = wb1.Range("D2").Text
    DateToday = Date

    With wb2.Sheets("Sheet1")
        Debug.Print .Name
        LastRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1
        .Range("A" & LastRow).Value = RequestType & " - " & RequestID & " - " & Client & " - " & Brand & " - " & Urgency & " - " & DateToday

        LastRow = .Range("B" & .Rows.Count).End(xlUp).Row + 1
        .Range("B" & LastRow).Value = RequestType

        LastRow = .Range("C" & .Rows.Count).End(xlUp).Row + 1
        .Range("C" & LastRow).Value = RequestID

        LastRow = .Range("D" & .Rows.Count).End(xlUp).Row + 1
        .Range("D" & LastRow).Value = Client

        LastRow = .Range("E" & .Rows.Count).End(xlUp).Row + 1
        .Range("E" & LastRow).Value = Brand

        LastRow = .Range("F" & .Rows.Count).End(xlUp).Row + 1
        .Range("F" & LastRow).Value = Urgency

        LastRow = .Range("G" & .Rows.Count).End(xlUp).Row + 1
        .Range("G" & LastRow).Value = DateToday
    End With

    wb2.Close True