Help with code to Add Rows

MadMatt

New Member
Joined
Sep 27, 2006
Messages
12
HI All, I'm trying to merge 2 command butons from the same workbook but on different sheets. The command button work fine by them self the code for them is below

Private Sub CommandButton1_Click()
Rows("11").Select
Selection.Insert Shift:=xlDown
Rows("11").Select

End Sub

Private Sub CommandButton1_Click()
Sheets("Punch Detail").Select
Application.CutCopyMode = False
Rows("5:5").Select
Selection.Copy
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
End Sub

I'm not to good at code yet but I tried this but it won't work the code is below.

Private Sub CommandButton1_Click()
Rows("11").Select
Selection.Insert Shift:=xlDown
Rows("11").Select
Sheets("Punch Detail").Select
Application.CutCopyMode = False
Rows("5:5").Select
Selection.Copy
Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
End Sub

Could somone please help me with this code as I'm not good at it yet and don't no many of the functions in excel.

Thanks Matt
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try
Code:
Private Sub CommandButton1_Click()
Me.Rows("11").Insert Shift:=xlDown
With Sheets("Punch Detail")
    .Rows("5:5").Copy .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
End With
End Sub
 

MadMatt

New Member
Joined
Sep 27, 2006
Messages
12
Hi, I have been testing the code but I'm having one problem when it adds the row to the punching detail sheet at row 5 the formula goes Incorrect. It used to refer to cell E11 but after you run the code it refers to Cell E12. I'm not sure what needs to be changed in the code any idea's?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

1) Do you have "Puching Detail" & "Puch Detail" sheets?
2) Which sheet does this code reside in?
3) Can you show us actual formula in the cell?
 

MadMatt

New Member
Joined
Sep 27, 2006
Messages
12
Hi here is the info you requested I hope it helps.

1 .The sheet where the code is called C Section

2.The C Section sheet it has the command button on it.

3. This is the formula on the Punch Detail Sheet That changes.

=IF(ISBLANK('C Section'!G13),"","A"&'C Section'!G13&"P1")& IF(ISBLANK('C Section'!H13),"",'C Section'!H13&"P1")& IF(ISBLANK('C Section'!I13),"",'C Section'!I13&"P1")& IF(ISBLANK('C Section'!J13),"",'C Section'!J13&"P1")& IF(ISBLANK('C Section'!K13),"",'C Section'!K13&"P1")& IF(ISBLANK('C Section'!L13),"",'C Section'!L13&"P1")& IF(ISBLANK('C Section'!M13),"",'C Section'!M13&"P1")& IF(ISBLANK('C Section'!N13),"",'C Section'!N13&"P1")& IF(ISBLANK('C Section'!O13),"",'C Section'!O13&"P1")& IF(ISBLANK('C Section'!P13),"",'C Section'!P13&"P1")& IF(ISBLANK('C Section'!Q13),"",'C Section'!Q13&"P1")& IF(ISBLANK('C Section'!E13),"",('C Section'!E13)&("G"))

This is what I'm trying to get it to do. When the command button on sheet C Section is used to add a row in sheet C Section sheet at row 11 Including any formula and, on sheet Punch Details to find the last row used and copy the row before it into the last row( Thats where the formula changes). Thanks for helping me!
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995

ADVERTISEMENT

If you want to generate formula with exactly same cell reference in your inserted row,
you need to change the cell reference in your formula to abosolute.

Is that what you are after?
 

MadMatt

New Member
Joined
Sep 27, 2006
Messages
12
This is what I'm after I'm having some trouble working out when the code copies row 5 my formulas get messed up. They are below

This is the formula in row 5 before running the code.
=IF(ISBLANK('C Section'!G11),"","A"&'C Section'!G11&"P1")& IF(ISBLANK('C Section'!H11),"",'C Section'!H11&"P1")& IF(ISBLANK('C Section'!I11),"",'C Section'!I11&"P1")& IF(ISBLANK('C Section'!J11),"",'C Section'!J11&"P1")& IF(ISBLANK('C Section'!K11),"",'C Section'!K11&"P1")& IF(ISBLANK('C Section'!L11),"",'C Section'!L11&"P1")& IF(ISBLANK('C Section'!M11),"",'C Section'!M11&"P1")& IF(ISBLANK('C Section'!N11),"",'C Section'!N11&"P1")& IF(ISBLANK('C Section'!O11),"",'C Section'!O11&"P1")& IF(ISBLANK('C Section'!P11),"",'C Section'!P11&"P1")& IF(ISBLANK('C Section'!Q11),"",'C Section'!Q11&"P1")& IF(ISBLANK('C Section'!E11),"",('C Section'!E11)&("G"))

This is the formula in row 5 after running the code.
=IF(ISBLANK('C Section'!G12),"","A"&'C Section'!G12&"P1")& IF(ISBLANK('C Section'!H12),"",'C Section'!H12&"P1")& IF(ISBLANK('C Section'!I12),"",'C Section'!I12&"P1")& IF(ISBLANK('C Section'!J12),"",'C Section'!J12&"P1")& IF(ISBLANK('C Section'!K12),"",'C Section'!K12&"P1")& IF(ISBLANK('C Section'!L12),"",'C Section'!L12&"P1")& IF(ISBLANK('C Section'!M12),"",'C Section'!M12&"P1")& IF(ISBLANK('C Section'!N12),"",'C Section'!N12&"P1")& IF(ISBLANK('C Section'!O12),"",'C Section'!O12&"P1")& IF(ISBLANK('C Section'!P12),"",'C Section'!P12&"P1")& IF(ISBLANK('C Section'!Q12),"",'C Section'!Q12&"P1")& IF(ISBLANK('C Section'!E12),"",('C Section'!E12)&("G"))

