Run macro from active workbook whose name keeps changing

  • Thread starter Thread starter u208839
  • Start date Start date
U

u208839

Hello,

I have a macro that copies data from the active sheet to a separate sheet. Although the MBC Data Backup.xlsb file will never change, the name of the active workbook where the macro resides "Iron Horse MBC Listing 0.3.0.xlsb" will change from time to time and causes this scrip to fail. How can I makethis work without referencing the actual name of the active file?


Sub Backup()

' Backup Macro

Workbooks.Open Filename:="C:\NSD\Personal\MBC Data Backup.xlsb"
Range("A9:N20000").Select
Selection.ClearContents
Range("A9").Select

Windows("Iron Horse MBC Listing 0.3.0.xlsb").Activate
Sheets("Data").Select
Range("A9:N20000").Select
Selection.Copy

Windows("MBC Data Backup.xlsb").Activate
Range("A9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Range("A9").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Range("A9").Select

Sheets("Cover").Select

End Sub
 
Have a look at...

GetOpenFilename()

...and 'Set' a fully qualified ref to it. For example...


Sub Backup()
Dim wkbSource As Workbook, wkbTarget As Workbook, sFilename$

sFilename = Get_FileToOpen("Excel Files ""*.xlsb"", (*.xls)")
If sFilename = "" Then Exit Sub '//user cancels

Set wkbSource = Workbooks("Iron Horse MBC Listing 0.3.0.xlsb")
Set wkbTarget = Workbooks.Open(Filename:=sFilename)

wkbTarget.Range("A9:N20000") = wkbSource.Range("A9:N20000")
wkbTarget.Close SaveChanges:=True

wkbSource.Sheets("Cover").Select
End Sub


Function Get_FileToOpen$(Optional FileTypes$)
If FileTypes = "" Then FileTypes = "All Files ""*.*"", (*.*)"
Get_FileToOpen = Application.GetOpenFilename(FileTypes)
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Note that the sample code assumes the activesheet in both files is
already set. Otherwise.., edit as follows:

Change

wkbTarget.Range("A9:N20000") = wkbSource.Range("A9:N20000")
TO

wkbTarget.Range("A9:N20000") = _
wkbSource.Sheets("Data").Range("A9:N20000")

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Hi Gary,

Thanks for the response. I used the codes you suggested but it didnt' work.It was failing at these 2 lines.

Keep in mind that the desination file "MBC Data Backup.xlsb" will never change name nor location. The source file where this macro resides in will change version from 3.0 to 3.1, 3.2, 3.3 etc). I think what I need is to figure how how to reference the active workbook as being dynamic instead of a static name.




Set wkbSource = Workbooks("Iron Horse MBC Listing 0.3.1.xlsb")
Set wkbTarget = Workbooks.Open(Filename:=sFilename)
 
Sorry, I misunderstood! Try...

Sub Backup()
Dim wkbTarget As Workbook
Const sFilename$ = "C:\NSD\Personal\MBC Data Backup.xlsb"

Set wkbTarget = Workbooks.Open(Filename:=sFilename)

wkbTarget.Range("A9:N20000") = _
ThisWorkbook.Sheets("Data").Range("A9:N20000")
wkbTarget.Close SaveChanges:=True

ThisWorkbook.Sheets("Cover").Select
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Garry,

Not working either. When the latest code was copied and pasted, the "wkbTarget.Range("A9:N20000") = _" turned red and errored out.

I also have a question regarding the copy and paste portion of the original code? Where would i put that ?
 
Not working either. When the latest code was copied and pasted, the
"wkbTarget.Range("A9:N20000") = _" turned red and errored out.
Click to expand...

Make sure there's no 'line-wrapping' in the code as a result of your
copy/paste. Otherwise, this works EXACTLY as you requested in my sample
file!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
I also have a question regarding the copy and paste portion of the
original code? Where would i put that ?
Click to expand...

Nowhere! My macro assigns the source range 'values' directly, replacing
any existing data on the target sheet.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Garry,

I'm so sorry. I'm way to new to vba to make heads and tail out of this. Any possibility for me to send you the actual files?
 
I'm so sorry. I'm way to new to vba to make heads and tail out of
this. Any possibility for me to send you the actual files?
Click to expand...

Ok then, follow these instructions...

1. Delete all existing macros I gave you;
2. In the code window of a standard module:
2.1 Double-click the module in Project Explorer;
2.2 Choose Insert>File... from the menubar;
2.3 Browse to the folder where you put the file you downloaded here.

https://app.box.com/s/23yqum8auvzx17h04u4f
(Look for "BackupMacro.txt")

3. Run the macro.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Garry,

Followed your instruction. Code failed at


wkbTarget.Range("A9:N20000") = _
ThisWorkbook.Sheets("Data").Range("A9:N20000")
 
Followed your instruction. Code failed at
wkbTarget.Range("A9:N20000") = _
ThisWorkbook.Sheets("Data").Range("A9:N20000")
Click to expand...

What error description is given? Does the workbook you inserted the
code into have a sheet named "Data"?

Note that 'ThisWorkbook' refers to the file containing the code!

Note that wkbTarget is the backup file specified as 'sFilename'!

On my machine this code opens wkbTarget and replaces any existing data
in the specified range with the data from the same range on "Data".

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Revise the code as follows...

Sub Backup()
Dim wkbTarget As Workbook
Const sFilename$ = "C:\NSD\Personal\MBC Data Backup.xlsb"

