[RESOLVED] Looping to next column

[summer0897]

I am looking to use a loop to populate the next column and am difficulties. The only way I am able to get it to work is to just past a new column each time, but that is making the order of the looping go backwards. Is there a way to move through columns and not have to insert a column? Any help is greatly appreciated. TIA
Here is the code:

'lets loop
While dTempStart < dEnd

'create a new column for the data
Sheets("Setup").Select
Columns("F:F").Select
Selection.Insert Shift:=xlToRight

'plug the dates into the worksheet
Worksheets("Setup").Range("C3").Value = dTempStart
Worksheets("Setup").Range("C4").Value = dTempEnd

'put worksheet output into the new column
Sheets("Setup").Select
Columns("C:C").Select
Selection.Copy
Columns("F:F").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Range("E11").Select

'setup for next iteration of the loop
dTempStart = dTempEnd + 1
dTempEnd = dTempEnd + interval - 1
Wend

[RobDog888]

Looks like your trying to past a column of data but why are you inserting a new one? Why not just change the destination column of the paste?

[summer0897]

Instead of inserting a new column and using that column continually, I would like to paste into the next column, but am unsure of how to get the loop to go to the next column.

When moving to the next row, you can just create something like this, but as far as moving to the next column, I am a little lost.

Worksheets("Setup").Range("B13").Value = "N"
i = 4
While Worksheets("PrintList").Range("D" + CStr(i)).Value > ""
Worksheets("Test").Range("B2").Value = Worksheets("PrintList").Range("D" + CStr(i)).Value
Worksheets("Printlist").Range("E" + CStr(i)).Value = Worksheets("Test").Range("G40").Value
Worksheets("Printlist").Range("F" + CStr(i)).Value = Worksheets("Test").Range("G42").Value
Worksheets("Printlist").Range("G" + CStr(i)).Value = Worksheets("Test").Range("K40").Value
Worksheets("Printlist").Range("H" + CStr(i)).Value = Worksheets("Test").Range("K42").Value
Worksheets("Printlist").Range("R23").Value = i
i = i + 1

[DKenny]

The following version of your original code will copy to the next free column of the "Setup" worksheet. It assumes that you can determine which column is the starting "copy to" column. (I used colum F)
I've added a Long variable "lPasteColumn" to store the column number, you will need to add this to you variables declaration section.
All that happens is that as you loop you increment the column number by one to move to the next column.
Also, I have replaced your WHILE...WEND with a DO..LOOP which is a better loopinf structure.

'Assume Column F is the first column
'to copy to
lPasteColumn = 6

'lets loop
Do While dTempStart < dEnd

With Sheets("Setup")
'Change the Start and End Dates
.Range("C3").Value = dTempStart
.Range("C4").Value = dTempEnd

'Copy Column C to the Destination column
.Columns(4).Copy
.Columns(lPasteColumn).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
End With

'Clear the Clipboard
Application.CutCopyMode = False


'setup for next iteration of the loop
dTempStart = dTempEnd + 1
dTempEnd = dTempEnd + interval - 1
lPasteColumn = lPasteColumn + 1
Loop

[summer0897]

DKenny~

Thank you, that worked. I was definately struggling with the move to next column.

Summer0897

[DKenny]

OK, glad I could help. Don't forget to mark this thread as resolved if your question has been answered. its under "Thread Tools" at the top of the page.