I need to set up information in a single worksheet that has been extracted from the system into several worksheets (plan1, plan2, plan3 ...). I need the information, when updating, to stay one below the other. The problem that per month can vary this amount of spreadsheets, from plan20 to plan45, and in the spreadsheets there are empty intervals (type excel exports from the system to empty plan3, already plan4 with information). This information will be the basis for a PivotTable.
Also in some spreadsheets containing information ex: Plan2 all the columns with filled sequence (column A, B, C ...), already in plan4 there are empty intervals (column C empty, columnD with data ...) I need to configure considering in some worksheets to jump the empty column so that the information sequences are pasted correctly in all the fields.I started doing this here, but I can not seem to consider jumping the empty column to fill in the correct sequence
Sub Consolidar()
Sheets(1).Range("A2:T100").Clear
plans = Sheets.Count
linha = 2
For n = 2 To plans
lin = 2
Do Until Sheets(n).Cells(lin, 1) = ""
Sheets(1).Cells(linha, 1) = Sheets(n).Cells(lin, 1)
Sheets(1).Cells(linha, 2) = Sheets(n).Cells(lin, 2)
Sheets(1).Cells(linha, 3) = Sheets(n).Cells(lin, 3)
Sheets(1).Cells(linha, 4) = Sheets(n).Cells(lin, 4)
Sheets(1).Cells(linha, 5) = Sheets(n).Cells(lin, 5)
Sheets(1).Cells(linha, 6) = Sheets(n).Cells(lin, 6)
Sheets(1).Cells(linha, 7) = Sheets(n).Cells(lin, 7)
Sheets(1).Cells(linha, 8) = Sheets(n).Cells(lin, 8)
Sheets(1).Cells(linha, 9) = Sheets(n).Cells(lin, 9)
Sheets(1).Cells(linha, 10) = Sheets(n).Cells(lin, 10)
Sheets(1).Cells(linha, 11) = Sheets(n).Cells(lin, 11)
Sheets(1).Cells(linha, 12) = Sheets(n).Cells(lin, 12)
Sheets(1).Cells(linha, 13) = Sheets(n).Cells(lin, 13)
Sheets(1).Cells(linha, 14) = Sheets(n).Cells(lin, 14)
Sheets(1).Cells(linha, 15) = Sheets(n).Cells(lin, 15)
Sheets(1).Cells(linha, 16) = Sheets(n).Cells(lin, 16)
Sheets(1).Cells(linha, 17) = Sheets(n).Cells(lin, 17)
Sheets(1).Cells(linha, 18) = Sheets(n).Cells(lin, 18)
Sheets(1).Cells(linha, 19) = Sheets(n).Cells(lin, 19)
Sheets(1).Cells(linha, 20) = Sheets(n).Cells(lin, 20)
Sheets(1).Cells(linha, 21) = Sheets(n).Cells(lin, 21)
Sheets(1).Cells(linha, 22).Font.ColorIndex = n + 1
Sheets(1).Cells(linha, 22) = Sheets(n).Name
lin = lin + 1
linha = linha + 1
Loop
Next
End Sub