Need help with VBA code to copy cells for certain values onto new sheet as headings

[ralphiecifaretto]

Hi all,

I've been searching online but only found some youtube videos to copy rows from one sheet to another, rather than what I am trying to achieve.

So I have a table like below:

table.PNG

and I want to setup a macro/code that will create headings on a new sheet using cells from column A but only if they have a "y" in column B

so for example, using the above y's from the above table, the macro should create headings on new sheet like below for those rows with y's:

'y' headings.PNG

I hope that makes sense, and someone can help,

Many thanks,

Ralphie



Test file attached below with table and example tab of desired result

Book1.xlsx

[Pepe Le Mokko]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[TMS]

Try:

Please Login or Register  to view this content.

[ralphiecifaretto]

Welcome to the forum

Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

thank you, I have attached a copy of test file now (Book1.xlsx) with data table and tab with example of desired result

[TMS]

For your file

Please Login or Register  to view this content.

[ralphiecifaretto]

For your file

Please Login or Register  to view this content.

thank you, this seems to work and also updates when the y's are updated

many thanks for your help

[TMS]

You're welcome.



Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

[Marc L]

According to your attachment a VBA demonstration for starters :

Sub Demo1()
         Sheets.Add , Sheets(Sheets.Count)
         [A1].RowHeight = 25
    With Sheet3.[A1].CurrentRegion
        .AutoFilter 2, "Y"
        .Columns(1).Offset(1).Copy
         [A1].PasteSpecial , , True, True
        .AutoFilter
    End With
        ActiveSheet.UsedRange.Resize(6).Borders.Weight = 2
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[ralphiecifaretto]

According to your attachment a VBA demonstration for starters :

Sub Demo1()
         Sheets.Add , Sheets(Sheets.Count)
         [A1].RowHeight = 25
    With Sheet3.[A1].CurrentRegion
        .AutoFilter 2, "Y"
        .Columns(1).Offset(1).Copy
         [A1].PasteSpecial , , True, True
        .AutoFilter
    End With
        ActiveSheet.UsedRange.Resize(6).Borders.Weight = 2
End Sub 

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

thank you, this code work's as well, and now I have two options to use

many thanks for your help as well