Dear Friends,
i am learning VBA and went to several websites and forums and with the help of those i have created a small tool in excel.
where it gets collated multiple sheets into one, as of i have mapped the drive and its working file however now i want any one can do this exercise and its not possible for me to map the drive for everyone so we have shared folder which can be accessible though IP Address now got stuck how do i amend my code so it can work there.
IP Address : // 50.24.2.41
Shared Folder Name // Team_TT_Collections
Sub Folder Name //Sachin
i want my tool to be placed in the subfolder Sachin
there are 2 codes in Vlook Up everything is good too go
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("Sheet1").Range("A2:S5000").Copy DestSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
Wbk.Close False
End If
MyFile = Dir
Loop
'Freezing the Top Row
Rows("2:2").Select
ActiveWindow.FreezePanes = True
End Sub
Sub VlookupVBA()
'Vlook up from Team Name to Raw
Dim lLastRow As Long
With Sheets("Raw")
lLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("U2:U" & lLastRow)
.FormulaR1C1 = "=VLOOKUP(RC3,'Team Name'!C2:C3,2,FALSE)"
.Value = .Value
End With
End With
End Sub
Could you guys please review and help me
i am learning VBA and went to several websites and forums and with the help of those i have created a small tool in excel.
where it gets collated multiple sheets into one, as of i have mapped the drive and its working file however now i want any one can do this exercise and its not possible for me to map the drive for everyone so we have shared folder which can be accessible though IP Address now got stuck how do i amend my code so it can work there.
IP Address : // 50.24.2.41
Shared Folder Name // Team_TT_Collections
Sub Folder Name //Sachin
i want my tool to be placed in the subfolder Sachin
there are 2 codes in Vlook Up everything is good too go
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("Sheet1").Range("A2:S5000").Copy DestSht.Range("A" & Rows.Count).End(xlUp).Offset(1)
Wbk.Close False
End If
MyFile = Dir
Loop
'Freezing the Top Row
Rows("2:2").Select
ActiveWindow.FreezePanes = True
End Sub
Sub VlookupVBA()
'Vlook up from Team Name to Raw
Dim lLastRow As Long
With Sheets("Raw")
lLastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
With .Range("U2:U" & lLastRow)
.FormulaR1C1 = "=VLOOKUP(RC3,'Team Name'!C2:C3,2,FALSE)"
.Value = .Value
End With
End With
End Sub
Could you guys please review and help me
