Final Row Error

Huey462

Board Regular
Joined
Jul 25, 2011
Messages
147
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 2nd</SPAN> batch of information? I get an "object required" error with the MyRange variable.

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

mirabeau

Banned user
Joined
Nov 4, 2010
Messages
2,075
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 2nd batch of information? I get an "object required" error with the MyRange variable.

Code:
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

Board Regular
Joined
Jul 25, 2011
Messages
147
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

Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Another way:

Code:
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
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
You're welcome, good luck.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,567
Messages
5,596,908
Members
414,110
Latest member
docops

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top