• 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 create macro for adding titles in excel sheet

Brunda

Member
hi chandoo,

hope your doing good.
i wanted a macro to autopopulate titles from various list of titles
when i run the macro it selecting the particular title it should create a new sheet adding those title
eg:
under product type "saree" there will be 7 different attributes like that many product type and different attributes will be present under each product type
when i select saree and attribute 1 and run the macro it should create a table having all the title present in the attribute 1 for saree
would you please help me with this by creating a macro

Thank you
 

rahulshewale1

Active Member
hi @Brunda,

See if is it Okey?

Code:
Option Explicit

Sub Print_Header()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Extraction")

ws.UsedRange.Clear

Dim s As Worksheet
Set s = ThisWorkbook.Sheets("Setup")

Dim cLrow As Long, Alrow As Long, i As Long

cLrow = s.Range("B1000").End(xlUp).Row
Alrow = s.Range("C1000").End(xlUp).Row

Dim col, k, hcol, mcol As Long
col = 1
hcol = 1
mcol = 1

For i = 2 To cLrow
    ws.Cells(1, col).Value = s.Cells(i, 2).Value
    ws.Range(Cells(1, mcol), Cells(1, mcol + 6)).Select
    Selection.Merge
    Selection.Interior.Color = vbYellow
    
        For k = 2 To Alrow
        
            ws.Cells(2, hcol).Value = s.Cells(k, 3).Value
            
            hcol = hcol + 1
        
        Next k
                  
    col = col + Alrow - 1
mcol = mcol + 7

Next i

End Sub
 

Attachments

Brunda

Member
hi @Brunda,

See if is it Okey?

Code:
Option Explicit

Sub Print_Header()

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets("Extraction")

ws.UsedRange.Clear

Dim s As Worksheet
Set s = ThisWorkbook.Sheets("Setup")

Dim cLrow As Long, Alrow As Long, i As Long

cLrow = s.Range("B1000").End(xlUp).Row
Alrow = s.Range("C1000").End(xlUp).Row

Dim col, k, hcol, mcol As Long
col = 1
hcol = 1
mcol = 1

For i = 2 To cLrow
    ws.Cells(1, col).Value = s.Cells(i, 2).Value
    ws.Range(Cells(1, mcol), Cells(1, mcol + 6)).Select
    Selection.Merge
    Selection.Interior.Color = vbYellow
   
        For k = 2 To Alrow
       
            ws.Cells(2, hcol).Value = s.Cells(k, 3).Value
           
            hcol = hcol + 1
       
        Next k
                 
    col = col + Alrow - 1
mcol = mcol + 7

Next i

End Sub
hi rahul,

Thats perfect but if i want to add different attributes is it possible in this cause we will have many attributes to change everything in the headers
 

Brunda

Member
hi rahul,

Thats perfect but if i want to add different attributes is it possible in this cause we will have many attributes to change everything in the headers
this will be constant but the next line changes for everytime cause phone will have 5 different heading and saree will have 6 different heading like that so we i choose phone heading 1 it should print heading 1 when i ask phone heading 2 it should print heading 2 like that
Section​
Associate Section​
Auditor Section​
Scoring Section​
 

rahulshewale1

Active Member
this will be constant but the next line changes for everytime cause phone will have 5 different heading and saree will have 6 different heading like that so we i choose phone heading 1 it should print heading 1 when i ask phone heading 2 it should print heading 2 like that
Section​
Associate Section​
Auditor Section​
Scoring Section​
See if is okey ?
 

Attachments

Brunda

Member
See if is okey ?
hi rahul
i have attach the file for your reference
i would be having different headings for different products so if i add everything in the list and select it only by heading name that would be feasible to use
so macro should help us to add multiple headings not the same heading with extra attribute
eg:
when i select phone heading 1
phone heading 1 should be present
when i selec saree heading 3
saree heading 3 should be present on the sheet

in the attachment i have copied the same table only but that would differ which i will add it later
 

Attachments

Brunda

Member
hi rahul
i have attach the file for your reference
i would be having different headings for different products so if i add everything in the list and select it only by heading name that would be feasible to use
so macro should help us to add multiple headings not the same heading with extra attribute
eg:
when i select phone heading 1
phone heading 1 should be present
when i selec saree heading 3
saree heading 3 should be present on the sheet

in the attachment i have copied the same table only but that would differ which i will add it later
Hi Rahul,
Can you help me with this soon please. Its much needed one.
 

rahulshewale1

Active Member
Hi Rahul,
Can you help me with this soon please. Its much needed one.

hi @Brunda ,

Still i am not cleared your requirement.

if you select Heading 1 you say that you need Their section & Attributes ?

do select Single Heading like that ? or You select multiple Heading also How much row you need to blank after on heading ( In your data there are 6 row blank after second heading output )
 

Brunda

Member
Hi Rahul,

I would give a example,

we have different products like phone,saree,pants,watch and so on under those each products there would be some 3 or 4 different headings
eg for phone heading 1 some headings (like mentioned in attachment)
phone heading 2 some headings under that
saree heading 1 someheadings under that
saree heading 2 some headings under that
so if i run the macro and select anyone option as phone heading 2 what are the headings present under that it should appear in the fresh sheet

every heading would be more are less like that same i have provided in the attachment
 

Brunda

Member
hi @Brunda ,

Still i am not cleared your requirement.

if you select Heading 1 you say that you need Their section & Attributes ?

do select Single Heading like that ? or You select multiple Heading also How much row you need to blank after on heading ( In your data there are 6 row blank after second heading output )
every product table would look in the same way or might be more or less columns.

I would give a example,

we have different products like phone,saree,pants,watch and so on under those each products there would be some 3 to 7 different headings
eg for phone heading 1 some headings (like mentioned in attachment)
phone heading 2 some headings under that
saree heading 1 someheadings under that
saree heading 2 some headings under that
so if i run the macro and select anyone option as "phone heading 2" what are the headings present under that it should appear in the fresh sheet

every heading would be more are less like that same i have provided in the attachment
 

Attachments

rahulshewale1

Active Member
every product table would look in the same way or might be more or less columns.

I would give a example,

we have different products like phone,saree,pants,watch and so on under those each products there would be some 3 to 7 different headings
eg for phone heading 1 some headings (like mentioned in attachment)
phone heading 2 some headings under that
saree heading 1 someheadings under that
saree heading 2 some headings under that
so if i run the macro and select anyone option as "phone heading 2" what are the headings present under that it should appear in the fresh sheet

every heading would be more are less like that same i have provided in the attachment

hi @Brunda ,
This attachment doesn't have section (Sheet2) ?
Section is same for every Products ?
 

Brunda

Member
hi @Brunda ,
This attachment doesn't have section (Sheet2) ?
Section is same for every Products ?
This section is always same for every headings of the products
only the second line differs
when i run the macro data should print from column D

Associate SectionAuditor SectionScoring Section
 

Brunda

Member
This section is always same for every headings of the products
only the second line differs
when i run the macro data should print from column D

Associate SectionAuditor SectionScoring Section
Hi Rahul,

Is it clear now what i asked for?
if not please let me know..its very much required thing
 
Top