Edit VBA Code to Paste Data in 1st empty row

[PRS25]

Hello Experts -

The following code copies and pastes data between workbook sheets. Can someone please modify so it pastes in the first empty row? It currently pastes in the 2nd row.

Set zDest1 = sht01.Cells(2, 1) 
zSource.Sheets(4).[a1].CurrentRegion.Offset(2).Copy zDest1
Set zDest1 = sht01.Cells(Rows.Count, 1).End(xlUp).Offset(1)

'Preciate everyone on this forum helping others

 

[Joe4]

That is because it is looking for the last row with data, and then moving down 1 row.
However, if there is no data at all in column A, it goes to the top (row 1) and then moves down 1 row.
You can simply add a line after the copy/paste step that will delete the top blank line, if one exists, i.e.

[COLOR=#ff0000]If sht01.Cells(1, 1) = "" Then Rows(1).Delete[/COLOR]

 

[PRS25]

Hi Joe,

Thanks for your response. I added your line below the copy/paste step, however the import still pasted over the values in the first 3 rows.

 

[Joe4]

Try changing your code like this:

Set zDest1 = sht01.Cells(Rows.Count, 1).End(xlUp).Offset(1)
zSource.Sheets(4).[a1].CurrentRegion.Offset(2).Copy zDest1
If sht01.Cells(1, 1) = "" Then Rows(1).Delete

 

[PRS25]

Nothing was pasted with that code

 

[Joe4]

Please post your complete code, so we can try to recreate your scenario.
Also, describe the data you are copying. You can post images if you like using the tools mentioned in Section B here: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

 

[PRS25]

Thanks for your help, Joe. Happy to report I figured it out by adjusting the first line to:

Set zDest1 = sht01.Cells(1, 1).End(xlDown).Offset(1)

 

[Joe4]

Thanks for your help, Joe. Happy to report I figured it out by adjusting the first line to:

Set zDest1 = sht01.Cells(1, 1).End(xlDown).Offset(1)
​

Note that there are limitations to that!
If there are no entries below row 1 in column A, this will return errors!