[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.