For Next loop through columns

[wardamneagle21]

I have what i assume is a simple question but still new to VBA so i am struggling. I have a couple loops that do exactly what i need them to do in the first row, i just now need it to do the same thing across columns. I figured i would need to do a for next loop as the number of columns are known (117). Examples i have seen online essentially show the same value for the loop

(Sheets("sheet1").Cells(1,column).value = "Yay")

and I dont need that. I will attach the VBA screen shot as well as the excel screenshot. Preferably when the information starts to come over from machine 2 it will just fall in line under the information that machine 1 has already put in.

Any help with this would be greatly appreciated!

 

[mumps]

It is hard to work with pictures. It would be easier to help if you could use the XL2BB add-in (icon in the menu) to attach screenshots (not pictures) of Sheet3 and Sheet6. Alternately, you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary).

 

[wardamneagle21]

Unfortunately those sites are blocked by my work computer and i cant download the xl2bb add in, so photos are the only way i can go. I have fixed one issue but now have another. I have updated the code and it cycles through the columns but the issue now is that the values in the second and so on columns are staggered. Meaning there is one set of data in the first column and it pulls it without a problem but when it gets to the second column the first record is essentially ignored and it goes to the second record. Column C gets ignored because it only has one record and then finally Column G only the third record is picked up. I have attached pictures and the yellow highlighted values seem to be ignored. I was wondering what would be wrong with my code that the values are staggered like this? I am assuming it is something with the "x=x+2" line but i am unsure. Again any help is greatly appreciated!

 

[Fluff]

When posting code, please post the actual code, rather than an image. Otherwise people have to re-type everything.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

 

[wardamneagle21]

When posting code, please post the actual code, rather than an image. Otherwise people have to re-type everything.

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

Public Sub CopyPaste()
Dim x, x2, y, y1, y2, z1, z2, z3, c As Integer
x = 2
x2 = 3
y = 1
y1 = 1
y2 = 2
z1 = 1
z2 = 2
z3 = 3
For c = 1 To 117
        Do Until IsEmpty(Cells(x, c))
        Sheets("Sheet1").Cells(x, c).Copy
        Sheets("Sheet2").Cells(y, 1).PasteSpecial
        x = x + 2
        y = y + 1
        Loop
       
        Do Until IsEmpty(Cells(x2, c))
        Sheets("Sheet1").Cells(x2, c).Copy
        Sheets("Sheet2").Cells(y1, 2).PasteSpecial
        x2 = x2 + 2
        y1 = y1 + 1
        Loop
Next c
End Sub

I apologize!

 

[Alex Blakenburg]

I would probably use a For/Next Loop to do the Rows but using your code here are the changes I suggest you make:
Note: "Dim x, x2, y, y1, y2, z1, z2, z3, c As Integer" - only declares c as Integer and everything else will default to variant.

Public Sub CopyPaste_OP()
    Dim x As Long, x2 As Long, y As Long, y1 As Long, y2 As Long
    Dim z1 As Long, z2 As Long, z3 As Long, c As Long
  
    Application.ScreenUpdating = False
    x = 2
    x2 = 3
    y = 1
    y1 = 1
    y2 = 2
    z1 = 1
    z2 = 2
    z3 = 3
    For c = 1 To 117
        x = 2                   ' Needs to be reset with each new column
        x2 = 3                  ' Needs to be reset with each new column
        Do Until IsEmpty(Sheets("Sheet1").Cells(x, c))
            Sheets("Sheet1").Cells(x, c).Copy
            Sheets("Sheet2").Cells(y, 1).PasteSpecial
            x = x + 2
            y = y + 1
        Loop
      
        Do Until IsEmpty(Sheets("Sheet1").Cells(x2, c))
            Sheets("Sheet1").Cells(x2, c).Copy
            Sheets("Sheet2").Cells(y1, 2).PasteSpecial
            x2 = x2 + 2
            y1 = y1 + 1
        Loop
  
    Next c
      
    Application.ScreenUpdating = True
End Sub

 

[Alex Blakenburg]

If you open to an alternative, this is about 10x faster.

Sub CopyPaste()
    Dim srcSht As Worksheet, destSht As Worksheet
    Dim srcLRow As Long, destLRow As Long
    Dim srcLCol As Long, j As Long, i As Long
    
    Application.ScreenUpdating = False
    
    Set srcSht = Sheets("Sheet1")
    srcLCol = 117
    Set destSht = Sheets("Sheet2")
    
    destLRow = destSht.Range("A" & Rows.Count).End(xlUp).Row
    If destLRow = 1 And destSht.Cells(destLRow, 1) = "" Then destLRow = 0
    
    For j = 1 To srcLCol
        srcLRow = srcSht.Cells(Rows.Count, j).End(xlUp).Row
        If srcLRow <> 1 Then
            For i = 2 To srcLRow Step 2
                destLRow = destLRow + 1
                destSht.Cells(destLRow, 1).Value = srcSht.Cells(i, j).Value
                destSht.Cells(destLRow, 2).Value = srcSht.Cells(i + 1, j).Value
            Next i
        End If
    Next j
    destSht.Columns("B").NumberFormat = "h:mm:ss AM/PM"
    Application.ScreenUpdating = True
    
End Sub

 

[wardamneagle21]

I would probably use a For/Next Loop to do the Rows but using your code here are the changes I suggest you make:
Note: "Dim x, x2, y, y1, y2, z1, z2, z3, c As Integer" - only declares c as Integer and everything else will default to variant.

Public Sub CopyPaste_OP()
    Dim x As Long, x2 As Long, y As Long, y1 As Long, y2 As Long
    Dim z1 As Long, z2 As Long, z3 As Long, c As Long
 
    Application.ScreenUpdating = False
    x = 2
    x2 = 3
    y = 1
    y1 = 1
    y2 = 2
    z1 = 1
    z2 = 2
    z3 = 3
    For c = 1 To 117
        x = 2                   ' Needs to be reset with each new column
        x2 = 3                  ' Needs to be reset with each new column
        Do Until IsEmpty(Sheets("Sheet1").Cells(x, c))
            Sheets("Sheet1").Cells(x, c).Copy
            Sheets("Sheet2").Cells(y, 1).PasteSpecial
            x = x + 2
            y = y + 1
        Loop
     
        Do Until IsEmpty(Sheets("Sheet1").Cells(x2, c))
            Sheets("Sheet1").Cells(x2, c).Copy
            Sheets("Sheet2").Cells(y1, 2).PasteSpecial
            x2 = x2 + 2
            y1 = y1 + 1
        Loop
 
    Next c
     
    Application.ScreenUpdating = True
End Sub

This worked Perfectly thank you so much! I will try the other one too! Appreciate you!

 

[Alex Blakenburg]

Thanks for letting us know. Glad we could help.