Run macro from button on separate sheet

[jccarrenod]

Hi guys,

Brand new member and first post, this is also my first time playing with macros/vba and have searched the web for two days now and have come up empty handed; I hope you can help.

I have a workbook (Master Production Report) where I have the following tabs

Tab 1 "Control": Here I want an array of buttons that users can click on to execute the macros loaded in the rest of the workbook
Tab 2 "Charts & Reports": Here I want a series of pivot charts created based on the selection the user makes on Tab 1
Tab 3 "data_rev": Here I have all the raw data used to create the reports on Tab 2. Macros would be executed upon this data based on the selections made on Tab 1

I am still building this report so I may need more raw data sheets but in essence this is what I need. What I've done so far is create a macro that would format the raw data so that it can be used to create pivot reports, this formatting is basic (setting titles to all rows, etc) and I think it runs ok. My next step was creating a button on Tab 1 to execute this macro which would format the data on Tab 3, and here is where I got stuck and could not make it work.

My code for the macro on Tab 3 as follows:

Sub Accelerate_formatting()
'
' Accelerate_formatting Macro
' Format accelerate reports to work with master report
'


'
ActiveWindow.SmallScroll Down:=42
Range("F52:G52").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("F52").Select
Selection.Copy
Range("G52").Select
ActiveSheet.Paste
Range("F52").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Hotel ID"
Range("K52:L52").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("K52").Select
Selection.Copy
Range("L52").Select
ActiveSheet.Paste
Range("K52").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Customer ID"
Range("N52:O52").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("N52").Select
ActiveCell.FormulaR1C1 = "Interface"
Range("O52").Select
ActiveSheet.Paste
Range("R51").Select
ActiveCell.FormulaR1C1 = "CFYTD"
Range("R50").Select
ActiveCell.FormulaR1C1 = "Roomnights (Service) CFYTD"
Range("U50").Select
ActiveCell.FormulaR1C1 = "TTV CFYTD"
Range("X50").Select
ActiveCell.FormulaR1C1 = "Cost of Sales CFYTD"
Range("AA50").Select
ActiveCell.FormulaR1C1 = "Operating Margin CFYTD"
Range("S51").Select
ActiveCell.FormulaR1C1 = "LFYTD"
Range("S50").Select
ActiveCell.FormulaR1C1 = "Roomnights (Service) LFYTD"
Range("V50").Select
ActiveCell.FormulaR1C1 = "TTV LFYTD"
Range("Y50").Select
ActiveCell.FormulaR1C1 = "Cost of Sales LFYTD"
Range("AB50").Select
ActiveCell.FormulaR1C1 = "Operating Margin LFYTD"
Range("T51").Select
ActiveCell.FormulaR1C1 = "Total LFY"
Range("T50").Select
ActiveCell.FormulaR1C1 = "Roomnights (Service) Total LFY"
Range("W50").Select
ActiveCell.FormulaR1C1 = "TTV Total LFY"
Range("Z50").Select
ActiveCell.FormulaR1C1 = "Cost of Sales Total LFY"
Range("AC50").Select
ActiveCell.FormulaR1C1 = "Operating Margin Total LFY"
Range("R50:AC50").Select
Selection.Cut
Range("R52").Select
ActiveSheet.Paste
Rows("50:51").Select
Range("Q51").Activate
Selection.Delete Shift:=xlUp
Range("A50").Select
End Sub


After trying several different options this is the latest code I have for the button on Tab 1:

Sub CommandButton1_Click()
Worksheet("data_rev").Range("F52:G52").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("F52").Select
Selection.Copy
Range("G52").Select
ActiveSheet.Paste
Range("F52").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Hotel ID"
Range("K52:L52").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("K52").Select
Selection.Copy
Range("L52").Select
ActiveSheet.Paste
Range("K52").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Customer ID"
Range("N52:O52").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("N52").Select
ActiveCell.FormulaR1C1 = "Interface"
Range("O52").Select
ActiveSheet.Paste
Range("R51").Select
ActiveCell.FormulaR1C1 = "CFYTD"
Range("R50").Select
ActiveCell.FormulaR1C1 = "Roomnights (Service) CFYTD"
Range("U50").Select
ActiveCell.FormulaR1C1 = "TTV CFYTD"
Range("X50").Select
ActiveCell.FormulaR1C1 = "Cost of Sales CFYTD"
Range("AA50").Select
ActiveCell.FormulaR1C1 = "Operating Margin CFYTD"
Range("S51").Select
ActiveCell.FormulaR1C1 = "LFYTD"
Range("S50").Select
ActiveCell.FormulaR1C1 = "Roomnights (Service) LFYTD"
Range("V50").Select
ActiveCell.FormulaR1C1 = "TTV LFYTD"
Range("Y50").Select
ActiveCell.FormulaR1C1 = "Cost of Sales LFYTD"
Range("AB50").Select
ActiveCell.FormulaR1C1 = "Operating Margin LFYTD"
Range("T51").Select
ActiveCell.FormulaR1C1 = "Total LFY"
Range("T50").Select
ActiveCell.FormulaR1C1 = "Roomnights (Service) Total LFY"
Range("W50").Select
ActiveCell.FormulaR1C1 = "TTV Total LFY"
Range("Z50").Select
ActiveCell.FormulaR1C1 = "Cost of Sales Total LFY"
Range("AC50").Select
ActiveCell.FormulaR1C1 = "Operating Margin Total LFY"
Range("R50:AC50").Select
Selection.Cut
Range("R52").Select
ActiveSheet.Paste
Rows("50:51").Select
Range("Q51").Activate
Selection.Delete Shift:=xlUp
Range("A50").Select
End Sub

