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

C

cdalgorta

Board Regular
Joined
Jun 5, 2022
Messages
87
Office Version
  1. 365
Platform
  1. Windows
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!

1659141367048.png

1659141431560.png




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
 
Sort by date Sort by votes
NateSC said:
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

VBA Code: 
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
Click to expand...

Thank you so much! Absolutely perfect! 🧎‍♂️🧎‍♂️🧎‍♂️🧎‍♂️🧎‍♂️🧎‍♂️

What would be that check you are mentioning? Right now if I click the same button twice, it causes an error and creates a blank new sheet. Would the check make it so nothing happens even if clicking the same button twice by accident?
 
Upvote 0

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Yes. The second time you click the button it will cause an error because a sheet with that name already exists. We need to check for the name and then do something different.

The question is: What do you want it to do when the sheet already exists?
Some options:
- do nothing
- go to that sheet, but do nothing else
- create a second instance of that sheet with a slightly different name
- delete the first instance of that sheet and then recreate it from scratch
- password protect the file with a random password and close the file (not recommended)

-N
 
Upvote 0
NateSC said:
Yes. The second time you click the button it will cause an error because a sheet with that name already exists. We need to check for the name and then do something different.

The question is: What do you want it to do when the sheet already exists?
Some options:
- do nothing
- go to that sheet, but do nothing else
- create a second instance of that sheet with a slightly different name
- delete the first instance of that sheet and then recreate it from scratch
- password protect the file with a random password and close the file (not recommended)

-N
Click to expand...
I'd have said "do nothing" before, but the 2nd option to "go to that sheet, but do nothing else" sounds better. It would be like having a second "go to sheet" hyperlink like the one I have on column B. That'd nice.

By the way, I changed it a bit with very simple things in case you need to see what I have now before adding what you are mentioning.
Only changed the width of the columns
Deleted columns B, J and K
And made it so the Cell on column A of the pressed button turns yellow in the main "PYMT" sheet(to kind of remember if I had already pressed the button)
Thank you Nate


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)


'Make cell on column A yellow


Range("A" & myBtn.Name).Select
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With




'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"")"

'Fix Columns width

Columns("A:A").ColumnWidth = 9
Columns("C:C").ColumnWidth = 8
Columns("D:D").ColumnWidth = 8
Columns("E:E").ColumnWidth = 5
Columns("F:F").ColumnWidth = 40
Columns("G:G").ColumnWidth = 9
Columns("H:H").ColumnWidth = 9
Columns("I:I").ColumnWidth = 40


'Delete columns with buttons
Columns("J:K").Select
Selection.Delete Shift:=xlToLeft

'Delete "Go to sheet" column
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft

Range("A1").Select

End Sub
 
Upvote 0
Here you go. This method is the simplest form I have found to do this, but it does use more computing time than another version that uses an error trap. The difference really is not noticeable unless you are trying to do the test on thousands of tabs or have a computer that is really stressed for resources.

VBA Code: 
Sub New_Sheet()

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

Dim WorksheetExists as Boolean

    'Need to determine which button was clicked
    Set myBtn = ActiveSheet.Shapes(Application.Caller)
    
    Set HomeWS = ActiveSheet
    NewName = ActiveSheet.Range("A" & myBtn.Name)


    'Test if the worksheet name already exists
    WorksheetExists = Evaluate("ISREF('" & NewName & "'!A1)")

    If WorksheetExists Then

        Sheets(NewName).Activate
        Sheets(NewName).Range("A1").Select

    Else

        'Make cell on column A yellow
        Range("A" & myBtn.Name).Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
        End With
        
        '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"")"
        
        'Fix Columns width
        
        Columns("A:A").ColumnWidth = 9
        Columns("C:C").ColumnWidth = 8
        Columns("D:D").ColumnWidth = 8
        Columns("E:E").ColumnWidth = 5
        Columns("F:F").ColumnWidth = 40
        Columns("G:G").ColumnWidth = 9
        Columns("H:H").ColumnWidth = 9
        Columns("I:I").ColumnWidth = 40
        
        
        'Delete columns with buttons
        Columns("J:K").Select
        Selection.Delete Shift:=xlToLeft
        
        'Delete "Go to sheet" column
        Columns("B:B").Select
        Selection.Delete Shift:=xlToLeft
        
        Range("A1").Select
        
    End If

End Sub
 
