copypaste problem

[ndendrinos]

this works

Sheets("Records").Range("columns").Copy Destination:=Sheets("Statement").Range("A1")

but when I change the range to

Sheets("Records").Range("columns").Copy Destination:=Sheets("Statement").Range("A5")

it does not ...(copy area paste area not the same...)

There are no merged cells copied ... so what is the problem?
Thanks

 

[njimack]

How many rows is the range "Columns"?

 

[ndendrinos]

thanks,
all rows in columns
BTW would prefer to make it "dynamic"

=Records!$A:$A,Records!$B:$B,Records!$C:$C,Records!$E:$E,Records!$FS:$FS

 

[njimack]

Use a dynamic range - see http://contextures.com/xlNames01.html#Dynamic

This will stop the problem you're having, as well as probably making the file more efficient.

 

[lenze]

You are copying whole columns which is every row in each column. This works when copying to Row 1, but when you try to copy to Row 5, you run out of rows at the bottom of you worksheet!!! So, to make it dynamic, you need to copy only used rows. For us to avoid guessing, can you explain EXACTLY what you are doing
??
lenze

 

[ndendrinos]

thanks njimack I will look at the link
lenze:
This is to generate a statement for any given customer:
I import all records from sheet records (each rows = one invoice) then I filter by dates (from/to) and by customer name.
I've been doing this all along by importing all columns then deleting most to end up with just the 5

 

[ndendrinos]

Could solve it like this (deducted 5rows from copied range) but (again) would prefer a dynamic range

=Sheet1!$A$1:$A$65531,Sheet1!$C$1:$C$65531,Sheet1!$O$1:$O$65531

 

[lenze]

This can easily be done by finding the last row and only copying used range , but I don't have a handle as to what you want copied and where. If you are importing All records and then filtering to only certain oned by customer?) , why not just import the ones for that customer?? You can use Advanced Filter or MSQuery!!
lenze

 

[ndendrinos]

thanks Lenze,
What you are suggesting I do on sheet2 instead of sheet1
As to the "dynamic" range I don't see any benefit to it anymore since (in theory) if I fill Sheet1 with 65536 rows and tried to paste to sheet2 starting in A5 the code would fail for the reason you've explained.
Since at most (per year @ 10 measly invoices a day) I'll have just 3650 rows filled in sheet1 I don't foresee a problem.
Would have been nice to learn how to create a dynamic named range.
Checked @ contextures and as usual ended up unable to apply Debra's solution to my needs.

 

[Simon Lloyd]

If you are copying using vba why not set the range to be just the used portion of the column, like this

Dim MyRange As Range
Set MyRange = Range("A1:A" & Range("A" & Rows.Count).end(xlup).row)