Excel combining different workbooks into one workbook

As I have to do a lot of reports in my office using Excel, I have manage to learn some basic VBA programming to help autorun the reports.

 

For those of you who are wondering if there are any ways to combine all the different workbooks into one, here is a simple VBA programming that you can use on your Excel.

For this case, the combine work files into the workbook will be named as SES4. You may opt to choose a different name for the work file. Before you run the macro, you will need to close other Excel work files and open the ones that you want to combine into one work file.

Sub ConcatenateAll()

Workbooks.Add
ChDir "C:\"
ActiveWorkbook.SaveAs Filename:="C:\SES4.xls"

CopyTargetBookmark = 1

For Each Workbook In Application.Workbooks
If Workbook.Name <> "SES4.xls" And Workbook.Name <> "PERSONAL.XLS" Then
Workbook.Activate
Workbook.Worksheets(1).UsedRange.Copy
Workbooks("SES4.xls").Activate
Range("A" & CopyTargetBookmark).Select
ActiveSheet.Paste
CopyTargetBookmark = CopyTargetBookmark + Workbook.Worksheets(1).UsedRange.Rows.Count
End If
Next Workbook



End Sub

No comments:

Follow by Email

Theme images by i-bob. Powered by Blogger.