Hi, I totally new at VBA Macro and I just got the task from my work to do some.
There are 2 commands that I need to solve:
1. I need to merge / copy as value only the first sheet of every workbook
2. The next thing is that I need to sum for specific cells of the worksheet, whenever I need to add some rows, it automatically sum the new row. I can use the sum across the sheets but it would take too much time to do so because there are approx. 500 rows.
Every worksheet, they have the same format and place.
I try to look upon the internet and found a code to merge the files but it merge all the sheets (I only need the first sheet only)
For the sum, I record the macro manually and it really takes too many times and when I try to insert a new row, I need to recode the macro
Here is the code that I found for merging the files into 1 workbook:
Sub Merge_Files()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet
Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)
tempFileDialog.AllowMultiSelect = True
numberOfFilesChosen = tempFileDialog.Show
For i = 1 To tempFileDialog.SelectedItems.Count
Workbooks.Open tempFileDialog.SelectedItems(i)
Set sourceWorkbook = ActiveWorkbook
For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Next tempWorkSheet
sourceWorkbook.Close
Next i
For i = 1 To mainWorkbook.Worksheets.Count
mainWorkbook.Worksheets(i).Cells.Copy
mainWorkbook.Worksheets(i).Cells.PasteSpecial xlPasteValues
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
I tweak it on the paste special values only because I only need the values only on the first sheet but it still takes all of the sheet inside the workbook
Any help would be appreciated. Thank you very much.
There are 2 commands that I need to solve:
1. I need to merge / copy as value only the first sheet of every workbook
2. The next thing is that I need to sum for specific cells of the worksheet, whenever I need to add some rows, it automatically sum the new row. I can use the sum across the sheets but it would take too much time to do so because there are approx. 500 rows.
Every worksheet, they have the same format and place.
I try to look upon the internet and found a code to merge the files but it merge all the sheets (I only need the first sheet only)
For the sum, I record the macro manually and it really takes too many times and when I try to insert a new row, I need to recode the macro
Here is the code that I found for merging the files into 1 workbook:
Sub Merge_Files()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim numberOfFilesChosen, i As Integer
Dim tempFileDialog As FileDialog
Dim mainWorkbook, sourceWorkbook As Workbook
Dim tempWorkSheet As Worksheet
Set mainWorkbook = Application.ActiveWorkbook
Set tempFileDialog = Application.FileDialog(msoFileDialogFilePicker)
tempFileDialog.AllowMultiSelect = True
numberOfFilesChosen = tempFileDialog.Show
For i = 1 To tempFileDialog.SelectedItems.Count
Workbooks.Open tempFileDialog.SelectedItems(i)
Set sourceWorkbook = ActiveWorkbook
For Each tempWorkSheet In sourceWorkbook.Worksheets
tempWorkSheet.Copy After:=mainWorkbook.Sheets(mainWorkbook.Worksheets.Count)
Next tempWorkSheet
sourceWorkbook.Close
Next i
For i = 1 To mainWorkbook.Worksheets.Count
mainWorkbook.Worksheets(i).Cells.Copy
mainWorkbook.Worksheets(i).Cells.PasteSpecial xlPasteValues
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
I tweak it on the paste special values only because I only need the values only on the first sheet but it still takes all of the sheet inside the workbook
Any help would be appreciated. Thank you very much.
