• 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.

Macro to copy tab, rename based on cell, repeat for list of items

BigD

Member
Let me see how well I can explain this:

On my report tab, cell 'B1' has a drop down list containing an 'Unique ID'. The report is created using vlookup for multiple sets of data relating to the 'Unique ID'. My 'RawData' tab has over 100 'Unique IDs', and I need to create a report tab for each one. In an effort to speed up the process, I am seeking help with a macro that is connected to a button on the 'Report' tab and would execute the following:

1. copy 'report' tab
2. rename tab to cell B1 ('Unique ID')
3. Repeat process automatically for each 'Unique ID'

Any help would be appreciated. If this makes no sense, please ask I will try to be more explicit.

Thank you!!
 
Hi, BigD!
Consider uploading a sample file. It'd be easier to understand for people who might be able to help you.
Include indication of the input data, the attempted solution (formulas, code) and the manually written expected output data as example. Thanks.
Regards!
 
Hi, BigD!
Just fyi, with the "Upload a File" button at the right of "Post Reply" button, immediately below the comment text box, aka, the rectangle where you write.
Regards!
 
Hi, BigD!
Just fyi, with the "Upload a File" button at the right of "Post Reply" button, immediately below the comment text box, aka, the rectangle where you write.
Regards!

I was smart enough to find the button (miracle o_O, but it was not finding my excel files for some reason.....I assumed I didn't have enough post counts or something, so shared the DropBox link link in lieu.
 
Hi, BigD!

No, no restrictions of any kind, except those of file types.

Please clarify this:

a) you want a button in the Report form that:
1) copy the Report tab (with what name?)
2) rename tab to cell B1 (what happens with the previous B1 value?)
3) repeat the process for all Unique Ids (for each entry in the dropdown box?)

b) would it be easier and safer this procedure?
1) Report tab works as a template
2) whenever you change B1 value in Report tab it gets copied or selected if yet exists
3) B1 cell of copied tabs are protected (once you copied, you can't modify it)
4) copy process gets triggered when selecting a new value at B1 cell

Regards!
 
Hi, BigD!
A bit curious... first I should state that English isn't my native language, it's Spanish, but never neither heard nor read "in lieu" in English. En Français je connaît l'expression "au lieu de...", but I didn't know about the use of "lieu" in English.
Regards!
 
Thanks for the dialogue and assistance! See responses below in red

I think either method would work; therefore, whichever (A or B) you think would be easiest to assist me with.


a) you want a button in the Report form that:

1) copy the Report tab (with what name? I'm just needing the format copied, not the actual name as it will be renamed as text in 'B1')
2) rename tab to cell B1 (what happens with the previous B1 value? It becomes a report itself)
3) repeat the process for all Unique Ids (for each entry in the dropdown box? yes)

b) would it be easier and safer this procedure?
1) Report tab works as a template
2) whenever you change B1 value in Report tab it gets copied or selected if yet exists
3) B1 cell of copied tabs are protected (once you copied, you can't modify it)
4) copy process gets triggered when selecting a new value at B1 cell
This would work just perfectly.

another option (c):
1) 'Report' tab works as a template
2) Button located at the top right of 'RawData' tab
3) When you click 'Button', it creates a 'Report' tab for each 'UniqueID' in columns A4:A80 using the 'UniqueID' as tab name.

I'm not sure if I'm making any sense, but I'm trying hard to, hehe. It is more challenging to explain such situation than expected.
 
Hi, BigD!
A bit curious... first I should state that English isn't my native language, it's Spanish, but never neither heard nor read "in lieu" in English. En Français je connaît l'expression "au lieu de...", but I didn't know about the use of "lieu" in English.
Regards!

"in lieu" is same as "in place of". It is somewhat common in the US. Your English is excellent so no worries on not knowing this one. :)
 
Hi, BigD!

a) 3) automatically or upon changing manually B1 cell value in template Report tab?

b) compared with a), yes, I think so

c) 1) I was talking about this idea, that Report was a template
c) 2) I prefer this method, mostly because you don't have to upload/update the values at B1 cell since you have all them at raw data tab.
c) 3) you say that you'll be adding 77 report tabs to the Excel File? file size will grow significantly because of the image... and what would happen when you repeat the process a 2nd or 3rd time for newer values of Report!B1?

Regards!
 
Last edited:
Hi, BigD!
Size wouldn't be a problem, grew from 150 Kb to 500 Kb, nothing to care about.
Regards!
 
Hi, BigD!

what would happen when you repeat the process a 2nd or 3rd time for newer values of Report!B1?

Regards!

I think this is the only answer that I owe you. Great question:

Few options (I'm not sure which is easier):

1. If a person hits "report" button again, it does not duplicate any already made tabs and only create any added "UniqueID" to the column.

2. The macro runs off of two cells input which a person manually enters the range to run the report (ex. A1: Enter first cell to start report: , B1: Enter last cell to end report).

Either of these make ense?
 
Hi, BigD!

1) No duplication takes place

2) You decide, you know better than me how&when you update raw data, whether or not you print all reports...
a) It can run for all lines 4 thru 80 or 8000, it's up to you, it'd depend on raw data entries
b) It can run for a from/to schema
c) It can run on a single cell selection

If I were you, I'd be more concerned about filling all the data that's not in the raw data table; data & picture. Do you fill all data from row 12 thru 35, plus A5 cell, manually?

