Many workbooks consolidation

Question

hello i'm having a problem with the macro bellow on the line 21,

worksheets.add.name = shtname

it seams to me that it's not changing the value of the shtname string once it end the first loop i´d like to know what's happening hre is the code:

Sub lsConsolidarPlanilhas()

Dim lWorkbook           As Workbooks
Dim lWorksheet          As Worksheet
Dim lUltimaLinhaAtiva   As Long
Dim lControle           As Long
Dim lUltimaLinhaAtiva2  As Long
Dim lUltimaLinhaAtiva3  As Long
Dim lUltimaLinhaAtiva4  As Long
Dim shtname             As String

Application.DisplayAlerts = False
Application.ScreenUpdating = False

lUltimaLinhaAtiva = Worksheets("Configuração").Cells(Worksheets("Configuração").Rows.Count, 1).End(xlUp).Row
lControle = 2

While lControle <= lUltimaLinhaAtiva

    If (Workbooks("Macros.xlsm").Worksheets("Configuração").Range("B" & lControle).Value <> "") Then
    shtname = Range("B" & lControle).Text
    Worksheets.Add.name = shtname
    End If

    If (Workbooks("Macros.xlsm").Worksheets("Configuração").Range("B" & lControle).Value <> "") Then

    Workbooks.Open Filename:=Worksheets("Configuração").Range("A" & lControle).Value

        Set lworkbooks = ActiveWorkbook

                If (ActiveWorkbook.Sheets.Count > 1) Then

                Workbooks(lworkbooks.name).Worksheets("<LVC>").Activate
                lUltimaLinhaAtiva2 = Worksheets("<LVC>").Cells(Worksheets("<LVC>").Rows.Count, 1).End(xlUp).Row
                Worksheets("<LVC>").Select
                Worksheets("<LVC>").Range("A1:AI18").Select
                Selection.Copy

                lUltimaLinhaAtiva3 = Workbooks("Macros.xlsm").Worksheets(shtname).Cells(Workbooks("Macros.xlsm").Worksheets(shtname).Rows.Count, 1).End(xlUp).Row + 1
                Workbooks("Macros.xlsm").Worksheets(shtname).Activate
                Workbooks("Macros.xlsm").Worksheets(shtname).Range("A" & lUltimaLinhaAtiva3).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

                For i = 1 To ActiveWorkbook.Sheets.Count

                Workbooks(lworkbooks.name).Worksheets(i).Activate
                lUltimaLinhaAtiva2 = Worksheets(i).Cells(Worksheets(i).Rows.Count, 1).End(xlUp).Row
                Worksheets(i).Select
                Worksheets(i).Range("A19:AI" & lUltimaLinhaAtiva2).Select
                Selection.Copy

                lUltimaLinhaAtiva4 = Workbooks("Macros.xlsm").Worksheets(shtname).Cells(Workbooks("Macros.xlsm").Worksheets(shtname).Rows.Count, 1).End(xlUp).Row + 1
                Workbooks("Macros.xlsm").Worksheets(shtname).Activate
                Workbooks("Macros.xlsm").Worksheets(shtname).Range("A" & lUltimaLinhaAtiva4).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

                Next

                End If

                Workbooks(lworkbooks.name).Worksheets("<LVC>").Activate
                lUltimaLinhaAtiva2 = Worksheets("<LVC>").Cells(Worksheets("<LVC>").Rows.Count, 1).End(xlUp).Row
                Worksheets("<LVC>").Select
                Worksheets("<LVC>").Range("A1:AI" & lUltimaLinhaAtiva2).Select
                Selection.Copy

                lUltimaLinhaAtiva3 = Workbooks("Macros.xlsm").Worksheets(shtname).Cells(Workbooks("Macros.xlsm").Worksheets(shtname).Rows.Count, 1).End(xlUp).Row + 1
                Workbooks("Macros.xlsm").Worksheets(shtname).Activate
                Workbooks("Macros.xlsm").Worksheets(shtname).Range("A" & lUltimaLinhaAtiva3).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

    Else:

    Workbooks.Open Filename:=Worksheets("Configuração").Range("A" & lControle).Value

        Set lworkbooks = ActiveWorkbook

                If (ActiveWorkbook.Sheets.Count > 1) Then

                For i = 1 To ActiveWorkbook.Sheets.Count

                Workbooks(lworkbooks.name).Worksheets(i).Activate
                lUltimaLinhaAtiva2 = Worksheets(i).Cells(Worksheets(i).Rows.Count, 1).End(xlUp).Row
                Worksheets(i).Select
                Worksheets(i).Range("A19:AI" & lUltimaLinhaAtiva2).Select
                Selection.Copy

                lUltimaLinhaAtiva4 = Workbooks("Macros.xlsm").Worksheets(shtname).Cells(Workbooks("Macros.xlsm").Worksheets(shtname).Rows.Count, 1).End(xlUp).Row + 1
                Workbooks("Macros.xlsm").Worksheets(shtname).Activate
                Workbooks("Macros.xlsm").Worksheets(shtname).Range("A" & lUltimaLinhaAtiva4).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

                Next

                End If

                Workbooks(lworkbooks.name).Worksheets("<LVC>").Activate
                lUltimaLinhaAtiva2 = Worksheets("<LVC>").Cells(Worksheets("<LVC>").Rows.Count, 1).End(xlUp).Row
                Worksheets("<LVC>").Select
                Worksheets("<LVC>").Range("A19:AI" & lUltimaLinhaAtiva2).Select
                Selection.Copy

                lUltimaLinhaAtiva3 = Workbooks("Macros.xlsm").Worksheets(shtname).Cells(Workbooks("Macros.xlsm").Worksheets(shtname).Rows.Count, 1).End(xlUp).Row + 1
                Workbooks("Macros.xlsm").Worksheets(shtname).Activate
                Workbooks("Macros.xlsm").Worksheets(shtname).Range("A" & lUltimaLinhaAtiva3).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False

    End If

    Workbooks(lworkbooks.name).Close

    lControle = lControle + 1

