Copying from a worksheet to another workbook where both change dates

[Mykahlia]

Hello,

I am new to VBA and I am hoping someone can help me figure out this issue. I tried setting up a macro to copy data from one worksheet that I download daily in to a another workbook that we update the file by save as file with the same name but change the date.

This is an example of the worksheet.

As you can see the worksheet name changes each day it is downloaded. Where I need to copy in to is the workbook that we update the date daily on.

I have already set up a macro for sorting as needed before pasting in to the workbook. I just cant figure out how to get it to copy with VBA or Macro with the name changes. Any help is appreciated. Thank you.

 

[HighAndWilder]

Hello,

I am new to VBA and I am hoping someone can help me figure out this issue. I tried setting up a macro to copy data from one worksheet that I download daily in to a another workbook that we update the file by save as file with the same name but change the date.

This is an example of the worksheet.
View attachment 122453

As you can see the worksheet name changes each day it is downloaded. Where I need to copy in to is the workbook that we update the date daily on.
View attachment 122454

I have already set up a macro for sorting as needed before pasting in to the workbook. I just cant figure out how to get it to copy with VBA or Macro with the name changes. Any help is appreciated. Thank you.

This VBA code needs to go into a main code module in the workbook that you copy the data to.

It prompts for the Daily Download workbook so the code does not need to know the of the ever changing filename.

To test, set up a temporary XLSX workbook containing the destination worksheet and the code and run the code,

Do you need to indicate which workbook the data imported has come from?

I have done this in a way in which it may be easier for you to understand with comments.

Public Sub subImportDailyData()
Dim strFileName As Variant
Dim WsDestination As Worksheet
Dim WsSource As Worksheet
Dim arr() As Variant

  ActiveWorkbook.Save
  
  ThisWorkbook.Activate
    
  ' IMPORTANT Change the name of the worksheet below as appropriate. IMPORTANT
  Set WsDestination = Worksheets("DailyDownloads")

  ' Prompt for the Daily Download workbook.
  strFileName = Application.GetOpenFilename(Title:="Browse for Daily Download workbook.", FileFilter:="Excel Files (*.xls*),*xls*")
  
  If strFileName <> False Then
  
    ' Open the selected workbook.
    Workbooks.Open strFileName
    
    ' Assumes that the data needed is in sheet 1.
    Set WsSource = ActiveWorkbook.Sheets(1)
    
    ' Copies the data to an array.
    With WsSource.Range("A1").CurrentRegion
      arr = .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count)
    End With
    
    ' Writes the data to the sheet specified in WsDestination above.
    WsDestination.Cells(WsDestination.Cells(Rows.Count, 1).End(xlUp).Row + 1, 1).Resize(UBound(arr), UBound(arr, 2)).Value = arr
    
    ' Closes the Daily Download workbook.
    ActiveWorkbook.Close False
    
    ' Confirmation message.
    MsgBox UBound(arr) & " rows of data copied from " & strFileName, vbOKOnly, "Confirmation"
    
  Else
  
    ' Message to warn that no file was selected.
    MsgBox "No Daily Download workbook has been selected.", vbOKOnly, "Warning"
  
  End If
  
  ActiveWorkbook.Save
  
End Sub

 

[Mykahlia]

The file looks good but now for a silly question. Is there a way to store this in a personal macrobook instead of the actual workbook. The problem I run in to is the file that I download daily is a csv fill and we copy the data to a xlsx book. I also don't need the warning message at the end because this is a report we run and pull every single morning, The report we export from another program changes the name daily then the destination where I want the data to go to, changes the date on the book name every morning. The other VBA codes for other reports I am able to save them in my personal macrobook but not sure on this one. Thank you so much for all your help.

 

[HighAndWilder]

The file looks good but now for a silly question. Is there a way to store this in a personal macrobook instead of the actual workbook. The problem I run in to is the file that I download daily is a csv fill and we copy the data to a xlsx book. I also don't need the warning message at the end because this is a report we run and pull every single morning, The report we export from another program changes the name daily then the destination where I want the data to go to, changes the date on the book name every morning. The other VBA codes for other reports I am able to save them in my personal macrobook but not sure on this one. Thank you so much for all your help.

Almost anything is possible.

What is the name of the personal macrobook workbook where the code will be?

What is the name and the and folder location of the workbook where the data will be imported into.

This code will need to know that the destination workbook is open.

What is the destination worksheet name?

 

[Mykahlia]

The macros are stored in Personal.xlsb.

The location folder for the destination is Mod Squad\01.Projects - Mod Squad\FY25 Credentials\Team Reports
The destination worksheet name is All IDs

 

[HighAndWilder]

The macros are stored in Personal.xlsb.

The location folder for the destination is Mod Squad\01.Projects - Mod Squad\FY25 Credentials\Team Reports
The destination worksheet name is All IDs

What is the destination workbook name and drive name?

I'll catch up in the morning.

 

[Mykahlia]

The drive is c and the name of the workbook is SSC Prod Report - (Date changes to current day date)

 

[HighAndWilder]

The drive is c and the name of the workbook is SSC Prod Report - (Date changes to current day date)

How is the date represented in the file name?

 

[Mykahlia]

it represents as 02-17-2025 so basically it is SSC Prod - Report -02-17-2025.xlsx

 

[HighAndWilder]

it represents as 02-17-2025 so basically it is SSC Prod - Report -02-17-2025.xlsx

I'll look at it in the morning Greenwich Mean Time (GMT) .