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 make this 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 ?



On Wednesday, December 10, 2014 5:07:46 PM UTC-8, GS wrote:
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