L
Len
Hi,
After several attempts to work around but fails to run the complete
codes
Thus, I need help to join the 2nd part of vba codes below so that I
can make the changes in each sheet("P+L") of every workbook in J
folder, thereafter make the defined data range in each sheet of every
workbook in that J folder for data consolidation purpose : -
Sub Totals()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Const MAXBOOK As Long = 4
Dim i%, SheetArg$()
Dim sPath1 As String
ReDim SheetArg(1 To MAXBOOK)
Dim x As String
Dim Namerng As Variant, NameList As Variant
Dim sPath As String, sFile As String
Windows("Budget Consol.xls").Activate
sPath = "J:\BBT\LO\Budget\Budget Actual\Acad2\"
i = 0
sPath1 = "J:\BBT\LO\Budget\Budget Actual\Acad2\*.xls"
sFile = Dir(sPath1)
---------2nd part of join codes ---------------
Dim i As Long
Dim Lstrow As Long
Lstrow = Cells(Rows.Count, "A").End(xlUp).Row
If Lstrow > 0 Then
For i = 5 To Lstrow
If Cells(i, 1).Value <> "" Then
Cells(i, 1).Copy
Cells(i, 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Cells(i, 1).ClearContents
End If
Next
Else
MsgBox "It appears that the file is empty, check the file again"
Exit Sub
End If
----------- End 2nd part -----------------------
Do While sFile <> ""
i = i + 1
SheetArg(i) = "'" & sPath & _
[ & sFile & "]P+L'!R6C2:R47C15 "
sFile = Dir()
Loop
ThisWorkbook.Sheets("Sheet2").Range("A1").Consolidate _
Sources:=Array(SheetArg), Function:=xlSum, TopRow:=True, _
LeftColumn:=True, CreateLinks:=True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Your help will be much appreciated as I'm vba beginner and thanks in
advance
Regards
Len
After several attempts to work around but fails to run the complete
codes
Thus, I need help to join the 2nd part of vba codes below so that I
can make the changes in each sheet("P+L") of every workbook in J
folder, thereafter make the defined data range in each sheet of every
workbook in that J folder for data consolidation purpose : -
Sub Totals()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Const MAXBOOK As Long = 4
Dim i%, SheetArg$()
Dim sPath1 As String
ReDim SheetArg(1 To MAXBOOK)
Dim x As String
Dim Namerng As Variant, NameList As Variant
Dim sPath As String, sFile As String
Windows("Budget Consol.xls").Activate
sPath = "J:\BBT\LO\Budget\Budget Actual\Acad2\"
i = 0
sPath1 = "J:\BBT\LO\Budget\Budget Actual\Acad2\*.xls"
sFile = Dir(sPath1)
---------2nd part of join codes ---------------
Dim i As Long
Dim Lstrow As Long
Lstrow = Cells(Rows.Count, "A").End(xlUp).Row
If Lstrow > 0 Then
For i = 5 To Lstrow
If Cells(i, 1).Value <> "" Then
Cells(i, 1).Copy
Cells(i, 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Cells(i, 1).ClearContents
End If
Next
Else
MsgBox "It appears that the file is empty, check the file again"
Exit Sub
End If
----------- End 2nd part -----------------------
Do While sFile <> ""
i = i + 1
SheetArg(i) = "'" & sPath & _
[ & sFile & "]P+L'!R6C2:R47C15 "
sFile = Dir()
Loop
ThisWorkbook.Sheets("Sheet2").Range("A1").Consolidate _
Sources:=Array(SheetArg), Function:=xlSum, TopRow:=True, _
LeftColumn:=True, CreateLinks:=True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
Your help will be much appreciated as I'm vba beginner and thanks in
advance
Regards
Len