Current location - Quotes Website - Personality signature - How to combine multiple worksheets in excel
How to combine multiple worksheets in excel

1. Create a new Excel, ALT?+?F11 to open the VBA editor;

2. Menu bar, insert module;

3. Paste the code: Private?Sub?merge_sheet()

Dim?fn$

Dim?dir_path$

Dim?rs?As?Worksheet

Dim?wk?As?Workbook

'Directory to be traversed

dir_path?=?"E:\My Documents\Work\Desktop Directory\Signature Signature Statistics"

'Suppress display warnings

Application.DisplayAlerts?=?False

'Control the file format to be traversed

fn?=?Dir( dir_path?&?"\*.csv")

If?fn?=?""?Then?MsgBox?"There is no file to open in this directory"

Do< /p>

Set?wk?=?Workbooks.Open(dir_path?&?"\"?&?fn)

For?Each?rs?In?wk.Worksheets

'Determine whether the current Sheet is empty, skip if it is empty

If?Application.CountA(Sh.UsedRange.Cells)?=?0?Then

rs.Copy ?ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

End?If

Next

wk.Close?True

fn? =?Dir

Loop?While?fn?<>?""

'Turn on warnings

Application.DisplayAlerts?=?True

< p>End?Sub

4. Modify the directory address and file suffix in the code, and run the code;

5. Save the generated Excel;