Copy same range of columns from multiple sheets into summary sheet w/ different columns

Question

My apologies if this has already been posted. I've looked endlessly and have found similar topics but don't know how to manipulate the macro well enough to suit my needs.

I have 344 sheets of data. Within each sheet I have a specific range of data (AY125:AY158) that I would like to copy into a single sheet. What I would like is to paste this data into a summary sheet where the data range from Sheet 1 would be column A, sheet 2 would be column B, sheet 3 would be C etc etc.

My knowledge of VBA is very rudimentary, so any assistance on this would be great.

Answer 1

Fabricator is trying to help you solve your problems with formulae. This is probably the easiest approach if it meets your needs. I will show you a VBA approach.

You must break your requirement into small steps. Try to code a macro to perform step 1. Search for questions relevant to step 1. It is must easier to find questions and answers relating to a single issue. If you cannot get your macro to work, post your code with an explanation of what it does and what you want it to do. Small questions like this are often answered in minutes.

Having got step 1 working, try to upgrade your macro to perform steps 1 and 2 then steps 1, 2 and 3. You will be surprised how quickly you will make progress with this approach.

The first thing you need to do is cycle through all the worksheets. Try:

Option Explicit
Sub Try1()

  Dim InxW As Long
  
  For InxW = 1 To Worksheets.Count
    Debug.Print Worksheets(InxW).Name
  Next

End Sub

The above macro will list the name of every worksheet to the Immediate Window. The Immediate Window should be underneath the code area. If it is missing, click Ctrl+G.

The problem with this macro is that there is only room for 150 or so lines in the Immediate Window so the early names will have already scrolled off the top when the macro stops.

Now try:

Option Explicit
Sub Try2()

  Dim InxW As Long
  
  For InxW = 1 To Worksheets.Count
    Debug.Print Worksheets(InxW).Name
    If InxW Mod 100 = 0 Then
      Debug.Assert False
      Debug.print "-----------------"
    End If
  Next

End Sub

Every time InxW is a multiple of 100, the Debug.Assert False statement will be reached and will stop execution. This will allow you to examine the last 100 worksheets listed.

Are the worksheets being listed in the sequence you want?

Option Explicit
Sub Try3()

  Dim InxW As Long
  Dim WshtName As String
  
  For InxW = 3 To 1 Step -1
    WshtName = "Sheet" & InxW
    Debug.Print Worksheets(WshtName).Name
    If InxW Mod 100 = 0 Then
      Debug.Assert False
      Debug.Print "-----------------"
    End If
  Next

End Sub

You can access worksheets by name as well as by index. If your sheets are named "Sheet1" to "Sheet344", this macro displays the names in reverse order.

By the time I got to this point, you had told Fabricator you wanted to use District codes. According to Wikipedia, each US State has a different number of districts. If the sheets are not in order you want you will have to generate the names. The following might be the easiest way:

Option Explicit
Sub Try5()

  Dim InxDist As Long
  Dim InxState As Long
  Dim NumDist As Long
  Dim StateDtl() As Variant
  Dim StateDtlPart() As String
  
  StateDtl = Array("Alabama|AL|7", "Alaska|AK|1", "Arizona|AZ|9", "Arkansas|AR|4")
  
  NumDist = 0
  For InxState = LBound(StateDtl) To UBound(StateDtl)
    StateDtlPart = Split(StateDtl(InxState), "|")
    Debug.Print StateDtlPart(0)
    For InxDist = 1 To Val(StateDtlPart(2))
      Debug.Print "  " & StateDtlPart(1) & Right("0" & InxDist, 2)
      NumDist = NumDist + 1
      If NumDist Mod 100 = 0 Then
        Debug.Assert False
        Debug.Print "-----------------"
      End If
    Next
  Next

End Sub

Here I have introduced two arrays. If you do not know what an array is, please look it up.

StateDtl = Array("Alabama|AL|7", "Alaska|AK|1", "Arizona|AZ|9", "Arkansas|AR|4") initialises StateDtl so:

StateDtl(0) = "Alabama|AL|7"
StateDtl(1) = "Alaska|AK|1"
StateDtl(2) = "Arizona|AZ|9"
StateDtl(3) = "Arkansas|AR|4"

If you like this approach, you will need to add strings like this for every State.

Each entry in StateDtl has three parts: the State name, the State code and the number of districts. I do not know if my source is reliable so do not know if the number of districts I show are correct.

StateDtlPart = Split(StateDtl(InxState), "|") stplits the entries in StateDtl into parts:

StateDtlPart(0) = Alabama
StateDtlPart(1) = AL
StateDtlPart(2) = 7

This macro generates a list like:

Alabama
  AL01
  AL02
  AL03
  AL04
  AL05
  AL06
  AL07
Alaska
  AK01
Arizona
  AZ01
  AZ02
  AZ03
  AZ04
  AZ05
  AZ06
  AZ07
  AZ08
  AZ09
Arkansas
  AR01
  AR02
  AR03
  AR04

and so on.

You may prefer Fabricator's approach but if this looks interesting, I will add more detail tomorrow.