CODEWITHSUNDEEP
excelvba
Cole Snody
Cole Snody

Reputation: 7

Moving certain cells between sheets

I have an excel file where I need to take the dates out of the sheet named "Simulator Data" and place them into a sheet named "Consolidated Data" using VBA. The dates in "Simulator Data" start in the 7th row and then appear again every 31 rows after that in columns D, E, and F (year, month, day in separate columns), and I want to move them to "Consolidated Data" starting in row 1 with step=10 and in columns B, C, and D.

I have been able to find some code, but when I try to run it all it does is copy the last date in the "Simulator Data" into every tenth row in "Consolidated Data". The code I have only focuses on the year cells that is why it is going from D in "Simulator Data" to B in "Consolidated Data". What changes do I need to make so that it will work?

Here is my code

Sub consolidate()
Dim lastrow As Long
lastrow = ThisWorkbook.Sheets("Simulation Data").Range("B65356").End(xlUp).row

Dim srow As Long
Dim crow As Long
For srow = 7 To lastrow Step 31
    For crow = 1 To lastrow Step 10
        Sheets("Consolidated Data").Range("B" & crow) = Sheets("Simulation Data").Cells(srow, "D")
    Next crow
Next srow

End Sub

Upvotes: 0

Views: 26

Answers (1)

Tim Williams
Tim Williams

Reputation: 166316

Not sure you really want that nested loop?

Sub consolidate()

    Dim lastrow As Long
    Dim srow As Long
    Dim crow As Long
    Dim wsSim As Worksheet, wsCons As Worksheet

    Set wsSim = ThisWorkbook.Sheets("Simulation Data")
    Set wsCons = ThisWorkbook.Sheets("Consolidated Data")
    lastrow = wsSim.Range("B65356").End(xlUp).Row

    crow = 1             '<< destination start row
    For srow = 7 To lastrow Step 31
        wsSim.Cells(srow, "D").Resize(1, 3).Copy wsCons.Range("B" & crow)
        crow = crow + 10 '<< increment destination row
    Next srow

End Sub

Upvotes: 1

Related Questions