Hello champs,
I am looking for a VBA help, please see my question below.
I have multiple user and all user has one separate files with the same formatting.
with the help of VBA
I want to create a macro, which will copy all the users data from all the users file and paste in dynamically below the existing data into our Master file dynamically.
So last_row needs to be find for all the files.
I have created a macro, for this purpose.
Copy data from all the user's file and paste it into the master one dynamcially one by one.
I need to copy the data from all the users from a particular date range and paste dynamically into the master file by finding the last row, one by one.
Please see the below code I have created and it is working fine.
>>> use code - tags <<<
>>> as You've noted <<<
this code is working perfectly.
But I want to add one more criteria here, to copy data according to date wise or by entering a date range.
I will enter the Date range, such as :- start_date and end_date and hit enter or run the code.
It will copy the data from all the users file according to date range specified and paste into the master file dynamically by finding the last row.
If need more clarification then please ask, I will try to explain.
Thanks
I am looking for a VBA help, please see my question below.
I have multiple user and all user has one separate files with the same formatting.
with the help of VBA
I want to create a macro, which will copy all the users data from all the users file and paste in dynamically below the existing data into our Master file dynamically.
So last_row needs to be find for all the files.
I have created a macro, for this purpose.
Copy data from all the user's file and paste it into the master one dynamcially one by one.
I need to copy the data from all the users from a particular date range and paste dynamically into the master file by finding the last row, one by one.
Please see the below code I have created and it is working fine.
>>> use code - tags <<<
>>> as You've noted <<<
Code:
Sub Tracker_Compiler()
Dim n As Integer
Dim wb As Integer
Dim master As String
Dim Userfile As String
Dim L_Row As Long
Dim L_Dist As Long
Application.DisplayAlerts = False
Application.ScreenUpdating = False
master = ThisWorkbook.Name
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = True
.Title = "Locate Your Files"
.Show
n = .SelectedItems.Count
For wb = 1 To n
Path = .SelectedItems(wb)
Workbooks.Open (Path)
Userfile = ActiveWorkbook.Name
For Each Sheet In ActiveWorkbook.Worksheets
If Sheet.Name = "Tracker" Then
Sheet.Select
L_Row = Sheets("Tracker").Range("A1048576").End(xlUp).Row
'it will find the last row of the tracker sheet
Range("A2:E" & L_Row).Copy
'it will copy all the data from the tracker sheet
Windows(master).Activate
'this code will activate the master file where we will paste our copied data
L_Dist = Sheets("Sheet1").Range("A1048576").End(xlUp).Row + 1
'This code will fine the next blank row within the master file
Sheets("Sheet1").Range("A" & L_Dist).Select
'this code will fine the last blank cell in the master file
ActiveSheet.Paste
Windows(Userfile).Close savechanges:=False
End If
Next Sheet
Next wb
End With
End Sub
this code is working perfectly.
But I want to add one more criteria here, to copy data according to date wise or by entering a date range.
I will enter the Date range, such as :- start_date and end_date and hit enter or run the code.
It will copy the data from all the users file according to date range specified and paste into the master file dynamically by finding the last row.
If need more clarification then please ask, I will try to explain.
Thanks
Last edited by a moderator: