Make Your Recorded Macro Independent of Which Sheet is Active

When you run a recorded macro, it may work great, except it keeps referring back to the original worksheet. It also refers back to the original data range. Both of these situations can be remedied by editing of the recorded macro.

In How To: Fix a Recorded Macro I showed how to make a recorded macro more efficient by omitting and combining statements and eliminating the selection of objects during macro execution. This is a more specific example that makes the macro independent of the conditions (active sheet and selection) when it was recorded.

In this example, a macro is recorded after data is selected to capture the steps required to insert a chart object in the active worksheet. This recording was made in Excel 2003; the steps in 2007 are similar, and I’ll cover them in another post.

Here is our original data in C7:F13 of Sheet1:

Data for recorded macro /></p> <p>I recorded this macro while I created a chart, without changing any other formatting.</p> <pre>Sub Macro1() ' ' Macro4 Macro ' Macro recorded 3/16/2009 by Jon Peltier '  '     Charts.Add     ActiveChart.ChartType = xlXYScatterLinesNoMarkers     ActiveChart.SetSourceData Source:=Sheets(

The Recorded Macro

I recorded this simple macro while creating the chart, without changing any formatting:

Sub Macro1()
'
' Macro4 Macro
' Macro recorded 3/16/2009 by Jon Peltier
'

'
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("C7:F13")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub
 

The worksheet name and selected range appear hard-coded in this macro. No matter what sheet is active or what range is selected, the chart will always appear on Sheet1 using the data in C7:F13 of Sheet1.

I will describe a couple ways to change this, both involving variables which are declared and assigned before the chart is created.

Fixing the Macro with String Variables

The sheet name and range address are present in the recorded macros as simple text. We can define string variables to contain the sheet name and the range address, as shown in this fixed macro.

Sub Macro1a()
'
' Macro4 Macro
' Macro recorded 3/16/2009 by Jon Peltier
' Fixed to use text variables for sheet and range

'
    Dim sSheetName As String
    Dim sDataRange As String

    sSheetName = ActiveSheet.Name
    sDataRange = Selection.Address

    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=Sheets(sSheetName).Range(sDataRange)
    ActiveChart.Location Where:=xlLocationAsObject, Name:=sSheetName
End Sub
 

Fixing the Macro with Object Variables

We can also use object variables, that is, a worksheet object for the active sheet and a range object for the data, as in the following fixed up macro.

Sub Macro1b()
'
' Macro4 Macro
' Macro recorded 3/16/2009 by Jon Peltier
' Fixed to use object variables for sheet and range

'
    Dim wksData As Worksheet
    Dim rngData As Range

    Set wksData = ActiveSheet
    Set rngData = Selection

    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=rngData
    ActiveChart.Location Where:=xlLocationAsObject, Name:=wksData.Name
End Sub
 

By adding only a few lines of code, the recorded macro has been made much more flexible. The choice whether to use string or object variables is not important in this case. In more complex procedures, one or the other approach may be advantageous, if a particular variable type is more useful later in the procedure.

 

Peltier Tech Charts for Excel

