Copying from a worksheet to another workbook where both change dates

Mykahlia

New Member
Joined
Feb 17, 2025
Messages
6
Office Version
  1. 365
Platform
  1. Windows
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.
1739813112654.png


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.
1739813231510.png


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

VBA Code:
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
The drive is c and the name of the workbook is SSC Prod Report - (Date changes to current day date)
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,924
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top