macro, join sheets of different books of excel

Question

macro, join sheets of different books of excel

Navigation

#1 by (1 votes)

0

I am a macro researcher in excel, so far I consider myself a beginner in this work, I need your help to solve a problem.

I need to merge several excel files into one, this I have been able to achieve with a macro like:

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'NOTA: CAMBIA C:\Users\f13619\Desktop\BBBBBB POR LA RUTA QUE TENGAS TUS ARCHIVOS

Set dirObj = mergeObj.Getfolder("C:\Users\f13619\Desktop\BBBBBB")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
'change "A2" with cell reference of start point for every files here
'for example "B3:IV" to merge all files start from columns B and rows 3
'If you're files using more than IV column, change it to the latest column
'Also change "A" column on "A65536" to the same column as start point
Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
'Do not change the following column. It's not the same column as above
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

but I would like to know what I should change in this code because it only joins the first sheets and what I need is to unite all sheets 1 on a single sheet 1, all sheets 2 on a single sheet 2 ... and so on , I must mention that all the sheets have the same structure, in advance, thank you for your support I hope I have expressed myself well.

Thanks

vba macros excel

asked by INVEST_EXCEL 04.06.2018 в 19:53

source

1 answer

Problem of accents in the name when saving a file in php print with php [closed]

score 1 · Accepted Answer

I would advise you to join sheets in excel using Power Query instead of VBA with a few clicks has the expected results and is more stable and faster than a macro.

For the macro it would be:

Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Dim i, WS_Count, WS_Count2 As Integer
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")

'NOTA: CAMBIA C:\Users\f13619\Desktop\BBBBBB POR LA RUTA QUE TENGAS TUS ARCHIVOS

Set dirObj = mergeObj.Getfolder("C:\Users\f13619\Desktop\BBBBBB")
Set filesObj = dirObj.Files

Workbooks.Add 'crea archivo nuevo para almacenar hojas unidas
ActiveWorkbook.SaveAs Filename:="master.xlsx" 'guarda archivo en documentos

For Each everyObj In filesObj
    Set bookList = Workbooks.Open(everyObj)
    WS_Count = bookList.Worksheets.Count 'cuenta cuantas hojas hay en el libro
    For i = 1 To WS_Count 'En esta linea itera por cada hoja del libro
        bookList.Worksheets(i).Activate 'hoja a unir
        Range("A2:IV" & Range("A65536").End(xlUp).Row).Copy
        Workbooks("master.xlsx").Worksheets(i).Activate
        Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
        Application.CutCopyMode = False
        WS_Count2 = Workbooks("master.xlsx").Worksheets.Count 'cuenta cuantas hojas hay en master
        If WS_Count2 < WS_Count Then
            Sheets.Add After:=ActiveSheet 'adiciona nueva hoja
        End If
    Next i
    bookList.Close
Next
End Sub