Macro to use current wrksheet,whtevr the name

August 26, 2011 at 10:34:45
Specs: Windows XP
I would like my macro to run on whatever worksheet I select, but I get a runtime error, and it looks like the macro is specific to the sheet I used to build it. How do I get it to disregard the name of the original sheet, and run on whatever sheet I need to use it for (structurally identical to the original otherwise)? Is there a way to have it do this without actually changing the name of the sheet each time I need to run the macro? I am not experienced with VBA, but I can follow directions if there is something I can insert into the script in the debugging window.
Thank you for your assistance!

See More: Macro to use current wrksheet,whtevr the name

Report •


#1
August 26, 2011 at 14:31:37
Without seeing your code, it's hard to be specific, but ActiveSheet typically works pretty well.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 31, 2011 at 12:23:09
JuarezShelling Macro
'
' Keyboard Shortcut: Ctrl+Shift+J
'
    Range("B4").Select
    Selection.Copy
    Sheets("Temp Sheet").Select
    ActiveSheet.Paste
        Sheets("ActiveSheet").Select
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Temp Sheet").Select
    Range("B1").Select
    ActiveSheet.Paste
    Sheets("ActiveSheet").Select
    Range("B2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Temp Sheet").Select
    Range("C1").Select
    ActiveSheet.Paste
    Sheets("ActiveSheet").Select
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Temp Sheet").Select
    Range("D1").Select
    ActiveSheet.Paste
    Sheets("ActiveSheet").Select
    Range("B8:F60").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Temp Sheet").Select
    Range("F1").Select
    ActiveSheet.Paste
    Columns("A:J").Select
    Range("J1").Activate
    Selection.Columns.AutoFit
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Range("A1:D1").Select
    Selection.AutoFill Destination:=Range("A1:D60"), Type:=xlFillCopy
    Range("A1:D60").Select
    ActiveWindow.SmallScroll Down:=-54
    Range("A1").Select
End Sub


Report •

#3
August 31, 2011 at 12:30:18
Sorry, I meant to include this with the above: I have replaced the name of the worksheet on which I built the macro with "ActiveSheet", but I am still getting an error.

Report •

Related Solutions

#4
September 2, 2011 at 09:51:05
The reason no one has responded is probably because your code is really hard to read. I assume it was created by the Macro Recorder.

You should always clean up any code created by the Recorder to make it easier to follow/troubleshoot.

For example, you don't have to select an object in VBA in order to perform an action on it.

Therefore, each section of code that looks like this:

    Sheets("ActiveSheet").Select
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Temp Sheet").Select
    Range("B1").Select
    ActiveSheet.Paste

Can be replaced with something like this:

Sheets("ActiveSheet").Range("B3").Copy
    Sheets("Temp Sheet").Range("B1").Paste

In addition, the last line in this section is a waste since you've already selected a sheet by name and then selected it again once it's the ActiveSheet. What's the point?

    Sheets("Temp Sheet").Select
    Range("C1").Select
    ActiveSheet.Paste
    Sheets("ActiveSheet").Select

Clean up your code and repost it so we don't have to search through all of the bloat written by the Macro Recorder.

It might also help if you read this How-To on troubleshooting VBA Code before you start cleaning up your code.

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
September 2, 2011 at 11:00:26
Sorry, I am VERY new to using Excel and I didn't know there was 'junk' from the macro recorder. It might be easier to sum up what I am doing: I get an updated spreadsheet from Person A every day or two with 1-6 new worksheets on it. It is the same format of information each time (load number, date, bill number, PO number). What I need to do is take all of the information on each new tab and add it to one long table on a separate worksheet in order to create an End of Month PivotTable for Accounting. What I was trying to do, to speed up the process, was create a macro that would take the new info (load number, date, bill number, PO number) from each new worksheet and copy and paste it into a 'temp page' so I could copy and paste onto the pivot table reference sheet way more quickly. When I run the macro, I get an error message that takes me to the line that has the name of the worksheet that I used to create the macro highlighted. Honestly, I am not even sure of what exactly it doesn't like. If it is necessary, I will try to edit the code. And as far as the comment you left regarding the last lines of the code? I didn't know I did something wrong in the first place, so I have no idea what the point was - LOL!

I really do appreciate your assistance, thank you for your time and attention. You are REALLY good at this stuff!


Report •


Ask Question