VBA button to copy from another excel the filtered column and pasting into an xlsm

[Gtasios4]

Hi all,

I have a big excel file with products, codes description and delivery dates. At every start of the day we filter this excel file by date (col. G) and we send the daily imports by email.

We want to automate somehow that procedure by creating an xlsm file in a common server path for the imports team in which there would be a command button that when they press it, it will filters the above excel and pasting the rows from the today's imports.

Any help would be much appreciated!

 

[Flaiban]

Try this code :

Sub TransferDateToday()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim wsfilter As Worksheet
Dim CopyLastRow As Integer
Dim DestlastRow As Integer
Dim ws As Worksheet, rng As Range, LstRw As Long

    '1. open the workbook to copy from
    Workbooks.Open "C:\Users\Gtasious4\Desktop\Delivery Dates.xlsx"  ' Change for your path
    '2. Define each workbook
    Set wsCopy = Workbooks("Delivery Dates.xlsx").Sheets("Sheet1")
    Set wsDest = ThisWorkbook.ActiveSheet
    
    '3. Define last row in source data
    CopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    
    '4.Filter column G based on value
    wsCopy.Range("A1:H" & CopyLastRow).AutoFilter Field:=7, Operator:=xlFilterValues, Criteria1:=Format(Date, "dd/mm/yyyy")
    
    '5.Delet Visible data
    Application.DisplayAlerts = False
    wsDest.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
    
    '6. Copy data from Input Data to Forecast Source
    wsCopy.Range("A1:H" & CopyLastRow).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A1")
    On Error Resume Next
        wsDest.ShowAllData
    On Error GoTo 0

     '7. close and save source file
        Workbooks("Delivery Dates.xlsx").Close SaveChanges:=False
    
End Sub

 

[Gtasios4]

Try this code :

Sub TransferDateToday()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim wsfilter As Worksheet
Dim CopyLastRow As Integer
Dim DestlastRow As Integer
Dim ws As Worksheet, rng As Range, LstRw As Long

    '1. open the workbook to copy from
    Workbooks.Open "C:\Users\Gtasious4\Desktop\Delivery Dates.xlsx"  ' Change for your path
    '2. Define each workbook
    Set wsCopy = Workbooks("Delivery Dates.xlsx").Sheets("Sheet1")
    Set wsDest = ThisWorkbook.ActiveSheet
   
    '3. Define last row in source data
    CopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
   
    '4.Filter column G based on value
    wsCopy.Range("A1:H" & CopyLastRow).AutoFilter Field:=7, Operator:=xlFilterValues, Criteria1:=Format(Date, "dd/mm/yyyy")
   
    '5.Delet Visible data
    Application.DisplayAlerts = False
    wsDest.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
   
    '6. Copy data from Input Data to Forecast Source
    wsCopy.Range("A1:H" & CopyLastRow).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A1")
    On Error Resume Next
        wsDest.ShowAllData
    On Error GoTo 0

     '7. close and save source file
        Workbooks("Delivery Dates.xlsx").Close SaveChanges:=False
   
End Sub

Dear Flaiban,

Thank you for your valuable help. I've made the changes in the codes with the path and the workbooks' name. It works somehow in a way that I get the "todays deliveries".

I am facing though the below errors:

1) It opens the main-big excel while I want to be opened and filter in the background (somehow i want to be hiddenly linked)
2) It changes the header's format in the destination xlsm and removes the command button

Sub TransferDateToday()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim wsfilter As Worksheet
Dim CopyLastRow As Integer
Dim DestlastRow As Integer
Dim ws As Worksheet, rng As Range, LstRw As Long

    '1. open the workbook to copy from
    Workbooks.Open "C:\Users\iq2857\Documents\Deliveries 2022.xlsx"
    '2. Define each workbook
    Set wsCopy = Workbooks("Deliveries 2022.xlsx").Sheets("Deliveries")
    Set wsDest = ThisWorkbook.ActiveSheet
    
    '3. Define last row in source data
    CopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row
    
    '4.Filter column G based on value
    wsCopy.Range("A1:H" & CopyLastRow).AutoFilter Field:=7, Operator:=xlFilterValues, Criteria1:=Format(Date, "dd/mm/yyyy")
    
    '5.Delet Visible data
    Application.DisplayAlerts = False
    wsDest.UsedRange.Offset(1, 0).Resize(ActiveSheet.UsedRange.Rows.Count - 1).Rows.Delete
    Application.DisplayAlerts = True
    
    '6. Copy data from Input Data to Forecast Source
    wsCopy.Range("A1:H" & CopyLastRow).SpecialCells(xlCellTypeVisible).Copy wsDest.Range("A1")
    On Error Resume Next
        wsDest.ShowAllData
    On Error GoTo 0

     '7. close and save source file
        Workbooks("C:\Users\iq2857\Documents\Test Deliveries.xlsm").Close SaveChanges:=False
    
End Sub

My point is to have an xlsm file with that button and once a user press it he will gate the table ready and filtered with today's deliveries.

 

[Flaiban]

Change this :

'7. close and save source file
        Workbooks("C:\Users\iq2857\Documents\Test Deliveries.xlsm").Close SaveChanges:=False

For this :

 '7. close and save source file
        Workbooks("Deliveries 2022.xlsx").Close SaveChanges:=False

 

[Gtasios4]

Change this :

'7. close and save source file
        Workbooks("C:\Users\iq2857\Documents\Test Deliveries.xlsm").Close SaveChanges:=False

For this :

 '7. close and save source file
        Workbooks("Deliveries 2022.xlsx").Close SaveChanges:=False

Dear Flaiban,

In the path I have those 2 files

Deliveries2022 is the big source file from which the column is filtered based on date.

The destination file where I want the copied & filtered results to be pasted is in the "test deliveries" file, once I press the button load today's deliveries. Because, now when I press it it opens the deliveries 2022 and just filters it.

Therefore, even though your code works fine, I don't want the Deliveries2022 to open for the user; is it possible to make it hidden and work in the background, updating values etc?

Lastly is it possible to make a message box whenever it doesn't find any deliveries for today the user should get "There are no deliveries for today".

Thank you once again for your time & effort!

 

[Gtasios4]

Any hint on that?