Need some help geting VBA code to work back to back.

I have two sets of code. The first sets of code takes seperate excel spreadsheets and consolidates it into one excel spreadsheet with seperate tabs. The second code takes the tabs C1's thru C10's B32:B3032 Coloumns and sets them as seperate coloumns in a tab that i have to create called C. I would like to make it so I don't have to use two sets of code but don't know how. If anyone has the solution, I would greatly appreciate it.

Sub Macro1()
   Dim sourceWb As Workbook, newWb As Workbook
   Dim myPath As String, elem As Variant
   Dim ctr As Integer, myArray As String
   
   myPath = "C:\Users\bcourtney\Desktop\Data Consolidator\"
   myArray = "C1,C2,C3,C4,C5,C6,C7,C8,C9,C10"
   
   Application.ScreenUpdating = False
   Application.DisplayAlerts = False
   For Each elem In Split(myArray, ",")
      ctr = ctr + 1
      Set sourceWb = Workbooks.Open(myPath & elem & ".csv")
      If ctr = 1 Then
         sourceWb.ActiveSheet.Copy
         Set newWb = ActiveWorkbook
      Else
         sourceWb.ActiveSheet.Copy After:=newWb.Sheets(newWb.Sheets.Count)
      End If
      sourceWb.Close False
      newWb.Sheets(ctr).Name = CStr(elem)
   Next
   newWb.SaveAs Filename:=myPath & "Consolidated.xlsx"
   
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
End Sub



Sub SummurizeSheets()
  Dim ws As Worksheet

  Application.ScreenUpdating = False


For Each ws In Worksheets
If ws.Name <> "Consolidated" Then
ws.Range("B32:B3032").Copy
Sheets("C").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlValues

End If
Next ws
End Sub

Which workbook do you want to run SummurizeSheets on?

I do not see your problem.

All you do is paste the code within the second macro into the bottom of the first.

Am I missing something?

Sub SummurizeSheets()
Dim ws As Worksheet
Dim sourceWb As Workbook, newWb As Workbook
Dim myPath As String, elem As Variant
Dim ctr As Integer, myArray As String

Application.ScreenUpdating = False
Application.DisplayAlerts = False


'First Macro
For Each ws In Worksheets
If ws.Name <> "Consolidated" Then
ws.Range("B32:B3032").Copy
Sheets("C").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlValues

End If
Next ws


' Second Macro

myPath = "C:\Users\bcourtney\Desktop\Data Consolidator\"
myArray = "C1,C2,C3,C4,C5,C6,C7,C8,C9,C10"


For Each elem In Split(myArray, ",")
ctr = ctr + 1
Set sourceWb = Workbooks.Open(myPath & elem & ".csv")
If ctr = 1 Then
sourceWb.ActiveSheet.Copy
Set newWb = ActiveWorkbook
Else
sourceWb.ActiveSheet.Copy After:=newWb.Sheets(newWb.Sheets.Count)
End If
sourceWb.Close False
newWb.Sheets(ctr).Name = CStr(elem)
Next
newWb.SaveAs Filename:=myPath & "Consolidated.xlsx"

Application.DisplayAlerts = True
Application.ScreenUpdating = True

end sub()

You should just be able to add this at the end of Macro1.

For Each ws In newWB.Worksheets
    If ws.Name <> "Consolidated" Then
         ws.Range("B32:B3032").Copy
         newWB.Sheets("C").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlValue
     End If
 Next ws

Mind you, instead of copying the worksheet from the workbooks you are opening you could just copy the range.

Option Explicit

Sub Macro1()
Dim sourceWb As Workbook, newWb As Workbook
Dim wsDst As Worksheet
Dim myPath As String, elem As Variant
Dim ctr As Integer, myArray As String

    myPath = "C:\Users\bcourtney\Desktop\Data Consolidator\"
    myArray = "C1,C2,C3,C4,C5,C6,C7,C8,C9,C10"

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each elem In Split(myArray, ",")
        ctr = ctr + 1
        Set sourceWb = Workbooks.Open(myPath & elem & ".csv")
        If ctr = 1 Then
            sourceWb.ActiveSheet.Copy
            Set newWb = ActiveWorkbook
            Set wsDst = newWb.Worksheets(1)
        Else
            sourceWb.ActiveSheet.Range("B32:B3032").Copy
            wsDst.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlValue
        End If
        sourceWb.Close False
        newWb.Sheets(ctr).Name = CStr(elem)
    Next
    newWb.SaveAs Filename:=myPath & "Consolidated.xlsx"

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

PS Could you please add code tag? It was hard to see where one sub ended and the other started, tags would make that easier.

Oh, and I think adding code tags is kind of a rule too.

Thanks for the response and I'll remember to use code tags for future postings. I'm not an experienced code writer and when I tried using your suggested code below I got the following error. Run-time error '9': Subscript out of range. It didn't like newWb.Sheets(ctr).Name = CStr(elem)

Option Explicit

Sub Macro1()
Dim sourceWb As Workbook, newWb As Workbook
Dim wsDst As Worksheet
Dim myPath As String, elem As Variant
Dim ctr As Integer, myArray As String

    myPath = "C:\Users\bcourtney\Desktop\Data Consolidator\"
    myArray = "C1,C2,C3,C4,C5,C6,C7,C8,C9,C10"

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    For Each elem In Split(myArray, ",")
        ctr = ctr + 1
        Set sourceWb = Workbooks.Open(myPath & elem & ".csv")
        If ctr = 1 Then
            sourceWb.ActiveSheet.Copy
            Set newWb = ActiveWorkbook
            Set wsDst = newWb.Worksheets(1)
        Else
            sourceWb.ActiveSheet.Range("B32:B3032").Copy
            wsDst.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlValue
        End If
        sourceWb.Close False
        newWb.Sheets(ctr).Name = CStr(elem)
    Next
    newWb.SaveAs Filename:=myPath & "Consolidated.xlsx"

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub

That line isn't needed any more as you aren't copying sheets from the workbooks.

Brett

A new workbook is created by copying the first sheet of the first workbook.

Then instead of copying worksheets from the rest of the workbooks just the range is copied from them to then new workbook.

That might need a bit of a tweak to work properly, I can't tell without knowing more about the layout of the worksheets you are copying from.

Did you try my initial suggestion?