However it has not worked. I've tried several different options to select the sheet (workbook.sheet.select / sheet.select / worksheet.select) and nothing has worked. The latest error message I get is "Compile error: Sub or Function not defined"

I am working on Excel 2010 version 14 (32 bit) running on Windows 7 Professional (in case this is relevant).

I am doing this to try to modify a report my company created to better suit the needs of my region, however, the macro in their report is protected so I cant see the code. I will probably have many more questions as I go along so appreciate any help in advance.

Cheers!

 

[Snakehips]

jccarrenod,

Welcome to MrExcel.

Just a few points that may help.

Your compile error is down to an error in the very first line...
Should be Worksheets.

Worksheets("data_rev").Range("F52:G52").Select

If you are running your code from a button in the Control sheet...........
At the time you hit the button, the Control sheet is the Active sheet.
Within the code being called you then need to be very careful when referencing any ranges etc.
Range("Bla Bla") will assume the active sheet.
Sheets("Sheet Bla").Range("Bla Bla") refs that particular sheet which may not be the active sheet.

Even experienced coders often start with some recorded code but the problem is that the faithful recording of what you did will include every selection and scroll etc that you make. Also, certain actions will generate a heap of default settings that are not necessarily in need of coding. So recorded code can be pretty inefficient and very specific.
I have made some changes to your code that may illustrate this. It will not be perfect since it is not clear to me as to exactly what you are trying to achieve. e.g. Where you are copying and pasting. If that is because you wish to copy vale and format then copy paste is good. If you wish to only copy value then it is unnecessary.

Eg but not perfect...

Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Sheets("data_rev") 'from here on in can use ws as 'shorthand' for Sheets("data_rev")


ws.Select   ' Select the sheet if you wish to see what is happening.


'when your code is good, remove the leading apostrophe from the line below
'and that will then only refresh the screen once the routine is finished.


'Application.ScreenUpdating = False  ' *** remove the leading apostrophe


'eg now using ws....
With ws.Range("F52:G52")


'typically, do you really need all theese settings???  most are default
    .HorizontalAlignment = xlGeneral
    .VerticalAlignment = xlCenter
    .WrapText = True
    .Orientation = 0
    .AddIndent = False
    .IndentLevel = 0
    .ShrinkToFit = False
    .ReadingOrder = xlContext
    .MergeCells = True  '?? will get an alert  - can be overcome


    .UnMerge  '?????  youve just merged ???
    
    
 End With
    
ws.Range("F52").Copy ws.Range("G52")   ' copy paste in one statement
ws.Range("F52") = "Hotel ID"  ' assign value without select etc
With ws.Range("K52:L52")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.UnMerge '?????
End With




ws.Range("K52").Copy ws.Range("L52")
ws.Range("K52") = "Customer ID"


With ws.Range("N52:O52")
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
.UnMerge '????
End With
With ws
.Range("N52") = "Interface"
.Range("O52").Select  '????
.Range("R51") = "CFYTD"
.Range("R50") = "Roomnights (Service) CFYTD"
.Range("U50") = "TTV CFYTD"
.Range("X50") = "Cost of Sales CFYTD"
.Range("AA50") = "Operating Margin CFYTD"
.Range("S51") = "LFYTD"
.Range("S50") = "Roomnights (Service) LFYTD"
.Range("V50") = "TTV LFYTD"
.Range("Y50") = "Cost of Sales LFYTD"
.Range("AB50") = "Operating Margin LFYTD"
.Range("T51") = "Total LFY"
.Range("T50") = "Roomnights (Service) Total LFY"
.Range("W50") = "TTV Total LFY"
.Range("Z50") = "Cost of Sales Total LFY"
.Range("AC50") = "Operating Margin Total LFY"
.Range("R50:AC50").Cut .Range("R52")
.Rows("50:51").Select
.Range("Q51").Activate  '??????
Selection.Delete Shift:=xlUp
.Range("A50").Select
End With
End Sub

Hope fully there is something there for you to latch on to.

Here is a link to a great set of video tutorials for begining vba at WiseOwl Training LINK

 

[davesexcel]

