Control button to add additional worksheets

I'm trying to figure out a way to automate the duplication of a worksheet to capture data, e.g., creating a button on a worksheet that captures information about importing goods from country A. If the user also imported from countries B, C and D etc., after completing the form for country A, the button would ask if the user imported from another country. If yes, then a copy of the worksheet would be inserted to the right of the existing one (with country A data), clear (delete) with data so that it was blank and ready to receive data entry for imports from country B. The worksheet for country B would also need to have this button in case the user also needed to report imports for country C and so on.

If the response is NO (i.e., no additional countries), the cursor (focus) would move to the first unlocked cell of the next question.

I hope I've been cl ear enough and appreciate any and all help with is one.

Simon

I think I know what you are looking for, I have a macro below, to create the sheet.

It is necessary first to create a blank sheet which I have called "0000" and this will be the last worksheet and used as a basis for creating the new sheets with whatever formatting, data etc is needed.

The macro will check to see if the sheet name is already present and select that sheet and quit if it is, or create a new sheet if it is not.

Sub create_page()

Dim CN As String, response, a as integer, wcon As Integer, g As Integer

Application.ScreenUpdating = False

Let CN = ?? ‘origin of country name
Let wcon = Worksheets.Count
Let a = 3 ‘sheet before which new data sheet to be placed

For g = 3 To wcon
If Sheets(g).Name = CN Then
response = MsgBox(prompt:=”This country already has a data sheet”, Buttons:=vbOKOnly)
sheets(g).select
End
End If
Next g

Sheets("0000").Visible = True
Sheets("0000").Select
Sheets("0000").Copy Before:=Sheets(a)
Sheets("0000 (2)").Select
Sheets("0000 (2)").Name = CN
Sheets("0000").Visible = xlVeryHidden

Sheets(CN).Select  ‘Place any code here relating to data entry

Application.ScreenUpdating = True

End Sub

Hope it is of some use

Regards


Jeff