Macro doesn't work when workbook is renamed

**tandi** · 07-23-2012, 11:45 AM

I have a worksheet that has a button, which runs a macro that copies certain information into another workbook. The macro is saved in the workbook, because it will be used by 5 different people. When the workbook is opened it is renamed so the orginal stays empty for the next person. But, after it is renamed, the macro will not work. Is there a way to make the macro work no matter what the file name is?
I am using Excel 2010

**abousetta** · 07-23-2012, 11:51 AM

We'll need to see the code. It's probably a simple tweak but can't do it blindly.

**tandi** · 07-23-2012, 12:05 PM

Sub QUOTETRACKER()
'
' QUOTETRACKER Macro
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
    Workbooks.Open Filename:= _
        "J:\1-FRONT_OFFICE\ACCOUNTING - HR\1- QUOTES\PROJECT QUOTE TRACKER.xlsx"
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Windows("TEST - QUOTE SHEET.xlsm").Activate
    Range("C2").Select
    Selection.Copy
    Windows("PROJECT QUOTE TRACKER.xlsx").Activate
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("TEST - QUOTE SHEET.xlsm").Activate
    Range("C3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("PROJECT QUOTE TRACKER.xlsx").Activate
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("TEST - QUOTE SHEET.xlsm").Activate
    Range("B6:C6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("PROJECT QUOTE TRACKER.xlsx").Activate
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("TEST - QUOTE SHEET.xlsm").Activate
    Range("B7:C7").Select
    ActiveWindow.SmallScroll Down:=6
    Range("B13:E13").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("PROJECT QUOTE TRACKER.xlsx").Activate
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Windows("TEST - QUOTE SHEET.xlsm").Activate
    Range("C11").Select
    ActiveWindow.SmallScroll Down:=12
    Range("A23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("PROJECT QUOTE TRACKER.xlsx").Activate
    Range("E2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub

**abousetta** · 07-23-2012, 12:19 PM

Please use code tags around your code as it makes it easier to read.

I am thinking that the code is in a workbook named TEST - QUOTE SHEET.xlsm. If so, change all the lines with:

Windows("TEST - QUOTE SHEET.xlsm").Activate

to

ThisWorkbook.Activate

Now having said that, this code should be cleaned up. You don't need to activate workbooks back and forth. If this is of interest to you, then you can start a new thread asking for help on cleaning up the code and making it more efficient.

**tandi** · 07-23-2012, 12:49 PM

Thank you, I will make the change and see if that corrects my problem. I'm sorry, but I don't know what code tags are. I am self taught and know just enough to be dangerous. I appreciate your help and I will submit another thread to clean up the code. Thanks a million.

**abousetta** · 07-23-2012, 01:13 PM

Code tags are available on the 'Go Advanced' pane or you can manually put them in by putting [CODE] ... [/CODE?] around your code. (just remove the question mark I put in to prevent it from recognizing the Code tags)

If you your problem is solved then please mark the thread as Solved (under Thread Tools or Advanced Pane >> First post >> change the prefix).

Good luck.

abousetta