Copying Columns from one sheet to another

[IP AUM]

Hi there,

I'm a complete VBA newbie. I've tried googling methods to copy columns across, but keep getting the compile expected end sub error. I've also found people recommending very different ways of doing it. I've tried searching this forum but didn't find precisely what I need.

I'm literally just trying to copy columns say A, B, C in Sheet 1 to columns D, E F in Sheet 2.

Many thanks.

[Richard Buttrey]

Hi, and welcome to the forum.

Do the columns you're copying contain formulae and if so should they be pasted as formulae or as constant values?

[IP AUM]

Ah yes, they are just values. I have just tried the following methods,

"Sheets("Sheet1").Range("A1:B10").Copy
'Activate the destination worksheet
Sheets("Sheet2").Activate
'Select the target range
Range("E1").Select
'Paste in the target destination
ActiveSheet.Paste

Application.CutCopyMode = False"


AND


Sheets("Sheet1").Range("A1:B10").Copy Destination:=Sheets("Sheet2").Range("E1")



For both I am now receiving "Run-time error 9".


Many thanks.

[IP AUM]

My apologies for wasting any of your time but I've managed to fix it, I realised the version 1 I had to put a range into "Range", a crazy revelation for me.

Thank you for your time, and feel free to delete this useless thread for others.

IP

[Richard Buttrey]

Hi,

Not quite sure which code you are using but as a general point try and avoid using sheet tab names in VBA code. They can be changed too easily by a user causing a macro which uses them to fall over.
Use instead the VBA sheet CODE name.

[IP AUM]

Excellent point, how do I change the code so that it chooses the underlying sheet code?

Edit: I should add that I tried the obvious, changing the code within the parentheses from the tab name to "Sheet7" etc to the following:

Sheets("Sheet7").Range("Q2:Q65").Copy

But it didn't like it, why?

IP