Copy range problems

DaveUK

Board Regular
Joined
Jan 24, 2005
Messages
245
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

Code:
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, DaveUK,

try this
Code:
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
Code:
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

Board Regular
Joined
Jan 24, 2005
Messages
245
Many thanks Erik,

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

Regards
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
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

Board Regular
Joined
Jan 24, 2005
Messages
245
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

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
I think the changes you made "up to down" are the cause
just use the macro you received
 

Forum statistics

Threads
1,078,094
Messages
5,338,193
Members
399,212
Latest member
Braincraft

Some videos you may like

This Week's Hot Topics

Top