Final Row Error

[Huey462]

I am trying to combine data from two separate worksheets onto one so it can be sorted for printing. Using the macro recorder, and the search function on the forum, I managed to ham-fist my way through most of it…except for one issue.</SPAN>

How can I have Excel/VBA go to the first open cell in column A before it pastes the 2^nd</SPAN> batch of information? I get an "object required" error with the MyRange variable.

Sub UpdateSortedTab()
Dim MyRange As Variant

' Removes Old Information
Sheets("Sorted").Select
Columns("A:E").Select
Selection.Delete Shift:=xlToLeft
' Copies Bench Stock Information
Sheets("Bench Stock").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sorted").Select
Range("A2").Select
ActiveSheet.Paste
' Sets Variable
Set MyRange = MyWorkSheet.Range("A2").End(xlDown).Offset(1, 0)
' Copies Work Order Residue
Sheets("Work Order Residue").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sorted").Select
Range(MyRange).Select
ActiveSheet.Paste
End Sub

 

[mirabeau]

I am trying to combine data from two separate worksheets onto one so it can be sorted for printing. Using the macro recorder, and the search function on the forum, I managed to ham-fist my way through most of it…except for one issue.

How can I have Excel/VBA go to the first open cell in column A before it pastes the 2^nd batch of information? I get an "object required" error with the MyRange variable.

Sub UpdateSortedTab()
Dim MyRange As Variant

' Removes Old Information
Sheets("Sorted").Select
Columns("A:E").Select
Selection.Delete Shift:=xlToLeft
' Copies Bench Stock Information
Sheets("Bench Stock").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sorted").Select
Range("A2").Select
ActiveSheet.Paste
' Sets Variable
Set MyRange = MyWorkSheet.Range("A2").End(xlDown).Offset(1, 0)
' Copies Work Order Residue
Sheets("Work Order Residue").Select
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("Sorted").Select
Range(MyRange).Select
ActiveSheet.Paste
End Sub

Have you tried defining "MyWorkSheet"? I don't see where you've done this.

Also, you could almost halve the length of your code by leaving out all the "Select", "Selections" etc. These are unnecessary and just confuse matters for any reader, the code writer, and maybe even the code itself.

 

[Huey462]

Probably not the most efficient method, but the following below works. As always if someone has a better way I'm always willing to learn

Thanks in advance

Sub UpdateSortedTab()
Dim MyRange As Variant

' Removes Old Information
Sheets("Sorted").Select
Columns("A:E").Select
Selection.Delete Shift:=xlToLeft
' Copies Bench Stock Information
Sheets("Bench Stock").Select
Range("A2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sorted").Select
Range("A2").Select
ActiveSheet.Paste
' Copies Work Order Residue
Sheets("Work Order Residue").Select
Range("A2:E2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Sorted").Select
Range("A2").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
' Formats Cells
Call SortedFormat

Range("A2").Select
End Sub

 

[shg]

Another way:

Sub UpdateSortedTab()
    Worksheets("Sorted").Columns("A:E").Delete
    
    With Worksheets("Bench Stock")
        .Range("A2", .Range("A2").End(xlToRight).End(xlDown)).Copy _
        Destination:=Worksheets("Sorted").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    End With
    
    With Worksheets("Work Order Residue")
        .Range("A2:E2", .Range("A2:E2").End(xlDown)).Copy _
        Destination:=Worksheets("Sorted").Cells(Rows.Count, "A").End(xlUp).Offset(1)
    End With
    
    Application.Goto Sheets("Sorted").Range("A2")
    Call SortedFormat
End Sub

 

[Huey462]

Thank you very much.

 

[shg]

You're welcome, good luck.