Create a button to copy a template and update it from starting sheet automatically

[slzaleski]

AKBreedingProgram.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Owner	Collar #	Horse Name	Bred To	CreateSheet	LBD	14D	HB	60D	EFD
2	AK	R315	One Famous Miss	Golden Boi		3/5/2024	3/19/2024	4/2/2024	5/4/2024	2/13/2025
3	AK	R317	I Cant Help It	FDD		3/6/2024	3/20/2024	4/3/2024	5/5/2024	2/14/2025
4	AK	R324	The Stands Of Comm	Cyber Monday		2/11/2024	2/25/2024	3/10/2024	4/11/2024	1/21/2025
5	AK	OR50
6
Mares

Cell Formulas
Range	Formula
G2:G4	G2	=F2 + 14
H2:H4	H2	=F2 + 28
I2:I4	I2	=+F2 + 60
J2:J4	J2	=F2 + 345

AKBreedingProgram.xlsm
	A	B	C	D	E	F	G	H
1	Collar #	Horse Name	Bred To	Foaling Date
2
3
4	Date	L Ovary	R Ovary	Folds	Fluid	Cx	Bred	Notes
5
6
Template

AKBreedingProgram.xlsm
	A	B	C	D	E	F	G	H
1	Collar #	Horse Name	Bred To	Foaling Date
2	R315	One Famous Miss	Golden Boi
3
4	Date	L Ovary	R Ovary	Folds	Fluid	Cx	Bred	Notes
5
One Famous Miss

I'm trying to create a button in column E(didn't show it) (E2, E3, etc downward) to generate a copy of the template sheet, rename it and update some fields in it from the Mares sheet. I got that done. I do not know how many rows I will have so I must do them as needed thus reason for button. Is there a way to make the button update it's values upon a copy and paste like excel can do with regular cells. row 2 E2 button works as needed. E3 wondering if there is a way to know what line I'm on to use in the button1_click code automatically.

 

[rpaulson]

not sure this is what you want, it will tell you what row the button shape is in.

in theory you can have a button, with the same macro, any where on you sheet and it will tell you what row it is in.

Sub My_Macro()
r = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
MsgBox r
End Sub

hth,
Ross

 

[slzaleski]

Here is my button code. Any help appreciated?

Sub Button1_Click()

Dim R As Integer, C As Integer
'copy sheet from template
'Sheets("Template").Copy After:=Sheets("Mares")
Sheets("Template").Copy After:=Sheets(Sheets.Count)

ActiveSheet.Name = Sheets("Mares").Range("C2").Value
'Obtain current row in Mares sheet
R = Sheets("Mares").Shapes(Application.Caller).TopLeftCell.Row

'update New ActiveSheet row 2 column 1, 2, 3 from Mares sheet data
ActiveSheet.Range("a2").Value = Sheets("Mares").Cells(R, 1).Value
ActiveSheet.Range("b2").Value = Sheets("Mares").Range(R, 2).Value
ActiveSheet.Range("c2").Value = Sheets("Mares").Range(R, 3).Value

End Sub

 

[slzaleski]

Here is my button code. Any help appreciated?

Sub Button1_Click()

Dim R As Integer, C As Integer

R = ActiveCell.Row
'C = Sheets("Mare").Column

'copy sheet from template
Sheets("Template").Copy After:=Sheets(Sheets.Count)

ActiveSheet.Name = Sheets("Mares").Range("C2").Value
'Obtain current row in Mares sheet
'R = Sheets("Mares").Shapes(Application.Caller).TopLeftCell.Row

'update New ActiveSheet row 2 column 1, 2, 3 from Mares sheet data
ActiveSheet.Range("a2").Value = Sheets("Mares").Cells(R, 1).Value
ActiveSheet.Range("b2").Value = Sheets("Mares").Cells(R, 2).Value
ActiveSheet.Range("c2").Value = Sheets("Mares").Cells(R, 3).Value

End Sub

This seems to work except I'm getting the wrong row of the Mares sheet. I want the row the button is located on.

 

[rpaulson]

make sure the TOP Left corner of the button is in the correct cell.

 

[slzaleski]

make sure the TOP Left corner of the button is in the correct cell.

What does that mean? My buttons are in E2,E3,E4 and or on. I need the row the button is in.

 

[rpaulson]

start a new sheet. add a shape. assign macro below to the shape. run it and note the value returned for r, now move the shape all around the worksheet, note the value of R changes as you move the shape around.

Sub My_Macro()
r = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
MsgBox r
End Sub

 

[slzaleski]

start a new sheet. add a shape. assign macro below to the shape. run it and note the value returned for r, now move the shape all around the worksheet, note the value of R changes as you move the shape around.

Sub My_Macro()
r = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
MsgBox r
End Sub

Thank you I got it. Thank you Thank you. You got me thinking. I couldn't get around errors.

Sub My_Macro()
'Obtain current row in Mares sheet
R = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
Tag = Sheets("Mares").Cells(R, 2).Value
HName = Sheets("Mares").Cells(R, 3).Value
Stallion = Sheets("Mares").Cells(R, 4).Value

'MsgBox R
'MsgBox Tag
'MsgBox HName
'MsgBox Stallion

'copy sheet from template
Sheets("Template").Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = HName
'update New ActiveSheet row 2 column 1, 2, 3 from Mares sheet data
ActiveSheet.Range("a2").Value = Tag
ActiveSheet.Range("b2").Value = HName
ActiveSheet.Range("c2").Value = Stallion

End Sub