VBA copy 2 lists on different sheets & different l

Question

I have a partial solution to what I am asking for, but I need some help to make it complete. I have not found anyone who had the same situation, and I am not enough of a pro at this point to get where I need to on my own. Using the below VBA, I can move rows from one sheet to another based on criteria in column A quickly and easily. However, I need to accomplish this using the same criteria on a second sheet at the same time so I can compare the data between 2 different time periods. To make this slightly tougher, it'd be fantastic if the columns could dynamically come one after another - for example, if i have a column labeled 2016 anything, it would be followed by its 2046 companion. I am working with fewer columns on the 2046 sheet, all of which would have a mate on the 2016 sheet. Thanks for any help you guys can provide!

Sub columntosheets()

Const sname As String = "Sheet1" 'change to whatever starting sheet
Const s As String = "A" 'change to whatever criterion column
Dim d As Object, a, cc&
Dim p&, i&, rws&, cls&
Set d = CreateObject("scripting.dictionary")
With Sheets(sname)
    rws = .Cells.Find("*", , , , xlByRows, xlPrevious).Row
    cls = .Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    cc = .Columns(s).Column
End With
For Each sh In Worksheets
    d(sh.Name) = 1
Next sh

Application.ScreenUpdating = False
With Sheets.Add(after:=Sheets(sname))
Sheets(sname).Cells(1).Resize(rws, cls).Copy .Cells(1)
.Cells(1).Resize(rws, cls).Sort .Cells(cc), 2, Header:=xlYes
a = .Cells(cc).Resize(rws + 1, 1)
p = 2
For i = 2 To rws + 1
    If a(i, 1) <> a(p, 1) Then
        If d(a(p, 1)) <> 1 Then
            Sheets.Add.Name = a(p, 1)
            .Cells(1).Resize(, cls).Copy Cells(1)
            .Cells(p, 1).Resize(i - p, cls).Copy Cells(2, 1)
        End If
        p = i
    End If
Next i
Application.DisplayAlerts = False
    .Delete
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End With
Sheets(sname).Activate

End Sub

Answer 1

While I didn't have the exact same situation as you, I had to do a somewhat related task of reading in two separate data ranges, performing some comparison tests on those ranges, and sending the results to two separate fields depending on the results of the tests. What I learned from that is that it is much better to read in ranges as single arrays, doing all the background work on those arrays, and only sending the data back to Excel once the calculations have been completed. In total I was working with four arrays: one for the data range, one for the test range, one for the passed tests and one for the failed tests. This was not a trivial task, but it proved to be a lot faster than trying to work with individual cells, and it gave me much better control over the data.

From your description here, it sounds like you might benefit from a similar strategy - read in your two datasets into separate arrays, do what ever lookups or comparisons you need, store the results in a third array, and send that array back to Excel. I hope this gives you some ideas how to approach this one.