malleshg24
01-12-2018, 02:16 PM
Hi Team,:help
I want to consolidate all workbooks in master workbook.
From daily report file which has Total 14 sheets, I pick up only my teams data,
My Teams Data are in sheets (sheet1,sheet2,sheet3 sheet4 and sheet6 sheet8 ) and every sheets got name also here.
My task is pick sheet1 data Paste in Master workbooks sheet1, sheet2-sheet2 and so on.
every sheets has header here. Sometime in sheet6 and sheet8. there will not be any data. then ignore these .
Below are my code that work for single workbooks single sheet consolidation,
But this time the task is completely different. plz assist.
Sub CosolodiateFromDifferentworkbook()
Dim wbk As Workbook
Dim sht As Worksheet, Nsht As Worksheet
Application.ScreenUpdating = False
FP = "C:\Users\Desktop\Todays Report\"
FN = Dir(FP)
Set sht = Sheets.Add(, Sheets("Task"))
sht.Name = "Master"
With sht.Range("A1:E1")
.Value = Array("Date", "Curr", "Agent", "Product", "Sales") 'i can increate the array here as per my requ
.Font.Bold = True
.Interior.Color = 15123099
.Font.Size = 16
.Font.Name = "Algerian"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
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
sht.Range("A1:E1").EntireColumn.AutoFit
sht.Range("A1").CurrentRegion.Borders.LineStyle = -4119
sht.Range("A1").CurrentRegion.BorderAround 1, xlThick
ActiveWindow.DisplayGridlines = False
Set wbk = Nothing
Application.ScreenUpdating = True
MsgBox " Data consolodiate successfully !", vbInformation, "Data Import"
End Sub:help
I want to consolidate all workbooks in master workbook.
From daily report file which has Total 14 sheets, I pick up only my teams data,
My Teams Data are in sheets (sheet1,sheet2,sheet3 sheet4 and sheet6 sheet8 ) and every sheets got name also here.
My task is pick sheet1 data Paste in Master workbooks sheet1, sheet2-sheet2 and so on.
every sheets has header here. Sometime in sheet6 and sheet8. there will not be any data. then ignore these .
Below are my code that work for single workbooks single sheet consolidation,
But this time the task is completely different. plz assist.
Sub CosolodiateFromDifferentworkbook()
Dim wbk As Workbook
Dim sht As Worksheet, Nsht As Worksheet
Application.ScreenUpdating = False
FP = "C:\Users\Desktop\Todays Report\"
FN = Dir(FP)
Set sht = Sheets.Add(, Sheets("Task"))
sht.Name = "Master"
With sht.Range("A1:E1")
.Value = Array("Date", "Curr", "Agent", "Product", "Sales") 'i can increate the array here as per my requ
.Font.Bold = True
.Interior.Color = 15123099
.Font.Size = 16
.Font.Name = "Algerian"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
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
sht.Range("A1:E1").EntireColumn.AutoFit
sht.Range("A1").CurrentRegion.Borders.LineStyle = -4119
sht.Range("A1").CurrentRegion.BorderAround 1, xlThick
ActiveWindow.DisplayGridlines = False
Set wbk = Nothing
Application.ScreenUpdating = True
MsgBox " Data consolodiate successfully !", vbInformation, "Data Import"
End Sub:help