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

Question

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?

I proposed an edit that removed the <br /> and added code formatting to make your post more readable. Please double check that I didn't accidently delete a character around these two lines: sh.Range("B1:M247").Copy and _Destination:=shArc.Range("A" & lRow) Correct if needed. — matisetorm
– matisetorm, Commented Feb 25, 2018 at 12:57
So i tried editing the code with one of the replies here - it was in the correct orientation, but it only took the first column from each sheet. Each of the sheets I have 13 columns though. — bossgos
– bossgos, Commented Feb 25, 2018 at 13:30
I was just trying to edit to make the question more readable. Want to make sure I didn't add an error in the code block. — matisetorm
– matisetorm, Commented Feb 25, 2018 at 13:46

matisetorm · Accepted Answer · 2018-02-25 14:11:44Z

1

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

edited Feb 25, 2018 at 14:11

matisetorm

8538 silver badges21 bronze badges

answered Feb 25, 2018 at 13:39

bossgos

313 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related