Hi, I have the following code which basically mergers data from different Workbooks from a define pivot table range.
I want to add a msgbox to select a specific filter for this pivot data from each workbook. (e.g. FY17 ). So that it will merger only the mentioned data.
Kindly help me edit this VBA code.
Sub XlsMergerNew()
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")
'clear previous data before consolidation
ThisWorkbook.Worksheets(1).Range("A2:K10000").Clear
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("\\ntnet\Budget\2016\Capacity")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
bookList.Worksheets("MergeData").Activate
ActiveWorkbook.RefreshAll
'change "A2" with cell reference of start point for every files here
'If files using more than XI column, change it to the latest column
Range("A2:K" & 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
'Set font & size
Columns("A:N").Select
With Selection.Font
.Name = "Trebuchet MS"
.Size = 9
Columns("K:K").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlCenter
[T1] = Now
End With
End With
End Sub
I want to add a msgbox to select a specific filter for this pivot data from each workbook. (e.g. FY17 ). So that it will merger only the mentioned data.
Kindly help me edit this VBA code.
Sub XlsMergerNew()
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")
'clear previous data before consolidation
ThisWorkbook.Worksheets(1).Range("A2:K10000").Clear
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("\\ntnet\Budget\2016\Capacity")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
bookList.Worksheets("MergeData").Activate
ActiveWorkbook.RefreshAll
'change "A2" with cell reference of start point for every files here
'If files using more than XI column, change it to the latest column
Range("A2:K" & 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
'Set font & size
Columns("A:N").Select
With Selection.Font
.Name = "Trebuchet MS"
.Size = 9
Columns("K:K").Select
Selection.NumberFormat = "0.00"
With Selection
.HorizontalAlignment = xlCenter
[T1] = Now
End With
End With
End Sub