And the formula in row 6 is after running the code
=IF(ISBLANK('C Section'!G13),"","A"&'C Section'!G13&"P1")& IF(ISBLANK('C Section'!H13),"",'C Section'!H13&"P1")& IF(ISBLANK('C Section'!I13),"",'C Section'!I13&"P1")& IF(ISBLANK('C Section'!J13),"",'C Section'!J13&"P1")& IF(ISBLANK('C Section'!K13),"",'C Section'!K13&"P1")& IF(ISBLANK('C Section'!L13),"",'C Section'!L13&"P1")& IF(ISBLANK('C Section'!M13),"",'C Section'!M13&"P1")& IF(ISBLANK('C Section'!N13),"",'C Section'!N13&"P1")& IF(ISBLANK('C Section'!O13),"",'C Section'!O13&"P1")& IF(ISBLANK('C Section'!P13),"",'C Section'!P13&"P1")& IF(ISBLANK('C Section'!Q13),"",'C Section'!Q13&"P1")& IF(ISBLANK('C Section'!E13),"",('C Section'!E13)&("G"))

I need to make it like this

This is the formula in row 5 after running the code
=IF(ISBLANK('C Section'!G11),"","A"&'C Section'!G11&"P1")& IF(ISBLANK('C Section'!H11),"",'C Section'!H11&"P1")& IF(ISBLANK('C Section'!I11),"",'C Section'!I11&"P1")& IF(ISBLANK('C Section'!J11),"",'C Section'!J11&"P1")& IF(ISBLANK('C Section'!K11),"",'C Section'!K11&"P1")& IF(ISBLANK('C Section'!L11),"",'C Section'!L11&"P1")& IF(ISBLANK('C Section'!M11),"",'C Section'!M11&"P1")& IF(ISBLANK('C Section'!N11),"",'C Section'!N11&"P1")& IF(ISBLANK('C Section'!O11),"",'C Section'!O11&"P1")& IF(ISBLANK('C Section'!P11),"",'C Section'!P11&"P1")& IF(ISBLANK('C Section'!Q11),"",'C Section'!Q11&"P1")& IF(ISBLANK('C Section'!E11),"",('C Section'!E11)&("G"))

This is the formula in row 6 after running the code
=IF(ISBLANK('C Section'!G12),"","A"&'C Section'!G12&"P1")& IF(ISBLANK('C Section'!H12),"",'C Section'!H12&"P1")& IF(ISBLANK('C Section'!I12),"",'C Section'!I12&"P1")& IF(ISBLANK('C Section'!J12),"",'C Section'!J12&"P1")& IF(ISBLANK('C Section'!K12),"",'C Section'!K12&"P1")& IF(ISBLANK('C Section'!L12),"",'C Section'!L12&"P1")& IF(ISBLANK('C Section'!M12),"",'C Section'!M12&"P1")& IF(ISBLANK('C Section'!N12),"",'C Section'!N12&"P1")& IF(ISBLANK('C Section'!O12),"",'C Section'!O12&"P1")& IF(ISBLANK('C Section'!P12),"",'C Section'!P12&"P1")& IF(ISBLANK('C Section'!Q12),"",'C Section'!Q12&"P1")& IF(ISBLANK('C Section'!E12),"",('C Section'!E12)&("G"))


And the formula in row 7 is after running the code
=IF(ISBLANK('C Section'!G13),"","A"&'C Section'!G13&"P1")& IF(ISBLANK('C Section'!H13),"",'C Section'!H13&"P1")& IF(ISBLANK('C Section'!I13),"",'C Section'!I13&"P1")& IF(ISBLANK('C Section'!J13),"",'C Section'!J13&"P1")& IF(ISBLANK('C Section'!K13),"",'C Section'!K13&"P1")& IF(ISBLANK('C Section'!L13),"",'C Section'!L13&"P1")& IF(ISBLANK('C Section'!M13),"",'C Section'!M13&"P1")& IF(ISBLANK('C Section'!N13),"",'C Section'!N13&"P1")& IF(ISBLANK('C Section'!O13),"",'C Section'!O13&"P1")& IF(ISBLANK('C Section'!P13),"",'C Section'!P13&"P1")& IF
(ISBLANK('C Section'!Q13),"",'C Section'!Q13&"P1")& IF(ISBLANK('C Section'!E13),"",('C Section'!E13)&("G"))

and so on for each row after that if the command button is used.
Any help would be great thanks,Matt
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
That's I don't understand..

You are just copying Row5. so it should not affect its contents.....
 

MadMatt

New Member
Joined
Sep 27, 2006
Messages
12
That's what I thought I can't understand why it is changing the formula. I'm new to this forum is there any way I can post an attachement of the file incase you or someone would like to have a look at the strange behavior. I have only been learning excel for a few months now but I'm really stuck trying to work this out. Thankyou for being so helpful.
 

Forum statistics

Threads
1,136,268
Messages
5,674,734
Members
419,523
Latest member
Urnovio

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
Top