Copy range problems

[DaveUK]

Please can somebody tell me how i can copy ONLY the cell values from a range which starts on Workbook "Book1", Worksheet "Dump" at Cell A3.

The data i need to copy has blank cells but i have a fixed number of columns i need to copy. (From Column A - Column Q).

I need to copy an unspecified number, starting at cell "A3" ,of Rows (as this is going to vary).

the code i used was this

Sub ProcCopy()

Workbooks("Book1.xls").Worksheets("Dump"). _
  Range("A3").CurrentRegion.Copy
Worksheets("Processing").Range("A1").PasteSpecial

End Sub

However this also copied rows 1 & 2 which i do not want to copy.

I only want to copy from "A3" down and across to column Q. Also i do NOT want to copy the format, just the Values.

Please help !!!

TIA

 

[erik.van.geit]

Hello, DaveUK,

try this

Sub ProcCopy()
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
    With Workbooks("Book1.xls")
    .Worksheets("Dump").Range("A3:Q" & LR).Copy
    .Worksheets("Processing").Range("A1").PasteSpecial Paste:=xlValues
    End With
Application.CutCopyMode = False
End Sub

OR just

Sub ProcCopy()
Dim LR As Long
LR = Cells(Rows.Count, 1).End(xlUp).Row
    Worksheets("Dump").Range("A3:Q" & LR).Copy
    Worksheets("Processing").Range("A1").PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End Sub

kind regards,
Erik

 

[DaveUK]

Many thanks Erik,

Just made the small change form "xlUp" to "xlDown" and it works great.

Regards

 

[erik.van.geit]

Just made the small change form "xlUp" to "xlDown" and it works great.

OK, I see you were using current region, so you don't want all data to the last one but just till the first empty cell

 

[DaveUK]

Just found another problem.

On Workbook"Book1", Worksheet"DumpTemp" i need to copy all data to the end row, but there are empty rows throughout the data. Also the data is currently at 12,000 rows (including blanks).

Please advise how to copy all data (including empty row) to the workbook"Book2", Worksheets("Processing") and start at Cell "A1".
I only want to copy the values NOT the formats!

TIA

 

[erik.van.geit]

I think the changes you made "up to down" are the cause
just use the macro you received