PDA

View Full Version : Error Copying Data



trevb
09-23-2009, 11:40 PM
Hi
I'm trying to copy the data in 5 columns to another sheet but keep getting a runtime error, can anyone please tell me what is wron with the code below ?


Range("A4").Select
Range(Selection, Selection.End(xlDown)).Resize(, 6).Select
'ActiveCell.Resize(, 6).Select
Selection.Copy

Sheets("Data2").Activate
Range("G3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("G2").Select

geekgirlau
09-24-2009, 12:11 AM
Without actually seeing your workbook, you could try something like:


Range("A4").CurrentRegion.Copy

Sheets("Data2").Activate
Range("G3").PasteSpecial Paste:=xlPasteValues

trevb
09-24-2009, 12:22 AM
The data I'm trying to copy is 81 columns wide and varies from 1200 to 4000 rows generally. Before I copy I sort so that the data I want to copy is the first five columns, so really from cell A4 all I need to do is select using xldown and move across 5 columns, but I can't get it to work

Tinbendr
09-24-2009, 05:21 AM
Worksheets("Sheet1").Range("a4:e8").Copy
With Worksheets("Sheet2")
.Paste Destination:=Worksheets("Sheet2").Range("g2")
.Activate
.Range("G2").Select
End With

trevb
09-24-2009, 05:32 AM
Thanks very much both, works fine

mdmackillop
09-24-2009, 05:49 AM
Just to answer your question, you need to specify the sheet in selecting the range, otherwise the code is trying to select G3 on the original sheet.

ActiveSheet.Range("G3").Select

trevb
09-24-2009, 06:01 AM
mdmackillop, thanks for the tip, very useful, works a treat

geekgirlau
09-24-2009, 06:03 PM
I find it's a good idea to name each sheet in the VBE window, and explicitly refer to the sheet pretty much always (unless I'm being very lazy :snore: ).

This way

Worksheets("Sheet1").Range(...

becomes

shData.Range(...

Explicity referring to the sheet means that you are always in control of exactly which range on which sheet you are referring to. There are a couple of circumstances where you need to activate a specific sheet in order for some process to work correctly, but most of the time you can perform an action without having to select either the sheet or the cells first.

trevb
09-25-2009, 02:31 AM
geekgirlau, have just tried that, seems 'cleaner' and its makes it easier to follow the code, thanks very much for the tip