You can start by possibly getting rid of your selects then you will be able to read the code easier.
for example:

    With Range("F52:G52")
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        '.MergeCells = True
    End With
    'Selection.UnMerge'not sure why you merge and unmerge at the same time.
    Range("F52").Copy Range("G52")
    Range("F52") = "Hotel ID"

 

[jccarrenod]

Dear Snakehips and Davesexcel,

Thank you so very much for your kind assistance!! I ran through both your comments and it all made sense, took on board everything you said and made the code as simple as I could and tried it and it worked perfectly!!! Here is what it ended up as:

Sub CommandButton1_Click()
Dim ws As Worksheet
Set ws = Sheets("data_rev")
ws.Select
Application.ScreenUpdating = False
ws.Rows("50:51").Delete Shift:=xlUp
ws.Range("F50:G50").UnMerge
ws.Range("F50") = "Hotel ID"
ws.Range("G50") = "Hotel"
ws.Range("K50:L50").UnMerge
ws.Range("K50") = "Customer ID"
ws.Range("L50") = "Customer"
ws.Range("N50:O50").UnMerge
ws.Range("N50") = "Interface"
ws.Range("O50") = "Interface Code"
ws.Range("R50") = "Roomnights (Service) CFYTD"
ws.Range("S50") = "Roomnights (Service) LFYTD"
ws.Range("T50") = "Roomnights (Service) Total LFY"
ws.Range("U50") = "TTV CFYTD"
ws.Range("V50") = "TTV LFYTD"
ws.Range("W50") = "TTV Total LFY"
ws.Range("X50") = "Cost of Sales CFYTD"
ws.Range("Y50") = "Cost of Sales LFYTD"
ws.Range("Z50") = "Cost of Sales Total LFY"
ws.Range("AA50") = "Operating Margin CFYTD"
ws.Range("AB50") = "Operating Margin LFYTD"
ws.Range("AC50") = "Operating Margin Total LFY"
End Sub

Now I have a few more questions if I may:

1. Could you please explain what this piece of code (Application.ScreenUpdating = False) means and/or does? Its the only part of the whole thing Im not clear on.

2. The macro ran perfectly and did everything exactly as I wanted only that when it finished it took me to Tab 3 where all the raw data is but I want excel to stay on Tab 1 where all the buttons and options are for the user to continue working on this.

3. Formatting the raw data is something that only happens once so is it possible to make the button gray itself out (be 'unclickable') after its been used once? If so, will this get reset if the data is erased and new data is added to the workbook? We download new data bi-weekly but each set is saved in a separate workbook so we can keep track of things every 2 weeks, so basically this whole process is repeated every 2 weeks. I need a workbook that can do all these things (including the unclickable button) and the reset itself if I make a copy so it can do it again with a new set of data. Will it work if I leave a 'MASTER' workbook saved in my computer and copy that empty, untouched one before I add new data?

4. Now that you've helped me make the button run the macro on Tab 3, could I have the same button run a macro on Tab 4 as well? I didnt mention Tab 4 on my original post but given the amount of data we have to process I may end up with 6 to 10 tabs of raw data to format. Could a single button handle the macros for all of that? And also, do I need to write the macro code for each Tab as a module or could I just do it all under the code for the button?

Now, to answer your questions about merging and unmerging; I download production reports from a company-based- system that provides us with figures on sales, revenue, etc, and this system exports the data into excel in a preset format which includes some merged cells. Since I am trying to create a pivot table I have to unmerge said cells and give them all an individual title.

Thanks again and apologies for going off with a whole lot of questions after you've just helped me with one; I got really excited when this thing worked.

Regards,

 

[davesexcel]

`Application.ScreenUpdating = False` - stops screen flickering
-You have `ws.select`, I did not have that in my code and the other code provided to you has it to show you what the code is doing you can delete that line.

 

[Snakehips]

1. Normally, Excel will update it's window to reflect changes. If code is driving those changes we are generally interested in seeing only the final result. Setting Application.ScreenUpdating = False means that Excel will not, by and large, update the screen until the code has finished running. This has the benefit of speeding up the code and as Dave states reducing some of the screen flickering that is otherwise caused by a succession of albeit fast screen updates. If you want to monitor the progress of the code when testing then you can put the apostrophe in front of that line to disable it and then if you use the F9 key from within the vb editor you can step through the code line by line.

2.Again, as Dave has stated....... so you the result of the code. Without the ws.Select the Control sheet would remain visible whilst the code processes the data sheet.

3. I would suggest that you create each new workbook as a renamed copy of the Master.
Then one option to disable the button would be to make the final line of your code .....

 CommandButton1.Visible = False

4. The answer to this very much depends upon what you are doing with the additional sheets. If they are are all being treated in the same way then you just need to point the code at each sheet in turn, probably by looping. If you are processing each sheet differently then you will require different code for each. That could be different sections of one sub or several individual subs. They could be run from the click of one button and called by code or run from several buttons. Hard for me to say without more knowledge of what you are wanting to achieve.