'Range' of Object ' _Global' failed error when selectng range

Question

I'm really new to programming in VBA and having a problem with this code I'm trying to write. I am wanting the code to figure out the first row in column A that is unused then copy and paste data from a different part of the sheet into that row.

Sub CopyandPaste()


Dim RowLast As Long

RowLast = ThisWorkbook.Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1,   0).Row

Set NewRange = ThisWorkbook.Worksheets("Sheet2").Cells(RowLast, 1)

ThisWorkbook.Worksheets("Sheet1").Cells(8, "B").Select
Selection.Copy

Range("NewRange").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

End Sub

Any help would be extremely helpful.

Answer 1

Try this code :

Sub CopyandPaste()

    Dim RowLast As Long

    ThisWorkbook.Activate
    With Worksheets("Sheet2")
        RowLast = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
        Sheets("Sheet1").Cells(8, "B").Copy Sheets("Sheet2").Cells(RowLast, 1)
    End With

End Sub

Answer 2

I have added comments into the code explaining changes I made.

Sub CopyandPaste()

    Dim RowLast As Long
    Dim newRange As Range

    'this works easier if I understand your intent right
    'I generally use some large row number with Excel 2010
    'You may ahve to make this smaller if you are in 03
    RowLast = Sheets("Sheet2").Range("B99999").End(xlUp) + 1

    'if you KNOW you have continuous data in this column (no spaces)
    RowLast = Sheets("Sheet2").Range("B1").End(xldown) + 1

    'this is slightly better way to do this
    Set newRange = ThisWorkbook.Worksheets("Sheet2").Range("A" & RowLast)

    'don't do this
    'ThisWorkbook.Worksheets("Sheet1").Cells(8, "B").Select
    'Selection.Copy

    'do this instead
    Sheets("Sheet1").Range("B8").Copy
    newRange.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    'you were attempting to use a variable name (newrange) as a
    'name of a named range in the Excel sheet
    'use the variable range itself (as above)

End Sub