Comments

  1. Jon,

    I have come to you before when I have hit dead ends, and I am hoping you can help me out with something I am running into with 2007. I wasn’t sure where to post this, but am in need of help.

    In PowerPoint 2007 I have the option to paste my chart in, but with the entire workbook behind it (so that the user can actually go in and update everything themselves). My problem is, I get a PERSONAL.XLSB locked for editing error (when it is a chart). I have no idea how to get around this, and have been looking for answers all over… I saw mention of potentially creating my own Add-In?

    Also, when I copy and paste my chart into PowerPoint, I will lose any additional text boxes containing formulas that I may have added. Any ideas (oh, and I don’t want it to be a picture…)?

    I am missing 2003 about now, but hope you will be able to help ease the transition with tips for these things!

    Thank you!!

  2. LEM –

    I haven’t used Excel and PowerPoint 2007 together much, so I only know of the problems, not the workarounds. The deal with the personal.xlsb is that, if you change it to an add-in, it can be open multiple times without conflict. Which means it’s open in the instance in PowerPoint. That might be good or bad. So might having the workbook travel with the chart. Good to allow adjustments, bad to keep proprietary information secret.

  3. Ok i ran the below macro and it works great makes a new chart on the page before and uses the active sheet for referance.. the problem is, it only uses the active sheet for referance for 2 of my 4 data points not all… any suggestions?
    Thanks a bunch, your website has saved me a dozen times!!!

    Sub Chart()

    ‘ Chart Macro

    ‘ Keyboard Shortcut: Ctrl+k

    Range(“C13:C64”).Select
    ActiveWindow.SmallScroll Down:=-24
    Range(“C13:C64,N13:N64”).Select
    Range(“N13”).Activate
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.SmallScroll Down:=-21
    Range(“C13:C64,N13:N64,V13:V64”).Select
    Range(“V13”).Activate
    ActiveWindow.SmallScroll Down:=-18
    Range(“C13:C64,N13:N64,V13:V64,AG13:AG64”).Select
    Range(“AG13”).Activate
    Charts.Add

    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).XValues = “=”
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(4).Name = “=””Current Case Volume”””
    ActiveChart.SeriesCollection(4).Values = “=’Chef Canned Pasta’!$C$13:$C$64”
    ActiveChart.SeriesCollection(3).Name = “=””Case OH YAG”””
    ActiveChart.SeriesCollection(2).Name = “=””YAG Case Volume”””
    ActiveChart.SeriesCollection(1).Name = “=””Case OH Current”””
    ActiveChart.PlotArea.Select
    ActiveChart.ChartType = xlLine
    ActiveChart.SeriesCollection(1).XValues = “=’Chef Canned Pasta’!$B$13:$B$64”
    End Sub

  4. Hi Jon,

    I have a bunch of worksheets with data that all have the same format. I want to make the same plot for each worksheet. I tried using your code but input my own range as .Range(“R3:R130”) and therefore didn’t initialiye/use sDataRange. For some reason, the macro keeps plotting the data from every column of my worksheet in one graph. I’m using Excel 2010 and the xlLine chart type. Any ideas on what might be going wrong?

    Thank you,

    Daniel

  5. Daniel – You don’t mention how you input your own range.

  6. I want to run a macro on several sheets, one for each day of the month, that will copy data from the first worksheet named BCCwLosses to the active sheet when a button on that sheet is selected. The data on sheet BCCwLosses will be updated every hour and half hour. After the user updates, they select the sheet of the current day of the month and selects the button above the column for that hour or half hour. The data will be copied to the active sheet. I don’t want to have a macro for each sheet of the month that will do this. I know this code is wrong, I don’t know what I am doing. Can you help?
    Dim sSheetName As String
    Set sSheetName = ActiveSheet

    Sheets(“BCCwLosses”).Select
    Range(“E10”).Copy

    Sheets.ActiveSheet
    Range(“C6”).PasteSpecial Paste:=xlPasteValues

    Sheets(“BCCwLosses”).Select
    Range(“E12”).Copy

    Sheets.ActiveSheet
    Range(“C7”).PasteSpecial Paste:=xlPasteValues

    End Sub

  7. I’ll assume you can’t just use simple formulas
    =BCCwLosses!E10
    in C6 of the appropriate sheets, and
    =BCCwLosses!E12
    in C7 of the appropriate sheets.

    I think I’d just put this general routine into a regular code module:

    Sub UpdateActiveSheet()
      With ActiveSheet
        .Range("C6").value = Worksheets("BCCwLosses").Range("E10").Value
        .Range("C7").value = Worksheets("BCCwLosses").Range("E12").Value
      End With
    End Sub

    You can put a Forms Menu button onto each sheet, name it “Update”, and assign it to the UpdateActiveSheet routine.

  8. Thank you Jon for your input, I am not sure if this will work for what I needed to accomplish. I looked at your previous posting ‘Fixing the Macro with Object Variables’, and I did this instead & it works. Thanks again for your help!
    Sub GetData0100()

    ‘ GetData0100 Macro
    ‘ Macro recorded 9/19/2012 by dtrahan.
    ‘ Data is copied from worksheet BCCwLosses to active worksheet, col.B, 1:00.

    Dim wksData As Worksheet
    Set wksData = ActiveSheet

    Sheets(“BCCwLosses”).Range(“E10”).Copy
    ActiveSheet.Range(“B6”).PasteSpecial Paste:=xlPasteValues
    Sheets(“BCCwLosses”).Range(“E12”).Copy
    ActiveSheet.Range(“B7”).PasteSpecial Paste:=xlPasteValues
    Sheets(“BCCwLosses”).Range(“E14”).Copy
    ActiveSheet.Range(“B8”).PasteSpecial Paste:=xlPasteValues
    Sheets(“BCCwLosses”).Range(“E16”).Copy
    ActiveSheet.Range(“B9”).PasteSpecial Paste:=xlPasteValues
    Sheets(“BCCwLosses”).Range(“N17”).Copy
    ActiveSheet.Range(“B10”).PasteSpecial Paste:=xlPasteValues
    End Sub

  9. All that copying and pasting is slow. You can replace

    SourceReference.Copy
    TargetReference PasteSpecial xlValues

    with the faster and more direct

    TargetReference.Value = SourceReference.Value

  10. Perfect! I changed it to:
    ActiveSheet.Range(“B6”).Value = Sheets(“BCCwLosses”).Range(“E10”).Value
    ActiveSheet.Range(“B7”).Value = Sheets(“BCCwLosses”).Range(“E12”).Value
    ActiveSheet.Range(“B8”).Value = Sheets(“BCCwLosses”).Range(“E14”).Value
    ActiveSheet.Range(“B9”).Value = Sheets(“BCCwLosses”).Range(“E16”).Value
    ActiveSheet.Range(“B10”).Value = Sheets(“BCCwLosses”).Range(“N17”).Value
    I have several other cells to retrieve the data so this will take less rows of code.
    Thanks again for your help.

  11. Hi Again Jon,
    Now I need a negative number “N17” to be converted to a positive number when it is carried over to “B10”. This will always be a negative number.
    ActiveSheet.Range(“B10”).Value = Sheets(“BCCwLosses”).Range(“N17”).Value
    Can you help me do this in code?

  12. I need to be more specific on my previous question. The negative number that will be carried over to active worksheet on rows 10, 47, and 84 needs to converted to positive numbers.
    Thanks in advance for any help you can give me.

  13. Donna –
    Something like this:
    ActiveSheet.Range(“B10”).Value = Abs(Sheets(“BCCwLosses”).Range(“N17”).Value)

  14. Works great!
    I will change all = Sheets(“BCCwLosses”).Range(“N17″).Value to
    = Abs(Sheets(“BCCwLosses”).Range(“N17″).Value)
    Thanks again for your help.

  15. Thanks for this….. Just what I needed.

    Sub Macro1a()

    ‘ Macro4 Macro
    ‘ Macro recorded 3/16/2009 by Jon Peltier
    ‘ Fixed to use text variables for sheet and range


    Dim sSheetName As String
    Dim sDataRange As String

    sSheetName = ActiveSheet.Name
    sDataRange = Selection.Address

    Charts.Add
    ActiveChart.ChartType = xlXYScatterLinesNoMarkers
    ActiveChart.SetSourceData Source:=Sheets(sSheetName).Range(sDataRange)
    ActiveChart.Location Where:=xlLocationAsObject, Name:=sSheetName
    End Sub

  16. Thank you Jon, God of Excel, you have saved me from wandering on the internet for much longer. This solution is perfect!!

  17. Hello Again Jon,
    You helped me so much last year, I am truely grateful! I’m at another problem in VB and can’t figure out what I have wrong.
    I want to get data from one Excel unopened workbook to another opened workbook and have tried this:
    ActiveSheet.Range(“E1:AC1”).Value=”H:\[Get Hourly Loads.xls] sheets(“Load”).Range(“B12:Z12”).Value
    I’m getting a Compile Error: Expected: end of statement on (“Load”), which is the worksheet I want to get the data from
    I do not have the file Get Hourly Loads.xls open, I didn’t think it needed to be.
    I am hoping you can help me figure out what I need to do.
    Thanks Bunches!!!

  18. Donna –
    Your question is not related to this post. Following guidelines in Getting Answers For Your Excel Questions, please submit your question to a dedicated Excel forum.

  19. Hi jon,
    Can you please suggest me something to write a code that cleans or clears my clipboard.
    For example i have a vba interface from excel to powerpoint and after every 15 slides are generated i just need to clear my clipboard for better efficiency.
    Please reply to it ASAP and i tried searching out on your forum where it is appropriate but i am not able to . So can you please suggest me on this question over here only.
    Your help will be greatly appreciated.
    Thanks in advance

  20. Prateek –
    That’s another off-topic question. Follow instructions in Getting Answers For Your Excel Questions to ensure a prompt response.

  21. Jon,
    Having problems with an error in a macro attached to a button on a spreadsheet. I wish to copy the open spreadsheet and place that copy in front of the open spreadsheet and have that new spreadsheet be the new active sheet. What should the first line or lines of the macro read to do this?
    Thanks
    Ed

  22. Ed –
    It’s probably easier than you thought.
    ActiveSheet.Copy
    makes a copy of the active sheet, and since no destination is specified, it is copied into a new workbook, which becomes the active workbook.

  23. Thanks, this worked with Excel 2000 and in hindsight was obvious!

  24. Hi Jon,

    I’ve added this into my macro (sSheetName = ActiveSheet.Name) so that I can run the macro regardless of what the name is. That has worked splendidly. However, I think adding that into my macro code has somehow caused the macro to malfunction whenever I try to add new sheets. The debugger is highlighting this part of my code:

    Columns(“A:A”).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range(“A:A”).RemoveDuplicates Columns:=1, Header:=xlNo
    Range(“B1”).Select

    Should I use special naming for the new sheets? I’m kind of a newbie so any help is appreciated.

    Thanks.

  25. Cait –

    Try it this way:

    sSheetName = ActiveSheet.Name
    Sheets.Add After:=ActiveSheet
    Worksheets("sSheetName").Columns("A:A").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo
    ActiveSheet.Range("B1").Select
  26. I sent a follow up message yesterday but I don’t think it posted.

    Anyway, I did what you suggested and the debugger still keeps taking me to the same spot. Below i’ve posted the macro i’m using so maybe you can see if there are any problems you can see. Thank you for the help!

    myfile = Application.GetOpenFilename
    Workbooks.OpenText _
    Filename:=myfile, _
    Origin:=xlWindows, _
    StartRow _
    :=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote _
    , ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:= _
    False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 9), Array(2, 2), Array _
    (3, 2), Array(4, 1), Array(5, 2), Array(6, 3), Array(7, 1), Array(8, 1), Array(9, 2), Array(10, 2), Array(11, 2), Array(12, 3)), TrailingMinusNumbers _
    :=True

    sSheetName = ActiveSheet.Name

    Rows(“1:1”).Select
    Selection.Delete Shift:=xlUp

    Columns(“A:K”).Select
    Columns(“A:K”).EntireColumn.AutoFit

    Range(“H1”).Select
    Do While Selection “”
    If Selection = “P” Then
    Selection.EntireRow.Delete xlShiftUp
    Else
    Selection.Offset(1, 0).Select
    End If
    Loop

    Columns(“A:K”).Select
    ActiveWorkbook.Worksheets(sSheetName).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(sSheetName).Sort.SortFields.Add Key:=Range(“E:E” _
    ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets(sSheetName).Sort
    .SetRange Range(“A:K”)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Range(“L1”).Select
    ActiveCell.FormulaR1C1 = “Over 60 days”
    Range(“L2”).Select
    ActiveCell.FormulaR1C1 = “=IF(R2C5-RC[-1]>=60,1,0)”
    lnLastRow = Range(“A” & Rows.Count).End(xlUp).Row
    Range(“L2”).AutoFill Destination:=Range(“L2:L” & lnLastRow)
    Columns(“L:L”).Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

    Range(“L1”).Select
    Do While Selection “”
    If Selection = “0” Then
    Selection.EntireRow.Delete xlShiftUp
    Else
    Selection.Offset(1, 0).Select
    End If
    Loop

    sSheetName = ActiveSheet.Name
    Sheets.Add After:=ActiveSheet
    Worksheets(“sSheetName”).Columns(“A:A”).Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveSheet.Range(“A:A”).RemoveDuplicates Columns:=1, Header:=xlNo
    ActiveSheet.Range(“B1”).Select
    ActiveCell.FormulaR1C1 = “Cust Name”
    Range(“B2”).Select
    ActiveCell.FormulaR1C1 = “=VLOOKUP(RC[-1],IAD.AR!C[-1]:C,2,FALSE)”
    lnLastRow = Range(“A” & Rows.Count).End(xlUp).Row
    Range(“B2”).AutoFill Destination:=Range(“B2:B” & lnLastRow)
    Columns(“B:B”).EntireColumn.AutoFit
    Range(“C1”).Select
    ActiveCell.FormulaR1C1 = “Balance”
    Range(“C2”).Select
    ActiveCell.FormulaR1C1 = “=SUMIF(IAD.AR!C[-2],Sheet1!C[-2],IAD.AR!C[4])”
    lnLastRow = Range(“A” & Rows.Count).End(xlUp).Row
    Range(“C2”).AutoFill Destination:=Range(“C2:C” & lnLastRow)
    Range(“D1”).Select
    ActiveCell.FormulaR1C1 = “Over 5k”
    Range(“D2”).Select
    ActiveCell.FormulaR1C1 = “=IF(RC[-1]>=5000,1,0)”
    lnLastRow = Range(“A” & Rows.Count).End(xlUp).Row
    Range(“D2”).AutoFill Destination:=Range(“D2:D” & lnLastRow)

    Range(“D1”).Select
    Do While Selection “”
    If Selection = “0” Then
    Selection.EntireRow.Delete xlShiftUp
    Else
    Selection.Offset(1, 0).Select
    End If
    Loop

    Columns(“A:C”).Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Range(“A1”).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Columns(“A:C”).Select
    Columns(“A:C”).EntireColumn.AutoFit
    Range(“D1”).Select
    Application.CutCopyMode = False
    Range(“D1”).Select
    ActiveCell.FormulaR1C1 = “Count”
    Range(“D2”).Select
    ActiveCell.FormulaR1C1 = “=COUNTIF(IAD.AR!C[-3],Sheet2!C[-3])”
    Range(“D2”).Select
    Selection.AutoFill Destination:=Range(“D2:D100”), Type:=xlFillDefault

    Range(“E1”).Select
    ActiveCell.FormulaR1C1 = “Credit Limit”
    Range(“E2”).Select
    ActiveCell.FormulaR1C1 = “=VLOOKUP(RC[-4],IAD.AR!C[-4]:C[-2],3,FALSE)”
    Range(“E2”).Select
    Selection.AutoFill Destination:=Range(“E2:E100”), Type:=xlFillDefault
    Columns(“E:E”).EntireColumn.AutoFit
    Range(“F1”).Select
    ActiveCell.FormulaR1C1 = “Terms”
    Range(“F2”).Select
    ActiveCell.FormulaR1C1 = “=VLOOKUP(RC[-5],IAD.AR!C[-5]:C[3],9,FALSE)”
    Range(“F2”).Select
    Selection.AutoFill Destination:=Range(“F2:F100”), Type:=xlFillDefault

    Range(“D1”).Select
    Do While Selection “”
    If Selection = “0” Then
    Selection.EntireRow.Delete xlShiftUp
    Else
    Selection.Offset(1, 0).Select
    End If
    Loop

    Range(“F1”).Select
    Do While Selection “”
    If Selection = “M1” Or Selection = “M2” Then
    Selection.EntireRow.Delete xlShiftUp
    Else
    Selection.Offset(1, 0).Select
    End If
    Loop

    Columns(“A:F”).Select
    ActiveWorkbook.Worksheets(“Sheet2”).Sort.SortFields.Clear
    ActiveWorkbook.Worksheets(“Sheet2”).Sort.SortFields.Add Key:=Range( _
    “D:D”), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets(“Sheet2”).Sort
    .SetRange Range(“A:F”)
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With

    lastrow = Cells(Rows.Count, 2).End(xlUp).Row
    Cells(lastrow + 1, 2).Formula = “Totals”

    lastrow = Cells(Rows.Count, 3).End(xlUp).Row
    Cells(lastrow + 1, 3).Formula = “=sum(C1:C” & lastrow & “)”

    lastrow = Cells(Rows.Count, 4).End(xlUp).Row
    Cells(lastrow + 1, 4).Formula = “=sum(D1:D” & lastrow & “)”

    lastrow = Cells(Rows.Count, 5).End(xlUp).Row
    Cells(lastrow + 1, 5).Formula = “=sum(E1:E” & lastrow & “)”

    Columns(“C:C”).Select
    Selection.Style = “Comma”
    Columns(“E:E”).Select
    Selection.Style = “Comma”

    Rows(“1:1”).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range(“A1”).Select
    ActiveCell.FormulaR1C1 = “AR Open > 60 Days & $5k”
    Range(“A1:F1”).Select
    Range(“F1”).Activate
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = False
    End With
    Selection.Merge
    Selection.Font.Bold = True

    End Sub

  27. Cait –

    That’s a lot of code to wade through. What line produced the error, and what was the error message?

  28. The error message says, “Subscript out of range” and the debugger takes me here:

    Worksheets(“sSheetName”).Columns(“A:A”).Copy

    That’s the same spot that was causing problems before I added your macro.

  29. The error says, “Subscript out of range” and the debugger takes me here:

    Worksheets(“sSheetName”).Columns(“A:A”).Copy

    That’s the same place the debugger was taking me to before I added your macro in.

  30. Cait –

    Is the name of the sheet “sSheetName”? Or is sSheetName the string variable that contains the sheet name? If the latter, you want this:

    Worksheets(sSheetName).Columns("A:A").Copy
  31. Jon–

    I’m sorry but i’m thoroughly confused. I though your post:

    sSheetName = ActiveSheet.Name

    was saying that I could insert that into my macro and that way I would be able to run the macro no matter what the name of the file is. Is that not the case? I thought I literally just put sSheetName = ActiveSheet.Name in the code and it would run the macro no matter what the name is.

    The problem i’m having is when the macro adds sheets. The naming convention is funky.

  32. Cait –

    That line of code uses the variable sSheetName to hold the name of the sheet. so it should not be enclosed in double quotes when used later in the code. Use the syntax in my previous comment.

  33. Jon,

    I get that. I changed it and it’s still not working correctly. The problem I am having is that my macro has formulas like:

    =VLOOKUP(RC[-1],IAD.AR!C[-1]:C,2,FALSE)”

    The IAD.AR is the name of the text file and therefore the name of the first tab. However, I would like the macro to be able to run on any file regardless of the name. I don’t what to put in place of the IAD.AR so that the macro will work.

  34. I figured out what to do. I added this:

    ActiveSheet.Name = “IAD.AR”

    so that no matter what my file name/first tab name is, it automatically is changed to that so that the formulas and everything referencing to that sheet will work.

    Thank you for all your help!

  35. Changing a sheet name from a macro like this may cause problems. What if another sheet already has that name? What if it disrupts a user?

    Is that formula you posted in the macro? If that’s the sheet name, then it’s redundant in the formula, and you should just need
    =VLOOKUP(RC[-1],C[-1]:C,2,FALSE)

  36. Another sheet wouldn’t have the same name because the macro is used to format a text file. Meaning there aren’t any other sheets than what the macro adds and they are named “Sheet1”, “Sheet2”, etc.

    As far as disrupting a user, i’m not sure how that would happen. The users won’t be paying attention to that tab any way because it is the first tab and we only use the final product which is on the third tab.

    And I have to have the sheetname in the formula because my new sheets are using vlookups that are pulling data from the first tab (aka “IAD.AR”).

  37. Hai, can you help me?
    I have this macro code for sorting coloums. This macro can run when i use it on the file i made the macro, But when i run it in another workbook it can’t be run, i think the problem is the name of the sheet.. So can you help me to now ho make the code? Thank you

    Sub Macro2()
    '
    ' Macro2 Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+A
    '
       ActiveWorkbook.Worksheets("MOLDWIZARD_BOM_TEMPLATE").Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("MOLDWIZARD_BOM_TEMPLATE").Sort.SortFields.Add Key _
            :=Range("H2:H100"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        ActiveWorkbook.Worksheets("MOLDWIZARD_BOM_TEMPLATE").Sort.SortFields.Add Key _
            :=Range("D2:D100"), SortOn:=xlSortOnValues, Order:=xlAscending, _
            DataOption:=xlSortNormal
        With ActiveWorkbook.Worksheets("MOLDWIZARD_BOM_TEMPLATE").Sort
            .SetRange Range("A1:I100")
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        Range("A2").Select
        ActiveCell.FormulaR1C1 = "1"
        Range("A3").Select
        ActiveCell.FormulaR1C1 = "2"
        Range("A2:A3").Select
        Selection.AutoFill Destination:=Range("A2:A72")
        Range("A2:A72").Select
        Range("D1").Select
    End Sub
  38. Should you change

    ActiveWorkbook.Worksheets("MOLDWIZARD_BOM_TEMPLATE")

    to

    ActiveSheet
  39. Dear Jon,

    I am new to VBA and I have a question concerning a vlookup.

    I add each week 2 additional sheets to my workbook. I can make the vlookup with a macro, but it takes hard Sheet 12. I would like to have instead of sheet 12, where it refers to a specific sheet that it relates to the last sheet that is in the workbook.

    My code now is:

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],Sheet12!C[-11]:C[-4],8,0)"
    Range("O3").Select
    Selection.AutoFill Destination:=Range("O3:O122")
    Range("O3:O122").Select

    So I would like to have the Sheet12! replaced, but I have no clue on how to do this.

    I hope you can help me with this,

  40. Jeanette –

    Are all references in the formula to the active sheet? Then just try

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11],C[-11]:C[-4],8,0)"

    Otherwise, if the sheet name is in a variable called LookupSheet, try this

    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-11]," & LookupSheet & "!C[-11]:C[-4],8,0)"
  41. Hi,
    I have copied an excel sheet that contains 2 macros that work. In the sheet i copied them to they still work if i open it up on a computer that is connected to our Z drive ( where the original sheet is) but i want to open the copied sheet when i don’t have access to the drive. How do i make the copied sheet independent of the original but with the macros still working?
    Hope you can help
    Thanks
    Hannah

  42. Hannah –

    Does the sheet really contain the macros? Or are they contained in the workbook that you copied the sheet from? I suspect it’s the latter.

    Now that you’ve copied the sheet, you should also copy the macros from the original workbook to the new workbook.

    Next time, make a copy of the whole workbook, then remove the parts of it you don’t need.

  43. Thanks jon
    I think I have copied the macros as when I open up the copied sheet I can use them. But if I open the copied sheet on a device that does not have access to the original sheet then they don’t work.

    Is there a way of the two workbooks to not be linked?

    Hannah

  44. What do the macros do?

    What does “they don’t work” mean? What are the symptoms?

    Maybe there are other links besides the code.

  45. They apply filters to the sheet. They work when the original workbook is open But I need term to be independent. So I can use the copy without having the original open.

  46. I suspect the code is not in the workbook, or else it would run in the absence of the other workbook.

    Go to the VB Editor (press Alt+F11 in Excel), then expand the new and old projects in the Project pane. Can you find the code? Is it in the old workbook only, or in the new workbook as well?

  47. Fixing the Macro with String Variables Information was very helpful. I created a spreadsheet with a macro assigned to a button to extract data in Worksheet “January”. I copied the worksheet and renamed it February, March, etc. With a different worksheet active the macro kept defaulting to January until I made the changes suggested

    At the top of the macro in VB I add:

    Dim sSheetName As String
    sSheetName = ActiveSheet.Name

    Then changed the sheet name “January” to “sSheetName” where ever it appeared in the macro.
    The macro then only executed in the active sheet.

    Thanks
    Lyle

  48. Yes, that’s how to make it work.

  49. Hi , Can you tell me how to pass active sheet data to create Pivot table. I have tried below code but its not working.

    Sub Pivot()
    Dim sSheetName As String
    sSheetName = ActiveSheet.Name
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
    “sSheetName”, Version:=xlPivotTableVersion12).CreatePivotTable _
    TableDestination:=””, TableName:=”PivotTable1″, DefaultVersion _
    :=xlPivotTableVersion12
    End Sub

  50. Don’t put the variable sSheetName in quotes.

  51. Hi Jon
    Thanks for all the help.
    This is my situation.
    I have different csv files in a directory.
    These files have many data similar format rows and colums.
    I want a macro that will take the data and give me chart xy scatter.
    Up to now I am able to do the charts but when I try a new file the macro is not taking the data from the new file but it takes the data from the original file, and if you see the macro code you can see the file name in the code.
    Any suggestions ??
    Thanks for the help.

  52. Hi Mike –

    This is a bit more complicated. You need a way to ask the user what file to open and process. Fortunately Excel has GetOpenFileName, which opens a File-Open dialog. So try this code. It opens the CSV file selected by the user, makes a line chart using the entire used range of the CSV file, then saves the file as a regular Excel workbook.

    Sub OpenUserSelectedCSV()
      Dim sCurDir As String
      Dim sFileName As String
      Dim vFileName As Variant
      Dim wb As Workbook
      
      sCurDir = CurDir  '' save current directory
      ChDir "C:\Users\Jon\Documents\CSV_Folder"
      
      vFileName = Application.GetOpenFilename("CSV Files (*.csv), *.csv", , "Open a CSV File")
      If TypeName(vFileName) = "Boolean" Then
        '' GetOpenFileName returns False if user cancels
        GoTo ExitSub
      End If
      sFileName = CStr(vFileName)
    
      Set wb = Workbooks.Open(sFileName)
    
      '' do your file processing here
      ActiveSheet.Shapes.AddChart xlLineMarkers
      ActiveSheet.ChartObjects(1).Chart.SetSourceData Source:=ActiveSheet.UsedRange
      ActiveWorkbook.SaveAs Replace(ActiveWorkbook.FullName, ".csv", ".xlsx")
    
    ExitSub:
      ChDir sCurDir  '' restore current directory
    
    End Sub

    I’ve written about GetOpenFileName and GetSaveAsFileName in Get Open or Save-As Filename.

Speak Your Mind

*

Peltier Tech Charts for Excel 3.0