Control button to add copied row below user selection
 

'I am a VB Novice'
I have a worksheet with a bunch of job data, where I have placed a
CommandButton in the header stateing "To enter new invoice information,
please select a job and click OK". My code is simply this:

Private Sub CommandButton1_Click()

ActiveCell.Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=x1Down

End Sub

I don't know what this means, but it works fine. Problem is that I would
prefer that the row inserted have particular formating and not aquire the
formating of the row above. So, I created this formated row at the top of
the worksheet and did a Hide on it. so now I would like copy and paste that
row just below the row the user selects.

Control button to add copied row below user selection
 

Hi,

Try this addition to your code

Private Sub CommandButton1_Click()
myrow = ActiveCell.Row
ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=x1Down
Rows(1).EntireRow.Copy
Cells(myrow + 1, 1).PasteSpecial Paste:=xlPasteFormats
End Sub


Mike

"KUMPFfrog" wrote:

'I am a VB Novice'
I have a worksheet with a bunch of job data, where I have placed a
CommandButton in the header stateing "To enter new invoice information,
please select a job and click OK". My code is simply this:

Private Sub CommandButton1_Click()

ActiveCell.Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=x1Down

End Sub

I don't know what this means, but it works fine. Problem is that I would
prefer that the row inserted have particular formating and not aquire the
formating of the row above. So, I created this formated row at the top of
the worksheet and did a Hide on it. so now I would like copy and paste that
row just below the row the user selects.

Control button to add copied row below user selection
 

Typo alert.

You have:
Shift:=x1Down 'x-one-down, not x-ell-down

Mike H wrote:

Hi,

Try this addition to your code

Private Sub CommandButton1_Click()
myrow = ActiveCell.Row
ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=x1Down
Rows(1).EntireRow.Copy
Cells(myrow + 1, 1).PasteSpecial Paste:=xlPasteFormats
End Sub

Mike

"KUMPFfrog" wrote:

'I am a VB Novice'
I have a worksheet with a bunch of job data, where I have placed a
CommandButton in the header stateing "To enter new invoice information,
please select a job and click OK". My code is simply this:

Private Sub CommandButton1_Click()

ActiveCell.Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=x1Down

End Sub

I don't know what this means, but it works fine. Problem is that I would
prefer that the row inserted have particular formating and not aquire the
formating of the row above. So, I created this formated row at the top of
the worksheet and did a Hide on it. so now I would like copy and paste that
row just below the row the user selects.

--

Dave Peterson

Control button to add copied row below user selection
 

Dave,

I'm surprised that it works even with the typo. I know the switch isn't
necessary because that's the default and I only left it in because the OP had
it but why no syntax error I wonder.

Thanks for the correction.

Mike

"Dave Peterson" wrote:

Typo alert.

You have:
Shift:=x1Down 'x-one-down, not x-ell-down

Mike H wrote:

Hi,

Try this addition to your code

Private Sub CommandButton1_Click()
myrow = ActiveCell.Row
ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=x1Down
Rows(1).EntireRow.Copy
Cells(myrow + 1, 1).PasteSpecial Paste:=xlPasteFormats
End Sub

Mike

"KUMPFfrog" wrote:

'I am a VB Novice'
I have a worksheet with a bunch of job data, where I have placed a
CommandButton in the header stateing "To enter new invoice information,
please select a job and click OK". My code is simply this:

Private Sub CommandButton1_Click()

ActiveCell.Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=x1Down

End Sub

I don't know what this means, but it works fine. Problem is that I would
prefer that the row inserted have particular formating and not aquire the
formating of the row above. So, I created this formated row at the top of
the worksheet and did a Hide on it. so now I would like copy and paste that
row just below the row the user selects.

--

Dave Peterson

Control button to add copied row below user selection
 

I think you got lucky that excel didn't care.

If you had "Option Explicit" at the top of the module, then the code wouldn't
have compiled.

And the next typo error may not be as lucky. Excel may accept it, but do
something that you don't want.

Mike H wrote:

Dave,