Upvote 0
Solution
NateSC said:
Here you go. This method is the simplest form I have found to do this, but it does use more computing time than another version that uses an error trap. The difference really is not noticeable unless you are trying to do the test on thousands of tabs or have a computer that is really stressed for resources.

VBA Code: 
Sub New_Sheet()

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

Dim WorksheetExists as Boolean

    'Need to determine which button was clicked
    Set myBtn = ActiveSheet.Shapes(Application.Caller)
   
    Set HomeWS = ActiveSheet
    NewName = ActiveSheet.Range("A" & myBtn.Name)


    'Test if the worksheet name already exists
    WorksheetExists = Evaluate("ISREF('" & NewName & "'!A1)")

    If WorksheetExists Then

        Sheets(NewName).Activate
        Sheets(NewName).Range("A1").Select

    Else

        'Make cell on column A yellow
        Range("A" & myBtn.Name).Select
        With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
        End With
       
        '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"")"
       
        'Fix Columns width
       
        Columns("A:A").ColumnWidth = 9
        Columns("C:C").ColumnWidth = 8
        Columns("D:D").ColumnWidth = 8
        Columns("E:E").ColumnWidth = 5
        Columns("F:F").ColumnWidth = 40
        Columns("G:G").ColumnWidth = 9
        Columns("H:H").ColumnWidth = 9
        Columns("I:I").ColumnWidth = 40
       
       
        'Delete columns with buttons
        Columns("J:K").Select
        Selection.Delete Shift:=xlToLeft
       
        'Delete "Go to sheet" column
        Columns("B:B").Select
        Selection.Delete Shift:=xlToLeft
       
        Range("A1").Select
       
    End If

End Sub
Click to expand...

This is better than I ever hoped for. Thank you so much Nate!🙏

May I know if you are self-taught or used an online course to get your VBA knowledge? If it's the latter, may I know which course? I'd liked to learn/study, but I rather learn the same way an expert did than just follow the recommendations from page 1 in Google or YouTube. I have asked in other posts I've made here, but my question seems to always get ignored hahah. Since all I got for now is Google recommendations, I'm between the "The Ultimate Excel Programmer Course" from Udemy and "Excel/VBA for Creative Problem Solving, Part 1/2/3" from Coursera. But any recommendation from you would be greatly appreciate it and taken as a priority hahah. Thank you Nate.
 
Upvote 0
Unfortunately, just self-taught with macros. I did take a Fortran class in college that taught me basic programming logic. Since then, I have been using macros for ~20 years and Excel for ~30. Even doing these macros for you required lots of Google searches on this forum and several others. I will dig and dig and then try stuff until I get it to work. I usually have a basic idea of how to get there, but I often need to look up the syntax. What you listed for this second problem was perfect though. You clearly outlined the steps that needed to be followed and it made the solution fairly easy because I could solve it in small pieces.

Good luck and reach out if I can ever help again.
 
Upvote 0
NateSC said:
Unfortunately, just self-taught with macros. I did take a Fortran class in college that taught me basic programming logic. Since then, I have been using macros for ~20 years and Excel for ~30. Even doing these macros for you required lots of Google searches on this forum and several others. I will dig and dig and then try stuff until I get it to work. I usually have a basic idea of how to get there, but I often need to look up the syntax. What you listed for this second problem was perfect though. You clearly outlined the steps that needed to be followed and it made the solution fairly easy because I could solve it in small pieces.

Good luck and reach out if I can ever help again.
Click to expand...
Hopefully after a lot of studying and many many many years, I'll be able to help you with a VBA in here as well hahah. Thank you once again for all your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
How can I create a button to insert a new row and update related formulas?
Replies
0
Views
154
Arlochorim
A
J
  • Solved
Selenium VBA - loop to align new data with old data in worksheet
Replies
8
Views
349
Anthony47
A
R
  • Solved
How to run the same macro separately in all worksheets
Replies
6
Views
194
Alex Blakenburg
A
C
  • Solved
Copy data from multiple sheets into a master sheet - VBA button stuck
Replies
3
Views
182
NdNoviceHlp
NdNoviceHlp
Q
  • Solved
VBA: Dynamically Create Active X Frame and Yes / No Radio Button
2
Replies
13
Views
175
Domenic
D

Forum statistics

Threads
1,223,889
Messages
6,175,224
Members
452,620
Latest member
dsubash

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top