New sheet with name and copied data based on macro button on each respective row

[cdalgorta]

I got the first part(putting the buttons on each row) from NateSC here in MrExcel, so shout out to him!


So I need the "Sheet #" macro buttons on J column to do the following:
- Create a new sheet with the name on column A of its respective row.
- Copy the titles on row 1(A1:K1) and copy the data on the respective row of the clicked button(Sheet 5 button copies A5:K5).
- Paste both rows on the new sheet on A1:K2 (paste as "keep source column width")
- Place this formulas on A3: =HYPERLINK("#'PYMT'!A1","HOME") -----> If possible, make it so A1 changes to the number of the row that was copied(Sheet 5 button would be =HYPERLINK("#'PYMT'!A5","HOME"). Or even better, if possible, make it so the hyperlink brings you back to the cell in the "PYMT" sheet that matches the name of cell A2 on each new sheet(the latter would be so much better because I could delete some rows on the PYMT sheet as I work on them and the hyperlink would still take me back to the correct cell on the PYMT sheet).

Thank you in advance!

Macro for buttons:

Sub Button_for_J()


'''''=========Make Buttons to create new sheets on J column==========

Dim rng As Range
Dim btn As Object
Dim myNumRows As Integer

' Based on Changing Button Caption using VBA & Assigning Macro to button using VBA

myNumRows = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

'Loop to make your buttons
For i = 2 To myNumRows
Set rng = ActiveSheet.Range("J" & i)
Set btn = ActiveSheet.Buttons.Add(1, 1, 100, 100)

With btn
'Set the button location to match the rng that was set above
.Top = rng.Top
.Left = rng.Left
.Width = rng.Width
.Height = rng.RowHeight
'Rename the button, change the caption, change the font size, set what it runs when clicked
.Name = i 'This number will be used in the next routine to know which row is affected
.Characters.Text = "Sheet " & i
.Characters.Font.Size = 10
.OnAction = "New_Sheet" ' Any time the new macro would have
End With

Next i

End Sub

 

[My Aswer Is This]

I think it would be a lot easier to use a Double click sheet column to run script

Double click on J2 and your script runs
Double click on J3 and your script runs
Etc.
If you're interested in this approach, say so.
And explain in detail what you want the script to do when you double click on any cell in column J

 

[cdalgorta]

I think it would be a lot easier to use a Double click sheet column to run script

Double click on J2 and your script runs
Double click on J3 and your script runs
Etc.
If you're interested in this approach, say so.
And explain in detail what you want the script to do when you double click on any cell in column J

Hi.
If I understood correctly, you are saying I'd just have to double click on the cell, instead of 1 click a button right? If so, I don't mind. It could be a clickable button like I asked or anything clickable really.
As for the explanation for the script, I'd like it to do the same things I said in the main post.
Thank you!

P.S. This would go into a regular module and not be a script that I have to paste directly on the sheet's code right? Because this "PYMT" sheet is always new in a new workbook.

 

[My Aswer Is This]

With my way you would enter the script like this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

So the script would be assigned to the sheet not in a Module.
Would that work? Yes No

 

[cdalgorta]

With my way you would enter the script like this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

So the script would be assigned to the sheet not in a Module.
Would that work? Yes No

It wouldn't. I'd have to copy and paste the code in every new workbook(sheet of that workbook) for every client that I'm running payments for(I have to do 1 Excel per client). That'd be hundreds of times.
Thank you though

 

[My Aswer Is This]

Well with your button script would you not need to copy that script and put it in all those sheets?

Now I could write my script once and have it work in all sheets in that workbook.

 

[cdalgorta]

Well with your button script would you not need to copy that script and put it in all those sheets?

Now I could write my script once and have it work in all sheets in that workbook.

The button gets generated with the VBA I gave above which is in my personal macro(the XL one). When I run it in any new workbook with new PYMTs, it just generates the buttons with an assigned macro(also in my Personal macro XL) that runs differently depending on which row the button is.
This is the post in which I got helped for this:

VBA to open windows folder based on date, with a button on each row

Hi, Not sure if it is possible to add macro buttons with VBA, but here goes my question: This is my sample table: What I'm trying to do is have a VBA that will create a button on each cell on column K, that when clicked, it will open a windows folder based on the date on column C For example...

www.mrexcel.com

 

[My Aswer Is This]

If you think your way will be best for you then Go with it.
Not sure what you're wanting each button to do. Reading your script is hard for me to understand.

Maybe later someone here on the forum will be able to help you.
I will keep watching and see what I can learn

 

[NateSC]

I got the first part(putting the buttons on each row) from NateSC here in MrExcel, so shout out to him!


So I need the "Sheet #" macro buttons on J column to do the following:
- Create a new sheet with the name on column A of its respective row.
- Copy the titles on row 1(A1:K1) and copy the data on the respective row of the clicked button(Sheet 5 button copies A5:K5).
- Paste both rows on the new sheet on A1:K2 (paste as "keep source column width")
- Place this formulas on A3: =HYPERLINK("#'PYMT'!A1","HOME") -----> If possible, make it so A1 changes to the number of the row that was copied(Sheet 5 button would be =HYPERLINK("#'PYMT'!A5","HOME"). Or even better, if possible, make it so the hyperlink brings you back to the cell in the "PYMT" sheet that matches the name of cell A2 on each new sheet(the latter would be so much better because I could delete some rows on the PYMT sheet as I work on them and the hyperlink would still take me back to the correct cell on the PYMT sheet).

Thank you in advance!

View attachment 70462
View attachment 70463



Macro for buttons:

Sub Button_for_J()


'''''=========Make Buttons to create new sheets on J column==========

Dim rng As Range
Dim btn As Object
Dim myNumRows As Integer

' Based on Changing Button Caption using VBA & Assigning Macro to button using VBA

myNumRows = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row

'Loop to make your buttons
For i = 2 To myNumRows
Set rng = ActiveSheet.Range("J" & i)
Set btn = ActiveSheet.Buttons.Add(1, 1, 100, 100)

With btn
'Set the button location to match the rng that was set above
.Top = rng.Top
.Left = rng.Left
.Width = rng.Width
.Height = rng.RowHeight
'Rename the button, change the caption, change the font size, set what it runs when clicked
.Name = i 'This number will be used in the next routine to know which row is affected
.Characters.Text = "Sheet " & i
.Characters.Font.Size = 10
.OnAction = "New_Sheet" ' Any time the new macro would have
End With

Next i

End Sub

I think this will do what you are looking for. Please note that if the sheet already exists. pressing the button will cause an error. Let me know if you need a check for this condition. It isn't too complicated.

-N

Sub New_Sheet()

Dim myBtn As Object
Dim HomeWS As Worksheet
Dim NewName As String
Dim myWS As Worksheet

    'Need to determine which button was clicked
    Set myBtn = ActiveSheet.Shapes(Application.Caller)
    
    Set HomeWS = ActiveSheet
    NewName = ActiveSheet.Range("A" & myBtn.Name)
    
    'Create new sheet
    Set myWS = Sheets.Add
    myWS.Move after:=HomeWS
    myWS.Name = NewName
    
    'Copy the rows across
    HomeWS.Rows(1).EntireRow.Copy myWS.Range("A1")
    HomeWS.Rows(myBtn.Name).EntireRow.Copy myWS.Range("A2")
    
    'Build hyperlink
    myWS.Range("A3").Formula = "=hyperlink(""#'PYMT'!A" & myBtn.Name & """,""Home"")"
    
End Sub

 

[NateSC]

Put this at the bottom just above End Sub to autofit all the columns:

myWS.Range("A1:K1").EntireColumn.AutoFit