Copy range to end of data and paste into different sheet

pandagurl

New Member
Joined
Apr 13, 2021
Messages
5
Office Version
  1. 2010
Platform
  1. Windows
I've created a macro to copy a range of data from one worksheet to another and it works fine. However, I'd like to change this so that it copies through to the last row of data rather than specifying a numbered row.

This is what I have so far:

VBA Code:
Sub Macro2()

Worksheets("Raw Data Line Items").Range("A4:D4").Copy

Worksheets("Transformed").Range("A26").PasteSpecial
    
End Sub


This also works (from a Macro recording), but it's slow and clunky:

VBA Code:
Sub Copy()

    Sheets("Raw Data Line Items").Select
    Range("A4:D4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Sheets("Transformed").Select
    Range("A26").Select
    ActiveSheet.Paste
 
End Sub


Thanks!
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Macro2 revamped with a single codeline : ['Raw Data Line Items'!A4:D4].Copy Sheets("Transformed").Cells(Rows.Count, 1).End(xlUp)(2) …​
 
Upvote 0
Thanks Marc. That works to copy/paste the first line of data, but I'm looking for it to copy however many rows have data in A:D on the Raw Data Line Items sheet (like my Copy macro above).
 
Upvote 0
Different ways, depending on your missing attachment …​
 
Upvote 0
Does this help?

OS Creation Template (Amanda) Simplified #21.xlsm
ABCD
24
25Payment NumberInvoice NumberInvoice DateDescription
26
27
Transformed


OS Creation Template (Amanda) Simplified #21.xlsm
ABCD
1
2Invoices
3Payment NumberInvoice NumberInvoice DateDescription
41928473788051806500551_64301/17/2022JAN-22-RETURNS
51928473787917188648551_64301/17/2022JAN-22-RETURNS
61928473787933512632551_64301/17/2022JAN-22-RETURNS
71928473788074999197551_64291/17/2022JAN-22-RETURNS
81928473787917079724551_64301/17/2022JAN-22-RETURNS
Raw Data Line Items
 
Upvote 0
According to your attachment a VBA demonstration for starters :​
VBA Code:
Sub Demo1()
    With ['Raw Data Line Items'!A1].CurrentRegion.Rows
        If .Count > 3 Then .Item("4:" & .Count).Copy Sheets("Transformed").Cells(Rows.Count, 1).End(xlUp)(2)
    End With
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top