Help with code to Add Rows

[MadMatt]

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

 

[jindon]

Hi
try

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]

Hi, Thanks for your help it seems to work well I apperciate your help!

 

[MadMatt]

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]

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]

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]

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]

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]

That's I don't understand..

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

 

[MadMatt]

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.