Hi Professional, I would like to ask the help for the VBA code speed up & improvement.
I have to consolidate the sub-workbooks to my master workbook. Each workbooks have the same tab name to contain the data.
Eg: I have to copy the data in column F from "Monday" Tab in sub workbooks to my master workbook in the column B of "Monday" Tab and subtract the number.
I have to copy the data in column C from "Monday" Tab in sub workbooks to my master workbook in the column F of "Monday" Tab.
I found a VBA code to consolidate the data, but the speed seems too slow and the code is not perfect.
For the convenient, I skip to paste the similar code of "Tuesday","Wednesday", etc tab.
Thank you very much.
I have to consolidate the sub-workbooks to my master workbook. Each workbooks have the same tab name to contain the data.
Eg: I have to copy the data in column F from "Monday" Tab in sub workbooks to my master workbook in the column B of "Monday" Tab and subtract the number.
I have to copy the data in column C from "Monday" Tab in sub workbooks to my master workbook in the column F of "Monday" Tab.
I found a VBA code to consolidate the data, but the speed seems too slow and the code is not perfect.
For the convenient, I skip to paste the similar code of "Tuesday","Wednesday", etc tab.
VBA Code:
Sub Consolidatfiles()
On Error Resume Next
Application.ScreenUpdating = False
Dim FileName As Variant, wkbSource As Workbook, wkbDest As Workbook
Set wkbDest = ThisWorkbook
FileName = Application.GetOpenFilename("Excel Files (*.xlsx*),*.xlsx*", , "Select Excel Files", , True)
If Not IsArray(FileName) Then
MsgBox "No File Selected"
Exit Sub
End If
For i = LBound(FileName) To UBound(FileName)
Set wkbSource = Workbooks.Open(FileName(i))
With wkbSource
.Worksheets("Monday").Range("C5:C" & Range("C" & Rows.Count).End(xlUp).Row).Copy _
Destination:=wkbDest.Worksheets("Monday").range("F7").End(xlUp)
.Worksheets("Monday").Range("F5:F" & Range("F" & Rows.Count).End(xlUp).Row-1).Copy _
Destination:=wkbDest.Worksheets("Monday").range("F7").End(xlUp).PasteSpecial Paste:=xlPasteAll, Operation:=xlSubtract
wkbSource.Close savechanges = False
End With
Next i
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Thank you very much.