• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Copy and paste according to Date Range in VBA

Narend

Member
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 <<<

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:
Hello, if your dates are real Excel date rather than text so you can use a filter or an advanced filter …​
 
Hello,
Thank for your response.
I am unable to add advance filter code in this data set, can you do this for me.
I want to create date range and once I will enter the date range into the respected fields all the data from all the user files will be copied and paste into the master file.

I hope now it is clear, what I want.

Thanks
 
Last edited by a moderator:
In VBA, how can we copy the data from multiple files within a specific date range and paste into Master file or workbook.

I want to copy the data from multiple workbooks within a specific date ranges and paste into a Master file dynamically.

In the above code, I am unable to find where to add the date range criteria, I tried but it is not working.

Please try if possible.

Thanks
 
Find out manually a way according to your « specific date ranges » then once found​
activate the Macro Recorder and restart the same operations in order you get your own code base …​
As a reminder the VBA Range.Copy method copies only the visible cells​
the reason why a filter is the beginner way to go just before this Copy method obviously !​
What could mean « into a Master file dynamically » ?​
As nothing is dynamic under Excel, each time you launch a VBA procedure all is static …​
 
Back
Top