CODEWITHSUNDEEP
excelvba
bossgos
bossgos

Reputation: 31

Troubleshoot: Copy Data from multiple sheets into a single sheet in VBA

I have the following code which currently works, but does not display the way I want it to. I am new to VBA so I used this template from the web.

It makes a sheet called "Archive" then prints all the data I have in 40 other sheets onto it. The problem is that it from reads top to bottom.

Public Sub m()
    Dim lRow As Long
    Dim sh As Worksheet
    Dim shArc As Worksheet
    Set shArc = ThisWorkbook.Worksheets("Archive")
    For Each sh In ThisWorkbook.Worksheets
        Select Case sh.Name
            Case Is <> "Archive"
                lRow = shArc.Range("A" & Rows.Count).End(xlUp).Row
                sh.Range("B1:M247").Copy 
                _Destination:=shArc.Range("A" & lRow)
        End Select
    Next
    Set shArc = Nothing
    Set sh = Nothing
End Sub

I want the macro to paste the data so that it is read from from left to right.

TLDR: code gathers data but pastes it all vertically. I want it to paste horizontally. can anyone alter it?

Upvotes: 2

Views: 73

Answers (1)

bossgos
bossgos

Reputation: 31

So i tried messing around some more and edited one of the earlier replies. This seems to work for my purposes at the moment

Public Sub m()
    Dim lCol As Long
    Dim sh As Worksheet
    Dim shArc As Worksheet
    Set shArc = ThisWorkbook.Worksheets("Archive")
    For Each sh In ThisWorkbook.Worksheets
        Select Case sh.Name
             'do nothing
        Case Else
            lCol = shArc.Cells(1, shArc.Columns.Count).End(xlToLeft).Column
            sh.Range("B1:M247").Copy _
              Destination:=shArc.Cells(1, lCol + 13)
    End Select
Next
    Set shArc = Nothing
    Set sh = Nothing
End Sub

Upvotes: 1

Related Questions