Copy range from one sheet and paste in another sheet on the next visible row after header

Good day, hope someone can help me with a challenge I'm facing. I have recently started on VBA and have come across the following problem:

The VBA code to copy non-hidden range data from one worksheet, and paste to another worksheet is generally working. But the problem I have come across is that the destination sheet
has a header row and below that nothing but hidden rows. I would like the destination row to paste to to be the first row after the header row, which is basically the first visible row after all the hidden rows. The last row function will get me to the header row, but Lr + 1 is getting Excel/VBA to paste to the next HIDDEN row.

So far, the code I have is only working when I unhide the rows in the destination sheet first.

Can Excel/VBA paste to the next visible row??

Hope to get your solution, if any.

Best regards, MaQian.

This is assuming the first row is 1 and you're in column A. It will place the active cell below all of the hidden rows. You can then paste after that.

Please Login or Register  to view this content.

If you want it in the 5th column instead of column H, set the destination range to be E4 instead of H4.

Thanks guys, great help to finally get it working in the end. Storing the DestRange did the trick, although had a tweak a bit to make sure that Active cell had the right starting point in the right worksheet before going through the loop.

For completeness, final code below. Thanks again.

Please Login or Register  to view this content.