Transfer Data for Multiple into One Master Sheet

[sg2209]

hi ,


i have written the below code by reading some threads on Mr Excel .

I am trying to transfer data from Multiple workbooks into a master excel workbook automatically using VBA.

i have saved my excel sheets (.xlsb) in a folder and also a Master Sheet with name of Collated C:\Users\HP\Desktop\UPD

All the sheets are saved by name of upd05122017, upd06122017. i have also input a code in This Workbook so when i opened the master file , all the data is being captured .

below is the code , please help

Sub LoopThroughDirectory()
Dim MyFile As String
Dim erow
MyFile = Dir("C:\Users\HP\Desktop\UPD")


Do While Len(MyFile) > 0
If MyFile = "Collated.xlsm" Then
Exit Sub
End If


Workbooks.Open (MyFile)
Range("A2:S5000").Copy
ActiveWorkbook.Close


erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Worksheets("Raw").Range(Cells(erow, 1), Cells(erow, 19))


MyFile = Dir
Loop


End Sub

 

[Fluff]

Untested, but try

Sub LoopThroughDirectory()
   Dim MyFile As String
   Dim MyPath As String
   Dim Wbk As Workbook
   Dim DestSht As Worksheet
   
   Set DestSht = ThisWorkbook.Sheets("Raw")
   MyPath = "C:\Users\HP\Desktop\UPD\"
   MyFile = Dir(MyPath)
   
   Do While Len(MyFile) > 0
      If MyFile <> "Collated.xlsm" Then
      
         Set Wbk = Workbooks.Open(MyPath & MyFile)
         Wbk.Sheets("[COLOR=#ff0000]Sheet1[/COLOR]").Range("A2:S5000").copy DestSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
   
         Wbk.Close False
      End If
      
      MyFile = Dir
   Loop

End Sub

Change the sheet name in red to match the sheet name you are trying to copy from

 

[sg2209]

thank you it works , however when i saved the Collated Workbook and again open it , it once again copy the data so Duplicate data is there , how can i go for it so it does not copy the data from the sheet that was already copied.

if data was copied from upd05122017 , it should not again copy from that sheet .

 

[Fluff]

The easiest option is to clear the collated sheet first.

Sub LoopThroughDirectory()
   Dim MyFile As String
   Dim MyPath As String
   Dim Wbk As Workbook
   Dim DestSht As Worksheet
   
   Set DestSht = ThisWorkbook.Sheets("Raw")
   DestSht.UsedRange.Offset(1).Clear
   MyPath = "C:\Users\HP\Desktop\UPD\"
   MyFile = Dir(MyPath)
   
   Do While Len(MyFile) > 0
      If MyFile <> "Collated.xlsm" Then
      
         Set Wbk = Workbooks.Open(MyPath & MyFile)
         Wbk.Sheets("Sheet1").Range("A2:S5000").copy DestSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
   
         Wbk.Close False
      End If
      
      MyFile = Dir
   Loop

End Sub

 

[sg2209]

Ok. Thank you so much . Appreciate your help

 

[Fluff]

Glad to help & thanks for the feedback

 

[sg2209]

@Fluff , its not working when i am using the path "\\124.4.4.50\9100201_n01\DASHBOARD&REPORTING\2017\December\UPD"

 

[Fluff]

AFAIK, you cannot use an IP address with Dir. It needs to be a mapped drive.

 

[sg2209]

i did try by mapping it , and the drive is Z Should i use like this

"X:\DASHBOARD&REPORTING\2017\December\UPD"

 

[Fluff]

That would depend on how you've mapped it, but if you mapped it to the Z drive, the path name should begin with Z not X.
Best bet is try it & see.