create sheet, new row with links from template - only 1 step away

Question

I am trying to create a testing template that is almost completely automated. I am close to having it finished! There are a few items that are causing me a slight headache! I am really new to VBA, my understanding is very crude, I know how to copy and place things, and do some minor modifications, etc...

I have a file with 5 sheets in it. The sheets are listed as follows: Summary, WTemplate (hidden), ATemplate (hidden), References (hidden) and SummaryTemplate (hidden). The 2 templates are required because they are 2 different types of tests. I have a button on the Summary sheet for making a copy of one of the hidden templates, it asks for the name of the new sheet (which is what I want).

The next thing I need is when these sheets are created, I want a row to be added into the Summary sheet that has all of the cell links that are desired in it. I have a row already set-up in the SummaryTemplate sheet. Each new row should be added below the last in it's series i.e. - there is a section for the W-tests and a section below that for the A-tests.

I can do this as 2 different button clicks, however they have to be done in a specific order, if not you get a bunch of crap! I am trying to put the 2 functions together. I think the sheet creation has to somehow end before the row creation can start. I also need to know how to use the input from the first question to make the changes in the replace command (right now I have the row creation asking "Which Test Number would you like to link?" - I want this to go away and just use the 1 input).

Here are the 2 buttons:

Private Sub NewWaterTest_Click()

Sheets("WTemplate").Visible = True
Sheets("WTemplate").Copy After:=Sheets("Summary")
NewPageName = InputBox("What would you like to call your new Worksheet")
ActiveWindow.ActiveSheet.Name = NewPageName
Sheets("WTemplate").Visible = False

End Sub



Private Sub NextRow_Click()

ActiveSheet.Unprotect
Sheets("SummaryTemplate").Visible = True
Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows(1).EntireRow.Select
Selection.Copy
Worksheets("Summary").Activate
ActiveCell.Offset(1).Insert
Sheets("SummaryTemplate").Visible = False
Worksheets("Summary").Activate
Sheets("Summary").Select
Cells.Replace What:="WTemplate", Replacement:=InputBox("Which Test Number would you like to link?"), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
ActiveSheet.Protect

End Sub

This is what I tried to come up with (no luck):

Private Sub NewWaterTest_Click()

ActiveSheet.Unprotect
Sheets("WTemplate").Visible = True
Sheets("WTemplate").Copy After:=Sheets("Summary")
NewPageName = InputBox("What would you like to call your new Worksheet")
Sheets("WTemplate").Visible = False
Sheets("SummaryTemplate").Visible = True
Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows(1).EntireRow.Select
Selection.Copy
Worksheets("Summary").Activate
ActiveCell.Offset(1).Insert
Sheets("SummaryTemplate").Visible = False
Worksheets("Summary").Activate
Sheets("Summary").Select
Cells.Replace What:="WTemplate", Replacement:=InputBox(NewPageName), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
ActiveSheet.Protect
ActiveWindow.ActiveSheet.Name = NewPageName

End Sub

Thanks! Matt

I don't see where to post new updates or new things I have tried so:

I thought I got it! It is now giving me a

run-time error '1004': Insert method of range class failed Debug sends me to the line: ActiveCell.Offset(1).Insert (that is the 12th row of code). it looks like this may work because it is at least finishing the first section to create the new sheet and renaming it properly. I thought that it has something to do with not finishing the new sheet name before trying to reference the new sheet.

Private Sub NewWaterTest_Click()

Sheets("WTemplate").Visible = True
Sheets("WTemplate").Copy After:=Sheets("Summary")
NewPageName = InputBox("What is the new test number?")
ActiveWindow.ActiveSheet.Name = NewPageName
Sheets("Wtemplate").Visible = False

ActiveSheet.Unprotect
Sheets("SummaryTemplate").Visible = True
Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows(1).EntireRow.Select
Selection.Copy
Worksheets("Summary").Activate
ActiveCell.Offset(1).Insert
Sheets("SummaryTemplate").Visible = False
Worksheets("Summary").Activate
Sheets("Summary").Select
Cells.Replace What:="WTemplate", Replacement:=NewPageName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
ActiveSheet.Protect

End Sub

Thanks for any help

I don't have an answer to your particular question, but do have some friendly suggestions. With this level of complexity you need to code without using `Select`and `Activate`. Do an internet search or get a decent VBA book and it will explain why. Also, get in the habit of creating variables for sheets, ranges and other objects that you are manipulating and assigning the object to the variable as soon as it's created/identified. You'll get better Intellisense and more maintainable/robust code. — Doug Glancy, Oct 18 '13 at 19:35
I will try looking at that, however, I am having to learn on my own! — Matt Hamby, Oct 21 '13 at 20:00

score 0 · Answer 1 · answered Oct 19 '13 at 09:39

The reason you are seeing the 2nd InputBox is in the Replacement:=InputBox(NewPageName),because that's calling an InputBox with a prompt equal to your previous selection.

Sheets("Summary").Select
Cells.Replace What:="WTemplate", Replacement:=InputBox(NewPageName), LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False

If you change Replacement:=InputBox(NewPageName),to Replacement:=NewPageName, I think you'll find it works,

That way it uses the contents of NewPageName that you set when the 1st InputBox was invoked.

PS: stepping through code using F8 is a good way to find where your unexpected results (i.e. the second input box) are happening.

I tried your suggestion, but with no luck, I think the problem is coming from there not being a sheet with that name until the macro ends. I noticed this by selecting the "new row" button before I created the sheet to which I gave it the name to refer to. — Matt Hamby, Oct 21 '13 at 19:04

score 0 · Answer 2 · answered Oct 21 '13 at 20:09

YES!!!!!!!!!!!!

it works!

Private Sub NewWaterTest_Click()

Sheets("WTemplate").Visible = True
Sheets("WTemplate").Copy After:=Sheets("Summary")
NewPageName = InputBox("What is the new test number?")
ActiveWindow.ActiveSheet.Name = NewPageName
Sheets("Wtemplate").Visible = False

Worksheets("Summary").Activate
ActiveSheet.Unprotect
Sheets("SummaryTemplate").Visible = True
Worksheets("SummaryTemplate").Activate
ActiveSheet.Rows(1).EntireRow.Select
Selection.Copy
Worksheets("Summary").Activate
ActiveCell.Offset(1).Insert
Sheets("SummaryTemplate").Visible = False
Worksheets("Summary").Activate
Sheets("Summary").Select
Cells.Replace What:="WTemplate", Replacement:=NewPageName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
ReplaceFormat:=False
ActiveSheet.Protect

Worksheets(NewPageName).Activate

End Sub

create sheet, new row with links from template - only 1 step away

2 Answers2