Error Copying Data

[trevb]

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]

Without actually seeing your workbook, you could try something like:


Range("A4").CurrentRegion.Copy

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

[trevb]

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]

Worksheets("Sheet1").Range("a4:e8").Copy
With Worksheets("Sheet2")
.Paste Destination:=Worksheets("Sheet2").Range("g2")
.Activate
.Range("G2").Select
End With

[trevb]

Thanks very much both, works fine

[mdmackillop]

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]

mdmackillop, thanks for the tip, very useful, works a treat

[geekgirlau]

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]

geekgirlau, have just tried that, seems 'cleaner' and its makes it easier to follow the code, thanks very much for the tip