VBA - Help me please

[minger15]

OK, I have multiple workbooks with sheets in them (all named something different) but have the same structure. How do I create a macro that will copy each of those sheets into one file . To add to the complication each workbook as a summary sheet that I don't want to copy and every workbook as a different number of sheets. Can someone help me?

 

[JoeMo]

OK, I have multiple workbooks with sheets in them (all named something different) but have the same structure. How do I create a macro that will copy each of those sheets into one file . To add to the complication each workbook as a summary sheet that I don't want to copy and every workbook as a different number of sheets. Can someone help me?

Welcome to the board!

You could enhance your chance of getting some help by providing the details needed to address your problem. You know what you want, but the rest of us don't have the detailed knowledge to help you get there. What are the names of the workbooks, will they all be open when the copying takes place, what's the name or names of the sheets you don't want to copy ..... ?????

 

[minger15]

Thank you Joe. I didn't know how much detail I needed to provide. New to all this. OK, here goes,
I have three workbooks named Core, Trading, Base. All of these workbooks have a sheet dedicated to a various sector of each of those business. Each sheet is named after a business segment and is the same format. IE the CORE workbook would has sheets named (HKIP, HKAR, HKVL, HKGC, HKBF and HKOS) while the Trade workbook has sheets named (NAVL, NVIP, NQGI, MPTR, PGPR) and Base has sheets named (PIMO, PPOW, LFID, RBPI, RYIR, VGFA, JLTR, JMTA, JMVX)
There are a couple of things I want to do here. 1. I would like to create a sheet that would pull the Sales line (Range B20:M20) on each of the segment sheets into one new book 2. I would like to create a workbook that creates a sheet for each segment (Sheet name would = the Segment Name) from each of those individual workbooks. I hope that makes sense.

 

[JoeMo]

What's the file extension on each of the three workbooks? You've listed the sheet names you want to process, but you alluded to a sheet in each workbook you don't want to process- what are the names of the sheets you don't want to process?

For requirement 1:
You want to "pull" data from each sheet to be processed in each of three workbooks (Range B20:M20) into a new workbook - where exactly do you want to put that data in the new workbook?
For requirement 2:
What is a "segment" - is that the sheet names you want to process from the 3 workbooks or ....?

 

[minger15]

Hi Joe:

Thanks for the response. Each of the workbooks has a .xlsx. The sheet in each book that I do not want to process is named "Summary"

Requirement 1: I would ideally like a sheet that listed the names of each sheet in column A and then the corresponding sales on each row
Requirement 2: Yes, those are all the sheets, with the exception of the "Summary" sheet.

Thank you again

 

[JoeMo]

Hi Joe:

Thanks for the response. Each of the workbooks has a .xlsx. The sheet in each book that I do not want to process is named "Summary"

Requirement 1: I would ideally like a sheet that listed the names of each sheet in column A and then the corresponding sales on each row
Requirement 2: Yes, those are all the sheets, with the exception of the "Summary" sheet.

Thank you again

Here's an untested macro that is intended to do what you want, but will likely need some tweaking. You must install this as a standard module in either a macro-enable workbook or in a PERSONAL.XLSB workbook. The 3 source workbooks must be open before you run this macro.

To install the code as a standard module:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the code from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Press Alt+F8 keys to run the code
7. Make sure you have enabled macros whenever you open the file or the code will not run.

Sub minger15()
'Assumes the 3 source workbooks are all open before this macro is run
Dim Bks As Variant, newWB As Workbook, sht As Worksheet, i As Long, ct As Long
Const S1 As String = "SalesLineSummary"  'change name to suit
Const S2 As String = "SheetsConsolidated"  'change name to suit
Bks = Array("Core.xlsx", "Trading.xlsx", "Base.xlsx")
'1.create a sheet that pulls the Sales line (Range B20:M20) on each of the segment sheets into one new book
Set newWB = Workbooks.Add(xlWBATWorksheet)
With newWB
    .Sheets("Sheet1").Range ("A1:B1"), Value = Array("Segment", "Sales")
    For i = LBound(Bks) To UBound(Bks)
        For Each sht In Workbooks(Bks(i)).Worksheets
            If sht.Name <> "Summary" Then
                ct = ct + 1
                .Sheets("Sheet1").Range("A1").Offset(ct, 0).Value = sht.Name
                .Sheets("Sheet1").Range("B1:M1").Offset(ct, 0).Value = sht.Range("B20:M20").Value
            End If
        Next sht
    Next i
    .SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & _
        S1 & ".xlsx", FileFormat:=51 'change file extension & format to suit
End With
                
'2. I would like to create a workbook that creates a sheet for each segment
'  (Sheet name would = the Segment Name) from each of those individual workbooks
Set newWB = Workbooks.Add(xlWBATWorksheet)
With newWB
    For i = LBound(Bks) To UBound(Bks)
        For Each sht In Workbooks(Bks(i)).Worksheets
            If sht.Name <> "Summary" Then
                sht.Copy after:=.Sheets(.Sheets.Count)
            End If
        Next sht
    Next i
    Application.DisplayAlerts = False
    .Sheets(1).Delete
    Application.DisplayAlerts = True
    .SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & _
    S2 & ".xlsx", FileFormat:=51  'change file extension & format to suit
End With
End Sub

Give it a shot and post back. If you encounter an error, note the error message and the specific line that's highlighted when you select Debug on the error dialog box.

Note: the intention is to create 2 new workbooks that will be saved under the names assigned to the constants S1 & S2 at the start of the code and saved to the same folder that the macro-enabled workbook that contains the code is in.