Collate all spreadsheets .xls* in the current folder depending on a specific cell value.

[excelente]

Hi guys! I have a challenge for you. I am having a problem with multiple reports no uniformity of columns so I thought of these but I don't know how to do it. This is the scenario.

1. There are x numbers of spreadsheets and worksheets within with different column names in it.
2. I thought of putting a specific value in a hidden cell to let the collator know what to do and what range would it copy.
3. All spreadsheets that I want to collate have 4 column names that I want to copy. eg. ID, Name, Number, Status but columns arrangement are different from each other.
4. The collator then will check all the spreadsheets and the worksheets within them if cell AA has a value and copy columns depending on the value. For example: If the value of cell AA in the source spreadsheet is REPORT1 then it will copy columns B, C, D, F and paste it on the collators sheet columns A, B, C, D and if the value is REPORT2 the columns in which it will copy is A, D, G, H and copy it again in columns A, B, C, D of the collator sheet.
5. If possible, this will happen while the source spreadsheets in current folder is closed.
6. By the way, the source spreadsheets have different starting row.

I appreciate all the help big or small. Thank you.

 

[JLGWhiz]

Hi guys! I have a challenge for you. I am having a problem with multiple reports no uniformity of columns so I thought of these but I don't know how to do it. This is the scenario.

1. There are x numbers of spreadsheets and worksheets within with different column names in it.
2. I thought of putting a specific value in a hidden cell to let the collator know what to do and what range would it copy.
3. All spreadsheets that I want to collate have 4 column names that I want to copy. eg. ID, Name, Number, Status but columns arrangement are different from each other.
4. The collator then will check all the spreadsheets and the worksheets within them if cell AA has a value and copy columns depending on the value. For example: If the value of cell AA in the source spreadsheet is REPORT1 then it will copy columns B, C, D, F and paste it on the collators sheet columns A, B, C, D and if the value is REPORT2 the columns in which it will copy is A, D, G, H and copy it again in columns A, B, C, D of the collator sheet.
5. If possible, this will happen while the source spreadsheets in current folder is closed.
6. By the way, the source spreadsheets have different starting row.

I appreciate all the help big or small. Thank you.

Hi excelente
If the workbooks are isolated to a single directory so that they all have the same path, and they are the only workbooks in that directory, then they can be called without having to know their individual names. But the sheets are not that simple. If only the first sheet of each workbook is to be used, or if each sheet of each workbook is to be used, then those are easy enough to handle. But if the workbooks contain multiple sheets and only one or two randomly apply to the project, then that can get messy. All that is is needed to locate the correct columns is the column header string. Then as long as each worksheet has those column headers, spelled the same on each, then they can be found and applied as needed. So if you are looking for VBA code to help you, and can clear up how the workbooks and worksheets are organized, maybe someone will provide a method to do what you want.

 

[excelente]

Hi JLGWhiz,

Thank you for the response, it seems like your the only person interested with my problem. I'm still trying to figure out how make things work. for now this is what I came up with. I hope all things will turn out well.

 Sub FireTheLazer()

    Dim fPath As String
    Dim sh As Worksheet
    Dim sName As String

    fPath = ThisWorkbook.Path + "\"

    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With

    sName = Dir(fPath & "*.xls*")


    Dim activeLastRow As String

    Do Until sName = ""

        If sName <> ThisWorkbook.Name Then
            With GetObject(fPath & sName)
                For Each sh In .Worksheets
                    With sh

                        activeLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row + 1

                        If (.Range("BB1").Value = "REPORT1") Then
                            .Range("A2:A9").Copy
                            ThisWorkbook.Worksheets(1).Range("A" + activeLastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                            .Range("B2:B9").Copy
                            ThisWorkbook.Worksheets(1).Range("B" + activeLastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                            .Range("C2:C9").Copy
                            ThisWorkbook.Worksheets(1).Range("C" + activeLastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                            .Range("D2:D9").Copy
                            ThisWorkbook.Worksheets(1).Range("D" + activeLastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                            .Range("E2:E9").Copy
                            ThisWorkbook.Worksheets(1).Range("E" + activeLastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                        ElseIf (.Range("BB1").Value = "REPORT2") Then
                            .Range("A2:A9").Copy
                            ThisWorkbook.Worksheets(1).Range("A" + activeLastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                        ElseIf (.Range("BB1").Value = "REPORT3") Then
                            .Range("A2:A9").Copy
                            ThisWorkbook.Worksheets(1).Range("A" + activeLastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                        End If

                    End With
                Next sh
                .Close True
            End With
        End If
        sName = Dir
        
    Loop
    
    

    With Application
        .Calculation = xlAutomatic
        .EnableEvents = True
        .ScreenUpdating = True
    End With
End Sub

 

[JLGWhiz]

You're probably getting a type mismatch because of this line

Dim activeLastRow As String

Change to

Dim activeLastRow As Long

When you initialize that variable, it is an integer value, so you need the Long integer data type.

You also might want to use

Dim wh As Workbook
Set wb = Workbooks.Open(fPath & sName)

Instead of

With GetObject(fPath & sName)

Then of course you would do away with the End If for that with statement, and use the wb variable to set up your sheet variables.

 

[excelente]

Thank you the suggestions JLGWhiz, I completed the program!

 

[JLGWhiz]

You're welcome,
Regards, JLG