Solved VBA One Workbook to Another

October 2, 2020 at 04:52:27
Specs: Windows 10
Hello. I'm new to VBA. I recorded a Macro to copy the data of a worksheet from a workbook and paste it to another worksheet of another workbook. I was successful, but I realized that the workbook name where I'm copying the data from changes every week. The name of the workbook is "Weekly Performance Management Results - Week Ending (mm.dd.yy)". The date is a variable, so when I run my Macro, it only works for the workbook of the first week. The workbook contains same worksheets and formats. Is it possible to have a code that works when the workbook for the new week comes in?

message edited by CuriousChris


See More: VBA One Workbook to Another


✔ Best Answer
October 2, 2020 at 17:01:59
Before we work on your specific issue, I need to point out a discrepancy in a couple of things that you said. In your OP you said:

"The name of the workbook is "Weekly Performance Management Results - Week Ending (mm.dd.yy)""

Then in your response #2 you posted code that shows a file name with 09.11.2020. That date format is mm.dd.yyyy not mm.dd.yy and also does not use parenthesis. These differences may not seem important to you at this time, but as you work with VBA more and more, you will come to discover that those differences are huge, especically if you are asking for help in writing code. We can only offer code based on the information that we are given and if we write code based on incomplete or incorrect information, the code will not work as expected.

Going forward, I am going to use mm.dd.yyyy without parenthesis since that is what your code is currently using.

OK, so back to the task at hand:

re: Where should I put this part: Sub BuildWorkbookName()?

That is nothing more than the name of my macro. You named your macro Test: Sub Test()

I named my example macro Sub BuildWorkbookName() because I like the names of my macros to describe what they do.

If you are happy with using the name "Test" for your code, you don't need to change that line.

re: I edited the first line.

Yes, you did, but you didn't add any code to populate the weekDate variable with the date in the name of the workbook. VBA needs to know what Date to use in order to match the name of the workbook. If you don't assign a value to the variable, VBA with see "nothing" when it reads that variable and will go looking for a workbook named:

Weekly Performance Management Results - Implementation Week Ending .xlsx

This will cause the code to fail since it will never find a workbook with that name.

I don't know where the date in the filename comes from...I assume that you do. How does the date end up in the file name? If it is entered manually when you save the file, then you will probably need to have the VBA code ask for the date each time you run the code.

Since I don't know anything about your work process, it's difficult for me to provide a specific solution, but something like the code found below might work. However, before I post the code, I want to mention a few things:

1 - You will note that I included a few lines that begin with a single apostrophe. Those lines are known as "comments". Comments are used to describe what the code is doing so that not only can the user understand the flow of the code but also so that the author can keep track of what the code is doing. I have easily written thousands of macros over the years, both for myself and for others. If I didn't include comments in my macros there is no way that I could look at them several years (or sometimes even days) later and remember what it is supposed to be doing. You should get into the habit of using comments, even as far as adding them to code created by the recorder.

2 - The macro recorder produces code that is very basic and inefficient. As you quickly discovered, it often needs to be edited and enhanced to be useful for anything other than some very simple tasks. For the time being, I have left most of your code alone but once we solve your main issue, I will offer some ways to improve the recorded sections of the code so that it runs much smoother.

3 - As a new user of VBA, I suggest that you review the tutorial found at the following link. I assume that as of now, you are simply running the code and hoping for the best. When the code fails, you don't know why and you don't know know which line caused the error. Once you are familiar with some of the ways that you can Debug the code - especially using the Single Step function - you will have a much better understanding of how VBA works.

https://www.computing.net/howtos/sh...

OK, with all that said, try the following code and let me know if it works for you. Note: The data will be pasted into Book3 since that is what your original code was doing. Eventually that needs to be dealt with also. I assume that you will not always want the data pasted into Book3.

Sub CopyFromWorkbook()

On Error GoTo errorHandler

'Request date from user, Exit Sub if Canceled

enterDate:
   weekDate = Application.InputBox("Enter Date in mm.dd.yyyy Format" & vbCrLf & "test", "Enter Date")
   If weekDate = False Then Exit Sub

'Copy/Paste Table

    Windows( _
"Weekly Performance Management Results - Implementation Week Ending " & weekDate & ".xlsx" _
).Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Windows("Book3").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Exit Sub
    
'Error Handler for Invalid Date entry

errorHandler:
  MsgBox "Invalid Date or Date Format" & vbCrLf & vbCrLf & _
         "Please try again."
  GoTo enterDate
  
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code



#1
October 2, 2020 at 05:58:00
Without seeing the code you are using to reference the workbook, it's kind of hard to offer a specific solution.

Assuming (dangerous) that you have hardcoded the date into your workbook name, you would need to change the date portion to be a variable.

For example, this...

"Weekly Performance Management Results - Week Ending (10.02.20)"

...would need to be something like:

"Weekly Performance Management Results - Week Ending (" & weekDate & ")"

The weekDate variable would need to be populated and formatted prior to executing whatever instructions use the workbook name.

To use the current date, it would look something like this:

Sub BuildWorkbookName()

'Populate and format Today's date
     weekDate = Format(Date, "mm.dd.yy")

'Build Workbook Name
     MsgBox "Weekly Performance Management Results - Week Ending (" & weekDate & ")"

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
October 2, 2020 at 06:27:38
DerbyDad03, I checked VBA and here's the recorded code:

Windows( _
"Weekly Performance Management Results - Implementation Week Ending 09.11.2020.xlsx" _
).Activate
Range("Table_PM_C_Sampling.accdb_15[[#Headers],[Review ID]]").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Windows("Book3").Activate
ActiveSheet.Paste


