VBA: Copy all rows with data from one sheet and paste on bottom of another

[Archived-1715]

Hi,

In sheet4 I have data from column A:AC.
it can have sometimes 10 rows of data or sometimes only 1 row of data it is always random.

I need help writing VBA macro to copy only all the rows with data only and paste it below the last cell with data on sheet 5.

Thanks alot.

 

[JLGWhiz]

Give this a try

Sub cpynpst()
Dim sh4 As Worksheet, sh5 As Worksheet, lr As long, rng As Range
Set sh4 = Sheets("Sheet4")
Set sh5 = Sheets("Sheet5")
lr= sh4.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh4.Range("A2:A" & lr)
rng.EntireRow.Copy sh5.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

 

[Archived-1715]

Thanks it a lot works but there is one problem, it paste everything in correct place except it shifts everything to left one after 5th column? how can this be fixed please.

 

[Archived-1715]

Give this a try

Sub cpynpst()
Dim sh4 As Worksheet, sh5 As Worksheet, lr As long, rng As Range
Set sh4 = Sheets("Sheet4")
Set sh5 = Sheets("Sheet5")
lr= sh4.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh4.Range("A2:A" & lr)
rng.EntireRow.Copy sh5.Cells(Rows.Count, 1).End(xlUp)(2)
End Sub

Hi

thanks alot it worked like a charm, I had some error in my other sheet. Thanks again

 

[JLGWhiz]

Thanks it a lot works but there is one problem, it paste everything in correct place except it shifts everything to left one after 5th column? how can this be fixed please.

There is nothing in the code provided above that would cause such a shift. Do you have event code in one of your sheets that might be activated by the change when items are pasted into it? If not, I have no clue what is happening.

 

[JLGWhiz]

Hi

thanks alot it worked like a charm, I had some error in my other sheet. Thanks again

You're welcome
Regards, JLG

 

[AliRizvi]

Hello JLGWhiz, the Code works like a charm, however there is bit of an issue, it copies all columns in from the source table. But i only want columns A to AQ. Your help is much appreciated.