Template with Macro won't work once copy of template is made and file name is changed

**Sharonn** · 05-15-2013, 12:02 PM

I created a Simple Macro to go to another excel file and copy information from that excel file back into a template. However since it is a template the file name will change when a copy is made to be used and because I created my macro by recording it, it won't work in the new file because of the new file name. How do I make this macro dynamic with the changing file name? It errors at the - Windows("Trial Updated_Sharon_DO NOT USE.xlsm").Activate- line because the file name has changed.

Here is my Macro (I am very new at writing macro's so please forgive my crude programing below):

Sheets("Master_Part_List").Select
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.ClearContents
Range("A1").Select
Workbooks.Open Filename:="Z:\Jobs\HARTJE MAST.xlsx"
Range("D1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Windows("Trial Updated_Sharon_DO NOT USE.xlsm").Activate
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Bid Material").Select

Thanks for what ever assitance that can be provided

**HaHoBe** · 05-15-2013, 12:22 PM

Hi, Sharonn,

maybe use some objects for this (code is unteste):

Sub Sharonn()
Dim wbCode As Workbook
Dim wbData As Workbook

Set wbCode = ThisWorkbook
With Sheets("Master_Part_List")
  .Cells.ClearContents
  .Range("A1").Select
End With
Set wbData = Workbooks.Open("Z:\Jobs\HARTJE MAST.xlsx")
If Not wbData Is Nothing Then
  With wbData.ActiveSheet
    .Range(.Range("A1"), .Cells.SpecialCells(xlLastCell)).Copy
  End With
End If
wbData.ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
Sheets("Bid Material").Select
wbData.Close False

Set wbData = Nothing
Set wbCode = Nothing
End Sub

Ciao,
Holger

**Sharonn** · 05-15-2013, 03:48 PM

Hi Holger

Thanks for this. I am having two errors with it (Again so new at this that it might just be something easy I am missing):

1) I am getting a run-time error at - sheets ("Bid Material").Select line. Not sure why.
2) Also it is getting to this point however the information that was copied from the Hartje Mast file is not pasting. It is suppose to paste into a tab titiled "Mater_Parts_List". Since I used the Macro record button I doesn't look like this was included in my first post.
3) The Sheets("Bid Material").Select is the last thing that should occur setting the screen view for the user back to that tab.

Again Thanks for taking the time to help
Sharon

**HaHoBe** · 05-15-2013, 11:34 PM

Hi, Sharonn,

regarding Sheets("Bid Material").Select as well as not pasting the code I think this sheet is within the worksheet which holds the code and that I mixed the workbooks. If you skip the code lines like

wbCode.ActiveSheet.Range("A1").PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
wbData.Close False
Sheets("Bid Material").Select

it should do fine. As the newly opened workbook will be closed I don´t know if it really is necessary to select the sheet (that sheet would have been the active sheet within the workbook with code).

Ciao,
Holger

Template with Macro won't work once copy of template is made and file name is changed

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Template with Macro won't work once copy of template is made and file name is changed

Re: Template with Macro won't work once copy of template is made and file name is changed

Re: Template with Macro won't work once copy of template is made and file name is changed

Re: Template with Macro won't work once copy of template is made and file name is changed

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions