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:
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.
LEM says
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!!
Jon Peltier says
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.
Sarah says
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
Daniel says
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
Jon Peltier says
Daniel – You don’t mention how you input your own range.
Donna Trahan says
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
Jon Peltier says
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 SubYou can put a Forms Menu button onto each sheet, name it “Update”, and assign it to the UpdateActiveSheet routine.
Donna Trahan says
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
Jon Peltier says
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
Donna Trahan says
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.
Donna Trahan says
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?
Donna Trahan says
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.
Jon Peltier says
Donna –
Something like this:
ActiveSheet.Range(“B10”).Value = Abs(Sheets(“BCCwLosses”).Range(“N17”).Value)
Donna Trahan says
Works great!
I will change all = Sheets(“BCCwLosses”).Range(“N17″).Value to
= Abs(Sheets(“BCCwLosses”).Range(“N17″).Value)
Thanks again for your help.
BP says
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
Joyce says
Thank you Jon, God of Excel, you have saved me from wandering on the internet for much longer. This solution is perfect!!
Donna Trahan says
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!!!
Jon Peltier says
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.
Prateek kothari says
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
Jon Peltier says
Prateek –
That’s another off-topic question. Follow instructions in Getting Answers For Your Excel Questions to ensure a prompt response.
Ed Horton says
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
Jon Peltier says
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.
Phelgan says
Thanks, this worked with Excel 2000 and in hindsight was obvious!
Cait Brown says
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.
Jon Peltier says
Cait –
Try it this way:
Cait Brown says
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
Jon Peltier says
Cait –
That’s a lot of code to wade through. What line produced the error, and what was the error message?
Cait Brown says
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.
Cait Brown says
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.
Jon Peltier says
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:
Cait Brown says
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.
Jon Peltier says
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.
Cait Brown says
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.
Cait Brown says
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!
Jon Peltier says
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)
Cait Brown says
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”).
Lila says
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
Jon Peltier says
Should you change
to
Jeanette says
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:
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,
Jon Peltier says
Jeanette –
Are all references in the formula to the active sheet? Then just try
Otherwise, if the sheet name is in a variable called
LookupSheet, try thisHannah Green says
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
Jon Peltier says
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.
Hannah green says
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
Jon Peltier says
What do the macros do?
What does “they don’t work” mean? What are the symptoms?
Maybe there are other links besides the code.
Hannah says
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.
Jon Peltier says
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?
Lyle says
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
Jon Peltier says
Yes, that’s how to make it work.
siva says
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
Jon Peltier says
Don’t put the variable sSheetName in quotes.
Mike says
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.
Jon Peltier says
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.
I’ve written about GetOpenFileName and GetSaveAsFileName in Get Open or Save-As Filename.
Henry says
Hi Jon – love your work.
I have written the following, which works only when the sheet ‘Report’ is open. I would like to place a button on another sheet ‘Daily_Input’ to run the macro, however I can’t get it to work.
Any help appreciated :)
*****
Sub EndOfDay()
Dim Empty_col As Integer
Dim Report As String
Dim Summary As String
Report = ThisWorkbook.Worksheets(“Report”).Name
Summary = ThisWorkbook.Worksheets(“Summary”).Name
‘Find empty column
Empty_col = ActiveWorkbook.Worksheets(Report).Cells(5,3).End(xlToRight).End(xlToRight).End(xlToLeft).Offset(0,1).Column
‘top line summary
ThisWorkbook.Worksheets(Report).Range(Cells(5,Empty_col),Cells(8,Empty_col)).Value=ThisWorkbook.Worksheets(Summary).Range(“d8:d11”).Value
End Sub
I get a run time error 1004: Application-defined or object-oriented error on the ‘top line summary’
Thanks
Jon Peltier says
The problem is that the problematic line of code does not fully qualify the Cells inside the parentheses: without specifying, Excel things the cells are on the active sheet, and by definition these cannot be part of a range on a different sheet (Report). The line needs to be changed to:
ThisWorkbook.Worksheets(Report).Range(ThisWorkbook.Worksheets(Report).Cells(5, Empty_col), ThisWorkbook.Worksheets(Report).Cells(8, Empty_col)).Value = ThisWorkbook.Worksheets(Summary).Range(“d8:d11”).Value
That’s a bit ugly, isn’t it? I’m going to fix up the code a bit. I don’t know why you used a string variable for each of the sheet names, when you already have define them by hard-coded names. Let’s use worksheet variables.
Henry says
Thanks a lot Jon. Looks great, works great.
scott says
So heres my situation and i know theres a macro that can be made for it but everytime i make one its only effective for the workbook name at the time. I am taking 7 different reports and combining them in to 1 workbook and then doing pivot tables on 2 of the reports and then taking 4 of the reports and 2 pivot tables and putting the data into the main sheet on different columns. When i record the macro it works for the main report sheet name but if i rerun the main report to do it again it does not work. the report name changes everytime you run it which is what i think the problem is How can i record a macro that will not care what the name of the report is and just do the steps in the macro when i record it ?
Jon Peltier says
Scott –
Without seeing the code, it’s hard to tell. Can you find the name of the main report sheet in quotes in the code? That will prevent the code from working on a different sheet next time. You will see a message for “Run-time error ‘9’: Subscript out of range”.
It may be as simple as changing
Sheets("Main Report Sheet")toActiveSheet. Or it may be more complicated: I have made some routines that pop up a dialog so the user can select which workbook or worksheet to run the code on.Maybe that’s a good idea for a new post.
Christian says
Hello,
How do I do the same with tables?
thanks!
Jon Peltier says
Christian –
I recorded this code while sorting a Table, which is a ListObject in VBA:
Kind of a mess, so I streamlined it:
This relies on the active workbook having a worksheet names Sheet2, and Sheet2 having a Table named Table1, and Table1 having a column header of Value.
So let’s find our table. We can check if the activecell is in a table, because that table will be
ActiveCell.ListObject.But if the active cell isn’t in the table, we get an error. If that happens, then let’s just use the first table in the active sheet. And first, let’s check whether the worksheet even has any tables.
So that’s the Table, what about the field we’re sorting on? That’s this piece of the recorded code:
Key:=Range("Table1[[#All],[Value]]").So the Key is a range. A ListObject has ListRows and ListColumns, and each of these has a range. We want to use the last column, and there are
lst.ListColumns.Countof them.Our range then, is
lst.ListColumns(lst.ListColumns.Count).Rangeand I’ll use a range variable called SortCol for this.Here’s my finished code:
Ramia Kiran says
Dear Sir
I want to run a macro on another sheet. e.g. a macro is placed on sheet 5 and recorded there; works perfectly fine there but my workbook has many macros and for that i need to go different sheets to update them all.
Please provide a solution to that. I tried using a one macro to run all the macros but its not working because of different sheets and each macro is based on current sheet recording only.
so either i need to change all the macros codes adding name of the worksheets before range or any other thing which you can guide.
Please help!
Regards
Ramia
Jon Peltier says
Ramia –
Is the code in a regular module? If so you need to change references to the worksheet,
Sheets("Sheet1")orWorksheets("Sheet1"), to reference the active sheet,ActiveSheet.Is the code in a worksheet code module, as an event procedure? You can copy the code and paste it into other worksheet code modules, but this is inefficient and can lead to problems if you change the code one place but not another. You can also move the code into the ThisWorkbook code module, with appropriate changes. For example, move code from
Private Sub Worksheet_Change(ByVal Target As Range)intoPrivate Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range). Then check the sheetShto make sure it is one that you want the code to affect.