• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to copy the code of a report in one sheet in a workbook to create a report in another sheet

C

cyjacob

New Member
Let's say, there is a report that has already been created in a sheet in a work book using macros. I want to create another report within the same workbook in another sheet(tab) with the exact same code in VBA which was used to create the report that had already been created. I want the exact same layout of that report but the macros will change because the report serves a different set of data. For e.g. The report already created is for cargo/freight moving internationally from the US. So there is a button with a macros assigned for London-Heathrow, another button for Leipzig, Germany etc. Now I want to create another report with the exact same layout/format, for cargo moving domestically within the US. So essentially, the buttons will change to Dallas, New York, etc. Now, how do I go about editing the first report to suit my needs to create the new report? Can I just copy the macros used from the first report and edit the names alone? Or will I have to add/edit something else to make the macros for the new report work?
 
cyjacob
1) Create a copy of that sheet and edit needed layout
2) Where is that macro?
a) in module?
>>> duplicate & rename that macro and after that edit as needed...
>>> remember to edit controls of buttons too
b) with sheet?
>>> edit that macro as needed...

BUT ...
Could You edit that report (and macro)and
use it for many cases?
Why do You need 'same layout' form many times?

... it depends of Your file.
 
Last edited:
@vletm
1) Could you please elaborate on the first point? How do I copy and edit the layout that I need?
2) The macro is in the module. How do I edit the controls of buttons? I am sorry if the questions seem too basic. I am pretty much a beginner when it comes to VBA, and I have just started out on this.

https://www.dropbox.com/s/zzvd19z2t5xfkcz/Copy of Outbound Report.xlsm?dl=0
This is a link of my report. If you get the time to look into it, I'll be grateful. The first couple of tabs, Trend and Dashboard, is what I want to replicate in two different sheets(e.g. Sheet2 and Sheet3). How do I copy that, including making the buttons work, which means the macros have to shift too.
 
cyjacob ... yes!
You wrote:
I (cyjacob) am pretty much a beginner when it comes to VBA, and I have just started out on this.
Yes! I see ...
I quickly checked that file and ...
... how to write positive way? ...
I checked 'Module5':
There are many times same 'sub's!
means that those could do much less 'sub's!
... there needs at least ONE SUB!
and all of those 'sub's are also in other 'Module1'!
and seems that those are also in 'Sheet1'.
... hmm?

My opinion is
... Those should solve (=clean) before continue!
... and after that, write needed 'sub's!

Did You get any idea of my reply?


and ...
Do You really need to double those two (Trend & Dashboard) sheets?
 
I do need to double those two sheets. Because the two new sheets are for different cities. Essentially, everything remains the same except the names on all the buttons in the sheet. That I will have to edit once I am able to duplicate the trend and dashboard sheets.

So what you're telling me is that there are many duplicates of the same sub from Module 5 in Module 1 and sheet 1??
And by solving(=cleaning) you mean I should delete the duplicates from the other module and sheet?


When you say there should be atleast ONE SUB, what do you mean by that?
ONE SUB for the entire module?
 
cyjacob
Yes ...
If Buttons name is only Your reason to do doubles then ...
You can get 'Button's text eg with next:
tb = ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text
and after that, You can name Button as You need ...
and You'll know Your town!

two subs ... as there are BOS & BOSTrend, one for each case.
Those two 'cleaned' subs should clean and
use with 'green line' ... and so on...

Haven't You checked those code-sheet?
If You clean something ... then You should know what!

... this is not any 'one minute case'!
If You would like to do this as I would do it.
There should be clear idea,
what to need and do!

<Q> If You could use eg dropdown,
then You could add there 'as many towns as needed'...
have You thought that possibility at all?
 
Last edited:
cyjacob
I continue checking ...

1) You'll need four 'sub's for all of those buttons ..
Now, there are two subs per button (and many times)
... also those 'ResetTrendColors' and 'ResetDashboardColors' could do by my 'Swap_Btns'...
2) Those 'buttons' should name 'my way' ...
3) The challenge !
Those Pivot-tables has created with older version ...
I cannot use those nor 'ClearFilters' at all!
>> file size get bigger! >> cannot upload it for You <<

Samples of some codes ...
( those ...font.bold ... lines are to skip Pivots )
Code: 
Sub Do_Btn()
    On Error Resume Next
    Application.ScreenUpdating = False
   
    sh = Application.Caller
    btn = ActiveSheet.Shapes(sh).TextFrame.Characters.Text

    With Sheets("Trend")
        .Unprotect
If Not .Range("A1").Font.Bold Then _
        .PivotTables("PivotTable3").PivotFields("Responsible Station").CurrentPage = btn

        Swap_Btns "Trend", btn
        .Protect AllowUsingPivotTables:=True
    End With
   
    With Sheets("Dashboard")
        .Unprotect
If Not .Range("A1").Font.Bold Then
        .PivotTables("PivotTable2").PivotFields("Responsible Station").CurrentPage = btn
        .PivotTables("PivotTable4").PivotFields("Responsible Station").CurrentPage = btn
        .PivotTables("PivotTable5").PivotFields("Responsible Station").CurrentPage = btn
        .PivotTables("PivotTable6").PivotFields("Responsible Station").CurrentPage = btn
End If
        Swap_Btns "Dashboard", btn
        .Protect AllowUsingPivotTables:=True
    End With
    Application.ScreenUpdating = True
End Sub


Sub Swap_Btns(sh, btn)
    With Sheets(sh)
        prev_Btn = .Range("AA1")
        If prev_Btn <> Empty Then
            With .Shapes(prev_Btn)
                .ThreeD.Visible = True
                .ThreeD.BevelTopType = msoBevelCircle
                .Fill.ForeColor.RGB = RGB(252, 196, 37)
                .TextFrame.Characters.Font.Color = RGB(205, 48, 57)
            End With
        End If
        .Range("AA1") = btn
        With .Shapes(btn)
            .ThreeD.Visible = True
            .Fill.ForeColor.RGB = RGB(205, 48, 57)
            .TextFrame.Characters.Font.Color = RGB(252, 196, 37)
        End With
    End With
End Sub

... and as I wrote:
I would do those with 'drop downs' instead with 'buttons'.
> Ideas?
 
Yes, the report was created on Excel 2010, that is why the pivot tables and clear filters are not able to be used. I will use the code you have shared and see what I can do and if it is working successfully.

The drop down suggestion, as good as it sounds, having drop down in the same sheet can cause confusion. None of the new options are going to be related to the old ones.

How would you suggest using the drop down? you mean use the drop down in the old sheets right? (Trend/ dashboard)? Could you explain that idea and what you had in mind?


Lastly, if the pivot tables and clear filters cannot be used, then what do you suggest? To create new pivot tables?
 
cyjacob
Pivot-tables:
If even You cannot use those then You should make new of those!
... where is the data?

My codes:
Of course, You can test those ...
but it needs to do
... some modifications eg to all the buttons; name and macro
... to copy those in correct place
... it's possible ... but ... it's Your choice!

DropDowns:
1) buttons away
2) have a list of all towns ... and maybe states too (shorter list)
3) use drop down instead of those buttons
>>> then code would be shorter! <<<
 
cyjacob
It could work ... if You would do same (= a lot of) modifications.
and I won't use those in Module
also same part still missing
BUT
those Pivot-tables ... not!

Have You any file which works?
... including Pivots?
 
You must log in or register to reply here.
Back
Top