My spreadsheet is used to for sample data and the number of samples changes from run to run. I currently have a macro that inserts a row and then copies the formatting from a hidden row and pastes the formatting into the inserted row.
I have a spreadsheet with a UserForm that starts when the spreadsheet is opened and asks the user to specify how many rows they need inserted into spreadsheet. The code I have for the UserForm inserts the rows just fine but I am having a hard time copying the formatting and pasting in all the rows the UserForm inserted. My code for the UserForm is as follows;
[Option ExplicitSub CloseButton_Click()
Unload Me
End Sub
Sub InsertRowsButton_Click()
' Make sure a number is entered
If NumberofRows.Text = "" Then
MsgBox "You must enter a number."
NumberofRows.SetFocus
Exit Sub
End If
ActiveSheet.Unprotect
Range("E20").Activate
With ActiveCell
.End(xlDown).Offset(0, 0).Activate
End With
ActiveCell.Offset(1).EntireRow.Resize(NumberofRows).Insert
' This is the hidden range that contains the formatting that needs to be copied
Range("A20:V20").Copy
' This is where I think my code is going wrong because it is not selecting the whole range that was inserted
ActiveCell.Offset(1, -4).Select
ActiveSheet.Paste
Application.CutCopyMode = False
' Clear the controls for the next entry
NumberofRows.Text = ""
NumberofRows.SetFocus
Unload Me
End Sub]
Bookmarks