copy/paste macro

[Pegs]

Hi,

I need a macro which copies a range of cells from one page and pastes them to another. This is obviously very straightforward, however I need the range to be partially determined by a number on the page that the range is to be copied from.

Specifically, Sheet1 is going to contain data spread across 6 columns, but an indeterminate number of rows. So I need my macro to copy from A1 to Fn where n = the number of rows containing data (I was going to generate n on the sheet by using a simple COUNT or COUNTIF function).

For the PASTE part of the macro, I need it to select the appropriate sheet (easy enough) and then search for and select the first empty row beneath a certain row (say 10, beginning at cell A10) before pasting.

Thanks in advance for any help with this.

[royUK]

That's not necessary. This code will copy the cells in Columns A to E that contain data to the next empty row in sheet2

Please Login or Register  to view this content.

[Pegs]

Thanks Roy. My experience with VB is pretty limited. I insert this as part of the macro or as part of the Sheet1 code? Sorry for being thick.

[Pegs]

Ok I tried the code that Roy posted as a macro, and was given the error message:

Compile error

Sub or function not defined.

with the highlighted part:

Dim rToCopy As Range
Dim NextRw As Long
With Sheet1
Set rtoocopy = .Range(.Cells(1, 1), .Cells(.Rows.Count, 5).End(xlUp))
End With
With sheet2
NextRw = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
rcopy .Cells(NextRw, 1)
End With