malleshg24
10-27-2017, 09:55 PM
Hi Team,
I want to consolidate all workbooks data into masterworkbook. I receive daily report which contain 8 sheets
From this report I copy ( sheets1, sheet2, sheet4 and sheet5 data ) and paste into master sheets under (sheets1, sheet2, sheet4 and sheet5 ) respectively.
Below I have one code which consolidate all sheets data into master sheets. But this time my task is different copying specific sheets data and pasting masterworkbooks specific sheets.
Sub CosolodiateFromDifferentworkbook()
Dim wbk As Workbook
Dim sht As Worksheet, Nsht As Worksheet
Application.ScreenUpdating = False
FP = "C:\Users\AFCKS TECHNOLOGIES\Desktop\STVB004\Todays Report\"
FN = Dir(FP)
Set sht = Sheets.Add(, Sheets("Task"))
sht.Name = "Master"
Do Until FN = ""
lr = sht.Cells(Rows.Count, 1).End(xlUp).Row + 1
Set wbk = Workbooks.Open(FP & FN)
' To open workbook , need to mention File path & File name
Set Nsht = wbk.Sheets(1)
Nsht.Range("A1").CurrentRegion.Offset(1).Copy sht.Range("A" & lr)
wbk.Close False
FN = Dir
Loop
Set wbk = Nothing
Application.ScreenUpdating = True
MsgBox " Data consolodiate successfully !", vbInformation, "Data Import"
End Sub
Please suggest what changes to make
Thanks for your precious time
Regards,
Mallesh :think::help
I want to consolidate all workbooks data into masterworkbook. I receive daily report which contain 8 sheets
From this report I copy ( sheets1, sheet2, sheet4 and sheet5 data ) and paste into master sheets under (sheets1, sheet2, sheet4 and sheet5 ) respectively.
Below I have one code which consolidate all sheets data into master sheets. But this time my task is different copying specific sheets data and pasting masterworkbooks specific sheets.
Sub CosolodiateFromDifferentworkbook()
Dim wbk As Workbook
Dim sht As Worksheet, Nsht As Worksheet
Application.ScreenUpdating = False
FP = "C:\Users\AFCKS TECHNOLOGIES\Desktop\STVB004\Todays Report\"
FN = Dir(FP)
Set sht = Sheets.Add(, Sheets("Task"))
sht.Name = "Master"
Do Until FN = ""
lr = sht.Cells(Rows.Count, 1).End(xlUp).Row + 1
Set wbk = Workbooks.Open(FP & FN)
' To open workbook , need to mention File path & File name
Set Nsht = wbk.Sheets(1)
Nsht.Range("A1").CurrentRegion.Offset(1).Copy sht.Range("A" & lr)
wbk.Close False
FN = Dir
Loop
Set wbk = Nothing
Application.ScreenUpdating = True
MsgBox " Data consolodiate successfully !", vbInformation, "Data Import"
End Sub
Please suggest what changes to make
Thanks for your precious time
Regards,
Mallesh :think::help