Regards!
 
You patience is incredible, thank you.

2) You decide, you know better than me how&when you update raw data, whether or not you print all reports... dang, I'm not sure what the best way would be because you bring up a valid point: How to handle updated reports. I think the best way is for the macro to (b) It can run for a from/to scheme.


If I were you, I'd be more concerned about filling all the data that's not in the raw data table; data & picture. Do you fill all data from row 12 thru 35, plus A5 cell, manually? Great question. Once the 'Report' tab is complete, Row 12 thru 35 plus A5 will also be vlookups via cell B1. I haven't automated these yet as I am waiting for feedback from boss regarding the current layout. I just automated (vlookup) the "Location" section for simplicity.

I will have to edit the pictures for each report individually. This stinks, but I am not sure how to do this with Excel. My goal is to create a working workbook then maybe hire someone to create a program from it.

I hope this helps.
 
Hi, BigD!

Regarding the execution time (less than a minute, 12 sec in fact) I'd choose the run all option.

If you can expand RawData table to hold all the columns required then you can easily change the VBA code assigning the proper column. Wait & see for the code.

Pictures can't be placed in the same folder as the workbook? Or in a first level subfolder? If so, column E of worksheet "PIC Form" isn't the name of the file? If not, why not adding an F column to hold the name? If that's possible, check in the attached file how it will look like.

Code:
Option Explicit

Sub BunchOfReports()
Debug.Print Now()
    '
    ' constants
    Const ksWSRawData = "Raw Data"
    Const ksRawData = "RawData"
    Const ksWSReportTemplate = "Report"
    '
    ' declarations
    Dim rngRD As Range, wsT As Worksheet, wsR As Worksheet
    Dim I As Integer, bOK As Boolean
    Dim sUId As String
    '
    ' start
    '  ranges
    Set rngRD = Worksheets(ksWSRawData).Range(ksRawData)
    Set wsT = Worksheets(ksWSReportTemplate)
    '
    ' process
    With rngRD
        For I = 2 To .Rows.Count
            ' name
            sUId = .Cells(I, 1).Value
            ' new report
            bOK = True
            On Error Resume Next
            Worksheets(sUId).Activate
            If Err.Number <> 0 Then
                bOK = False
                On Error GoTo BunchOfReports_Resume
                wsT.Copy Before:=Worksheets(wsT.Name)
                On Error Resume Next
            End If
            Set wsR = ActiveSheet
            If Not bOK Then wsR.Name = sUId
            ' data
            wsR.[B1].Value = sUId
            wsR.[A5].Value = "??"
            wsR.[B5].Value = .Cells(I, 3).Value
            wsR.[C5].Value = .Cells(I, 5).Value
            wsR.[D5].Value = .Cells(I, 7).Value
            wsR.[A8].Value = .Cells(I, 10).Value
            wsR.[B8].Value = .Cells(I, 11).Value
            wsR.[C8].Value = .Cells(I, 20).Value
            wsR.[A12].Value = 1
            wsR.[B12].Value = 10
            wsR.[C12].Value = "L"
            wsR.[D12].Value = "10-12"
            wsR.[A15].Value = "Steel"
            wsR.[B15].Value = "Light"
            wsR.[C15].Value = "E"
            wsR.[D15].Value = "Spot"
            wsR.[A18].Value = 3
            wsR.[B18].Value = 7
            wsR.OLEObjects("imgPic").Object.Picture = LoadPicture(ActiveWorkbook.Path + "\" + "Carlsberg - " + Chr(65 + (I Mod 2)) + ".jpg")
            wsR.[A35].Value = 5250
            wsR.[B35].Value = "Overcoat"
BunchOfReports_Resume:
        Next I
    End With
    '
    ' end
    '  ranges
    Set wsR = Nothing
    Set wsT = Nothing
    Set rngRD = Nothing
    '  beep
    Beep
    '
Debug.Print Now()
End Sub

Will be following this tomorrow.

Regards!
 

Attachments

  • Desktop.rar
    213.7 KB · Views: 7
Your work is AMAZING and worked PERFECTLY!!! I wish I could such. This will save me tons of time!

I had no idea I could even have it insert a picture.

Yes, I can place pictures in same folder as workbook, but prefer a sub-folder called 'Pictures".

I would need it to insert 2 images on each report.

In lieu (I had to use for you - practice makes perfect :DD) of using worksheet "PIC Form", I would rather add 2 columns in the "Raw Data" worksheet and label them "Picture 1" and "Picture 2". I am assuming I could need to put the picture file name in the each cell for the macro to insert into report?

Thanks again mate!!! :DD:)
 
Hi, BigD!
Glad to help.
Then go ahead with Pictures subfolder
Thanks for the use of in lieu, in lieu of otherwise or something alike :p
Yes, add two columns with the filenames to be retrieved from subfolder.
If you happen to add all pending columns to raw data table, then I'll send you back the updated file in a few minutes... counting from I read that message... if not on weekend, due to a problem with my religion: I have to obey the two meters precept: can't get closer to a computer unless, a) Need for speed or Netflix; b) a six-pack lets me make an offering to my deity Carlsberg, hence allowing me to work up to 5 minutes (for each six-pack).
Regards!
 
Last edited:
Back
Top