Reply ↓  Report •

#3
October 2, 2020 at 06:55:25
Before we proceed, please click on the link at the bottom of this post and read the instructions on how to post VBA code in this forum. Then edit/repost your code so that the VBA format is retained.

Once that is complete, let us know what further questions you have. I believe that I provided the solution to your issue:

"You would need to change the date portion to be a variable."

Specifically, this line needs to be changed:

Windows( _
"Weekly Performance Management Results - Implementation Week Ending 09.11.2020.xlsx" _
).Activate

It should look like this:

Windows( _
"Weekly Performance Management Results - Implementation Week Ending " & weekDate & ".xlsx" _
).Activate

You then need to tell VBA what date the weekDate variable should contain. Once the weekDate variable is populated, the correct workbook will be referenced.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Related Solutions

#4
October 2, 2020 at 13:12:16
I edited the first line. Where should I put this part: Sub BuildWorkbookName()? Sorry, I'm really a beginner, but thank you for your patience.
Sub Test()
'
' Test Macro
'

'
    Windows( _
"Weekly Performance Management Results - Implementation Week Ending " & weekDate & ".xlsx" _
).Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Windows("Book2").Activate
    Range("A1").Select
    ActiveSheet.Paste
End Sub


Reply ↓  Report •

#5
October 2, 2020 at 17:01:59
✔ Best Answer
Before we work on your specific issue, I need to point out a discrepancy in a couple of things that you said. In your OP you said:

"The name of the workbook is "Weekly Performance Management Results - Week Ending (mm.dd.yy)""

Then in your response #2 you posted code that shows a file name with 09.11.2020. That date format is mm.dd.yyyy not mm.dd.yy and also does not use parenthesis. These differences may not seem important to you at this time, but as you work with VBA more and more, you will come to discover that those differences are huge, especically if you are asking for help in writing code. We can only offer code based on the information that we are given and if we write code based on incomplete or incorrect information, the code will not work as expected.

Going forward, I am going to use mm.dd.yyyy without parenthesis since that is what your code is currently using.

OK, so back to the task at hand:

re: Where should I put this part: Sub BuildWorkbookName()?

That is nothing more than the name of my macro. You named your macro Test: Sub Test()

I named my example macro Sub BuildWorkbookName() because I like the names of my macros to describe what they do.

If you are happy with using the name "Test" for your code, you don't need to change that line.

re: I edited the first line.

Yes, you did, but you didn't add any code to populate the weekDate variable with the date in the name of the workbook. VBA needs to know what Date to use in order to match the name of the workbook. If you don't assign a value to the variable, VBA with see "nothing" when it reads that variable and will go looking for a workbook named:

Weekly Performance Management Results - Implementation Week Ending .xlsx

This will cause the code to fail since it will never find a workbook with that name.

I don't know where the date in the filename comes from...I assume that you do. How does the date end up in the file name? If it is entered manually when you save the file, then you will probably need to have the VBA code ask for the date each time you run the code.

Since I don't know anything about your work process, it's difficult for me to provide a specific solution, but something like the code found below might work. However, before I post the code, I want to mention a few things:

1 - You will note that I included a few lines that begin with a single apostrophe. Those lines are known as "comments". Comments are used to describe what the code is doing so that not only can the user understand the flow of the code but also so that the author can keep track of what the code is doing. I have easily written thousands of macros over the years, both for myself and for others. If I didn't include comments in my macros there is no way that I could look at them several years (or sometimes even days) later and remember what it is supposed to be doing. You should get into the habit of using comments, even as far as adding them to code created by the recorder.

2 - The macro recorder produces code that is very basic and inefficient. As you quickly discovered, it often needs to be edited and enhanced to be useful for anything other than some very simple tasks. For the time being, I have left most of your code alone but once we solve your main issue, I will offer some ways to improve the recorded sections of the code so that it runs much smoother.

3 - As a new user of VBA, I suggest that you review the tutorial found at the following link. I assume that as of now, you are simply running the code and hoping for the best. When the code fails, you don't know why and you don't know know which line caused the error. Once you are familiar with some of the ways that you can Debug the code - especially using the Single Step function - you will have a much better understanding of how VBA works.

https://www.computing.net/howtos/sh...

OK, with all that said, try the following code and let me know if it works for you. Note: The data will be pasted into Book3 since that is what your original code was doing. Eventually that needs to be dealt with also. I assume that you will not always want the data pasted into Book3.

Sub CopyFromWorkbook()

On Error GoTo errorHandler

'Request date from user, Exit Sub if Canceled

enterDate:
   weekDate = Application.InputBox("Enter Date in mm.dd.yyyy Format" & vbCrLf & "test", "Enter Date")
   If weekDate = False Then Exit Sub

'Copy/Paste Table

    Windows( _
"Weekly Performance Management Results - Implementation Week Ending " & weekDate & ".xlsx" _
).Activate
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Windows("Book3").Activate
    Range("A1").Select
    ActiveSheet.Paste
    Exit Sub
    
'Error Handler for Invalid Date entry

errorHandler:
  MsgBox "Invalid Date or Date Format" & vbCrLf & vbCrLf & _
         "Please try again."
  GoTo enterDate
  
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
October 5, 2020 at 02:55:34
DerbyDad03 thank you for being so patient in explaining how it works. I'm sorry for the lapses, and I appreciate that you pointed out the differences in the data I presented and coding I used.

Reply ↓  Report •

Ask Question