I'm surprised that it works even with the typo. I know the switch isn't
necessary because that's the default and I only left it in because the OP had
it but why no syntax error I wonder.

Thanks for the correction.

Mike

"Dave Peterson" wrote:

Typo alert.

You have:
Shift:=x1Down 'x-one-down, not x-ell-down

Mike H wrote:

Hi,

Try this addition to your code

Private Sub CommandButton1_Click()
myrow = ActiveCell.Row
ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=x1Down
Rows(1).EntireRow.Copy
Cells(myrow + 1, 1).PasteSpecial Paste:=xlPasteFormats
End Sub

Mike

"KUMPFfrog" wrote:

'I am a VB Novice'
I have a worksheet with a bunch of job data, where I have placed a
CommandButton in the header stateing "To enter new invoice information,
please select a job and click OK". My code is simply this:

Private Sub CommandButton1_Click()

ActiveCell.Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=x1Down

End Sub

I don't know what this means, but it works fine. Problem is that I would
prefer that the row inserted have particular formating and not aquire the
formating of the row above. So, I created this formated row at the top of
the worksheet and did a Hide on it. so now I would like copy and paste that
row just below the row the user selects.

--

Dave Peterson


--

Dave Peterson

Control button to add copied row below user selection
 

Thanks Dave, in fact that's a good example of why you should use Option
Explicit

Mike

"Dave Peterson" wrote:

I think you got lucky that excel didn't care.

If you had "Option Explicit" at the top of the module, then the code wouldn't
have compiled.

And the next typo error may not be as lucky. Excel may accept it, but do
something that you don't want.

Mike H wrote:

Dave,

I'm surprised that it works even with the typo. I know the switch isn't
necessary because that's the default and I only left it in because the OP had
it but why no syntax error I wonder.

Thanks for the correction.

Mike

"Dave Peterson" wrote:

Typo alert.

You have:
Shift:=x1Down 'x-one-down, not x-ell-down

Mike H wrote:

Hi,

Try this addition to your code

Private Sub CommandButton1_Click()
myrow = ActiveCell.Row
ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=x1Down
Rows(1).EntireRow.Copy
Cells(myrow + 1, 1).PasteSpecial Paste:=xlPasteFormats
End Sub

Mike

"KUMPFfrog" wrote:

'I am a VB Novice'
I have a worksheet with a bunch of job data, where I have placed a
CommandButton in the header stateing "To enter new invoice information,
please select a job and click OK". My code is simply this:

Private Sub CommandButton1_Click()

ActiveCell.Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=x1Down

End Sub

I don't know what this means, but it works fine. Problem is that I would
prefer that the row inserted have particular formating and not aquire the
formating of the row above. So, I created this formated row at the top of
the worksheet and did a Hide on it. so now I would like copy and paste that
row just below the row the user selects.

--

Dave Peterson


--

Dave Peterson

Control button to add copied row below user selection
 

the reason i wanted to copy the row instead of paste formats is because i
have validation lists in the main row and do not want them in the new row and
I have different validation lists in the new row that are not in the main row.

"Mike H" wrote:

Hi,

Try this addition to your code

Private Sub CommandButton1_Click()
myrow = ActiveCell.Row
ActiveCell.Offset(1, 0).EntireRow.Insert Shift:=x1Down
Rows(1).EntireRow.Copy
Cells(myrow + 1, 1).PasteSpecial Paste:=xlPasteFormats
End Sub


Mike

"KUMPFfrog" wrote:

'I am a VB Novice'
I have a worksheet with a bunch of job data, where I have placed a
CommandButton in the header stateing "To enter new invoice information,
please select a job and click OK". My code is simply this:

Private Sub CommandButton1_Click()

ActiveCell.Offset(1, 0).EntireRow.Select
Selection.Insert Shift:=x1Down

End Sub

I don't know what this means, but it works fine. Problem is that I would
prefer that the row inserted have particular formating and not aquire the
formating of the row above. So, I created this formated row at the top of
the worksheet and did a Hide on it. so now I would like copy and paste that
row just below the row the user selects.