Macro containing For Each loop will step through correctly but won't run.

[antmud]

Hello,

I'm very new to VBA and decided to try and create a macro that loops through a selection of columns (in sheet "Data") and pastes them, one at a time, into a column in another sheet "CalcSheet". Once I'm successful I plan to do some work on the column while it's in sheet "CalcSheet".

My challenge is that I can step through the macro once (but not twice), however it won't run at all from the spreadsheet.

My code is as follows:
Sub CopyCols()

Dim aa As Range
Dim cc As Range
Set cc = Columns("p:s")
For Each aa In cc
Sheets("Data").Select
aa.Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CalcSheet").Select
Columns("d:d").Select
ActiveSheet.Paste
Next

End Sub

When it fails it fails at the line 'aa.select' with an error message 'Select Method of Range Class Failed'.

When it steps through the first time everything works fine for the four columns (p to s). If I continue stepping it fails as mentioned above. It will run once with F5 but fails on a second attempt. The worse part is that it fails immediately if I try and run it from the spreadsheet.

Is it my For Each loop? Or is it some other programming error I've created.

Many thanks, Anthony

 

[JLGWhiz]

A couple of adjustments. See what this does.

Code:
Sub CopyCols()
Dim aa As Range
Dim cc As Range
Set cc = Sheets("Data").Columns("p:s")
x=2
For Each aa In cc
Sheets("Data").Select
aa.Copy Sheets("CalcSheet").Range("D" & x)
x = x + 1
Next
End Sub
Code:

 

[antmud]

Thanks, I've replaced my code with yours but get an error message stating that the copy and paste area aren't the same size. I think that's because we're trying to paste a column to a cell (ie column P to cell D2). I also want to paste the column I'm working on into the same column (D column) in "CalcSheet" each time (replacing the previous column).

My other enigma is why should the macro step ok with the F8 button but not run when I run the macro?

 

[JLGWhiz]

I misinterpreted your intent. You need to qualify your Set cc statement like:

Code:
Sub CopyCols()
Dim aa As Range
Dim cc As Range
Set cc = Sheets("Data").Columns("p:s")
For Each aa In cc
aa.Copy
Sheets("CalcSheet").Select
Columns("d:d").Select
ActiveSheet.Paste
Next
End Sub
Code:

The other thing that I question is the posting of each iteration to column D in CalcSheet. Unless each column P thru S in Data is staggered vertically, the macro will overwrite and only the column S will be intact on CalcSheet. But since you indicated that it worked like you wnated manually, I will not bother with it. The modification to the Set cc statement should cure the error. I eliminated some of the unecessary Select and Selection statements to make it easier to read.

 

[antmud]

The modification to the Set statement did cure the error. Thanks very much for your efforts and advice. Anthony