Application.ScreenUpdating = False
Set wkbTarget = Workbooks.Open(Filename:=sFilename)
wkbTarget.Range("A9:N20000").Value = _
ThisWorkbook.Sheets("Data").Range("A9:N20000").Value
wkbTarget.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub

...so you don't see any screen activity. Also, note that I *explicitly*
specified the 'Value' property for both ranges. This is a range's
default property but depending on OS, Excel version, and so on it's
always better 'good practice' to use fully qualified refs!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Used you the latest code. Error wants me to debug the line below. Also highlighted in yellow


wkbTarget.Range("A9:N20000").Value = _
ThisWorkbook.Sheets("Data").Range("A9:N20000").Value

Garry, I appreciate your assistance. It seems that I might have to work around and make sure to change the workbook name everytime I rename the it only.

Thank you so much for your assistance.
 
Garry, I appreciate your assistance. It seems that I might have to
work around and make sure to change the workbook name everytime I
rename the it only.
Click to expand...

That should not be necessary *IF* the code resides in the workbook with
the source data. The error you get suggests this is not the case, *OR*
there's no sheet in that file named "Data", *OR* the sheetname is
mis-spelled!

How are you executing the code?
Where is the code located? In a worksheet module OR a standard module?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Ok.., my mistake! change that problem line to the following...

wkbTarget.ActiveSheet.Range("A9:N20000").Value = _
ThisWorkbook.Sheets("Data").Range("A9:N20000").Value

...where it assumes the opened file is the active workbook and the
active sheet is whatever sheet was active when the file was last saved.

OR
you can specify the sheetname...

wkbTarget.Sheets("<name>").Range("A9:N20000").Value = _
ThisWorkbook.Sheets("Data").Range("A9:N20000").Value

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
For clarity, my test macro uses this line

Range("A9:N20000").Value = _
ThisWorkbook.Sheets("Data").Range("A9:N20000").Value

...which assumes the activesheet in the opened file. I manually
prepended the ref to the workbook before posting, but forgot to include
any ref to the sheet the range belonged to. Very bad on my part, and
you have my sincere apology for any troublesome time/energy spent!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Garry,

Let me take a few steps back and descript the whole file so you get a better understanding of how it's currently set up.

The main file is a directory listing of people. This list is about 7,000 lines long and growing. It's in a tab called "Data" inside the main file called "Iron Horse MBC Listing 0.3.0.xlsb". From time to time, I will need to update this file to include new features as requested. When I do update this file, I will name it to Iron Horse MBC Listing 0.3.1, 0.3.2, 0.3.3 and etc. as part of version control. Since this file will be maintain by a person who isn't very computer savvy, I want to preserve the data in the Data tab of this file.

To make this happen, I got a Before Save script which will automatically copy the data from the Iron Horse MBC Listing 0.3.0.xls file to a separate excel file called "MBC Data Backup.xlsb". This file is located on the local C drive C:\NSD\Personal\MBC Data Backup.xlsb. This destination file will never change location nor name. The des


Here is the before save script and it's in the ThisWorkbook. Using the Callup command, everytime the file is saved, it will automatically call up theBackup macro which will copy the data from the main file's Data tab to MBCData Backup.xlsb file. Keep in mind that the data is also being copied to the destination file tab called "Data" as well. Perhaps that is the confusion.

Source file: Iron Horse MBC Listing 0.3.0.xlsb - Data tab A9:N20000
Destination file: MBC Data Backup.xlsb - Data tab A9:N20000

-----------------
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)


Call Backup

End Sub


The Backup macro is on Module 8. Using the original code that posted, thisworks perfectly EXCEPT when the main files' name changed from 0.3.0 to 0.3..1 as part of version control processes.

Hopefully this helps you understand the situation. Anything you can do to assist is greatly appreciated.
 
Ok, the only thing unclear was the name of the target sheet in the
backup file. As per my most recent post, I realize the troubles you've
been having are due to my neglecting to ref the target sheet as I
explained. Again I apologize and also thank you for your persistence!
Here's the complete final version...

Sub Backup()
Dim wkbTarget As Workbook
Const sFilename$ = "C:\NSD\Personal\MBC Data Backup.xlsb"

Application.ScreenUpdating = False
Set wkbTarget = Workbooks.Open(Filename:=sFilename)
wkbTarget.Sheets("Data").Range("A9:N20000").Value = _
ThisWorkbook.Sheets("Data").Range("A9:N20000").Value
wkbTarget.Close SaveChanges:=True
Application.ScreenUpdating = True
End Sub

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Ok, the only thing unclear was the name of the target sheet in the
backup file. As per my most recent post, I realize the troubles you've
been having are due to my neglecting to ref the target sheet as I
explained. Again I apologize and also thank you for your persistence!
Here's the complete final version...

Sub Backup()
Dim wkbTarget As Workbook
Const sFilename$ = "C:\NSD\Personal\MBC Data Backup.xlsb"

Application.ScreenUpdating = False
Set wkbTarget = Workbooks.Open(Filename:=sFilename)
wkbTarget.Sheets("Data").Range("A9:N20000").Value = _
ThisWorkbook.Sheets("Data").Range("A9:N20000").Value
Click to expand...
Garry,

IT WORKED !!! You are a genius my friend and have saved me countless hours in the long run. Thank you for everything and no, you don't need apologize for anything. I'm grateful. Thank you again.
 
Back
Top