Wend

Worksheets("Configuração").Select
Worksheets("Configuração").Range("A1").Select

Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "Planilhas consolidadas!"

End Sub

unqualified Range() function implicitly refers to the active worksheet which is probably not what was intended. Make it fully qualified by adding `Workbooks("Macros.xlsm").Worksheets("Configuração").` in front of it. In general, also, I suggest refactoring the code to eliminate all `.Select` and `.Activate` and and instead assigning to temporary worksheet variables so it is transparent and clear what is being acted on (e.g. `Set sourceWb = ...Set sourceWs = sourceWb.Worksheets(1)...etc` — Cor_Blimey, Sep 16 '14 at 17:44
Or at least refactor the code enough that we have a chance of figuring out what's wrong. See http://stackoverflow.com/help/how-to-ask — Smandoli, Sep 16 '14 at 18:24
Obvious question but is it adding a new sheet each time through the loop? Perhaps it is not, and if that is the case, then your `If` statement is the culprit. Did you put a breakpoint on the `shtname =...` line? Does that line execute more than once? If so, debug the value associated with that assignment. — David Zemens, Sep 16 '14 at 19:32

score 0 · Answer 1 · answered Sep 17 '14 at 16:33

Thank you @Cor_Blimey that really was the problem, once i've especified the workbook for the shtname it worked perfectly

"unqualified Range() function implicitly refers to the active worksheet which is probably not what was intended. Make it fully qualified by adding Workbooks("Macros.xlsm").Worksheets("Configuração"). in front of it. In general, also, I suggest refactoring the code to eliminate all .Select and .Activate and and instead assigning to temporary worksheet variables so it is transparent and clear what is being acted on (e.g. Set sourceWb = ...Set sourceWs = sourceWb.Worksheets(1)...etc – Cor_Blimey 22 hours ago"

Many workbooks consolidation

1 Answers1