I have 2 workbook, need to copy data from all sheets expect 1 sheet "Expenses - Over All" and paste rest of the sheets from workbook2 to workbook 1.
In workbook 1 it should compile the data.
I did tried very possible way but clearly no result., just need to loop the sheets and copy paste it in one file
this code will work for 1 sheet.
In workbook 1 it should compile the data.
I did tried very possible way but clearly no result., just need to loop the sheets and copy paste it in one file
this code will work for 1 sheet.
VBA Code:
Sub ExpenseDataCopy()
Dim fPath As String
Dim fName As String
Dim fName1 As String
Dim WB As Workbook
Dim WB1 As Workbook
Dim ctr As String
Set WB1 = ActiveWorkbook
Sheets("DownloadFiles1").Select
Dim lastRow As Long
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Dim I As Long
For I = 2 To lastRow
fName1 = Cells(I, "J").Value
Sheets("DownloadFiles").Unprotect "Qtech456$!"
Sheets("Client Wise Revenue Tracking").Unprotect "Qtech456$!"
Sheets("Manpower Costing").Unprotect "Qtech456$!"
On Error Resume Next
fPath = Application.ActiveWorkbook.Path & "\" & "Income & Expenditure Downloaded Files"
fName = fPath & "\" & fName1 & ".xlsx"
Set WB = Workbooks.Open(fName) 'It has multipal sheets
With WB
.Sheets("Expenses - Bizinso").Select
Dim lastRow1 As Long
lastRow1 = Cells(Rows.Count, "C").End(xlUp).Row
Range("C3:AA" & lastRow1).SpecialCells(xlCellTypeVisible).Copy
WB1.Activate
Sheets("Expenses").Select
Range("C5").Select
lMaxRows = Cells(Rows.Count, "C").End(xlUp).Row
Range("C" & lMaxRows + 1).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'xlPasteFormats
Application.CutCopyMode = False
Range("A4").Select
End With
Next I
End Sub