Copy Range of Cells And Paste Until Last Row

Unexpc

Board Regular
Joined
Nov 12, 2020
Messages
119
Office Version
  1. 2019
Platform
  1. Windows
Hi guys
i have a page in range A41:H74 and i want copy this page and paste until last row of excel sheet. does any vba code to do this?
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows
For rows with data, is column A always populated?
If so, you can try:
VBA Code:
    Dim lr as Long
'   Find last row in column A with data
    lr = Cells(Rows.Count,"A").End(xlUp).Row
'   Copy range
    Range("A41:H" & lr).Copy
    ...

If row 40 is blank, there are no totally blank rows in the middle of your data, and the data does not go out past column H, you may be able to use Current Region, i.e.
VBA Code:
    Range("A41").CurrentRegion.Copy
    ...
 

Unexpc

Board Regular
Joined
Nov 12, 2020
Messages
119
Office Version
  1. 2019
Platform
  1. Windows
i want paste unitl last row in excel, which line of code?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

command error for xlup...
Did you copy/paste or try typing it in?
A common error that people make is to type the number "1" instead of a lower case "l" in "xlUp".
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are you doing in Excel or Google Sheets?
What is the exact error message?
Please post your current code, exactly as you have it.
 

Unexpc

Board Regular
Joined
Nov 12, 2020
Messages
119
Office Version
  1. 2019
Platform
  1. Windows
VBA Code:
Sub CopyPaste()
        Dim lr As Long
        '   Copy range
    Range("A41:H74" & lr).Copy
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row


    End Sub

no problem for error command(not show xlUp error again), but not paste it, which line of code for paste until last row?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
55,457
Office Version
  1. 365
Platform
  1. Windows
Why did you flip the order of the lines of code I gave you?
You are trying to use the "lr" variable in the copy command before you calculate it/
You must calculate "lr" first before you can use it!

I also left it to you to enter where you want to copy it to (you did not mention where this should be copied).
So you still need the paste postion of the code. If you need help writing that, you need to tell us how to determine where to paste this to.
 

Unexpc

Board Regular
Joined
Nov 12, 2020
Messages
119
Office Version
  1. 2019
Platform
  1. Windows
i have a page from row 41:41 to 74:74 with specific format and data
in my row height , each page have 34 row and i have 8 column that fitted like my page in A41:H74
i want copy from this page and pasted in each page until rows excel is ended (row 1048576)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,781
Messages
5,574,218
Members
412,578
Latest member
jamiedaka
Top