Moving a worksheet via Userform

[chirscarr]

Hi all

I'm new to VBA programming and have been able to get most of my macro to work however there are a couple of things I can't get to work. My current code is:

Sub FunctionsMenuNew()

Application.ScreenUpdating = False
Call SetGlobalVariables

ActiveWorkbook.Sheets.Add After:=Worksheets("TEMPLATE")

'Copy template
ActiveWorkbook.Sheets("TEMPLATE").Copy After:=ActiveWorkbook.Sheets("Template")
ActiveSheet.Name = "New KET"
ThisWorkbook.VBProject.VBComponents("TEMPLATE1").Name = "NewKET"
'Setting Cases/Units
Worksheets("New KET").Range("D3").Value = frmFunctionsMenuNew.UOMListBox.Value
'Copy and paste
Dim i As Integer
Dim intQty As Integer
intQty = frmFunctionsMenuNew.ProductsListBox.Value
For i = 1 To intQty
Worksheets("New KET").Rows(1).Resize(38).Copy
Worksheets("New KET").Rows(1).Offset(38 * i).PasteSpecial xlPasteAll
Next i
'moving sheet
Dim j As Integer, Sht As String
If KETListBox.Selected(i) = True Then
Sht = KETListBox.List(i)
Worksheets("New KET").Move After:=Sheets("frmFunctionsMenuNew.KETListBox.Value")
'Rename worksheet name and VBA name
ActiveSheet.Name = frmFunctionsMenuNew.KETNameTextBox.Value
ThisWorkbook.VBProject.VBComponents("NewKET").Name = frmFunctionsMenuNew.KETCodeNameTextBox.Value
'Put name and nbr in sDATA
Dim intLastRow As Integer
Worksheets("sDATA").Select
'intLastRow = Sheets.Cells("BA").End(xlUp).Row
intLastRow = Range("BA" & Rows.Count).End(xlUp).Row
Worksheets("SData").Range("BA" & intLastRow).Value = frmFunctionsMenuNew.KETNameTextBox.Value
Worksheets("SData").Range("BB" & intLastRow).Value = intQty


Application.ScreenUpdating = True
End Sub

Everything works until i come to move the sheet. On my UserForm I have a list box with a list of the worksheets in the active workbook. What I'm after doing is getting the user to select where in the workbook the new sheet is to go. At the moment it is putting it in after a sheet called Template.

It's also creating an additional blank sheet after creating a copy of TEMPLATE (New KET). Is there anyway to stop this.

The final issue I haven't figured out is on a sheet called sDATA in columns BA & BB there is a list of the sheets which have been set up and the number of products per sheet. It needs to be in the same order as the sheets themselves. I've got my code to add the name of the sheet and the number of products onto the bottom of the list but I was wondering if there is a way to sort it to match the list of worksheets.

If you could let me know where I'm going wrong on any of the above it would be really appreciated.

Cheers

Chris

[gmr4evr1]

Please upload a sample workbook with your userform and code, it will make it a lot easier for people to help you out.
Also, when posting code, please use code tags. To do this, highlight your code in the post then click the # symbol. You will have to click Edit post at the bottom right hand side of your post first.