Lee Rabbit
New Member
- Joined
- Apr 30, 2020
- Messages
- 43
- Office Version
- 2010
- Platform
- Windows
Good Morning,
Hit a wall here. I want to copy a table contents (table name - ROUTE) into a copied template when creating a new sheet record.
The table ROUTE is constantly updating each week and is sat on a sheet named MASTER SHEET.
I will create a new sheet called WEEK 1, 2, 3 etc at the start of each week and will need the ROUTE table contents to be copied into and sorted alphabetically into a cells B5:B74 on the new created WEEK sheet.
Here is my code so far that creates the new sheet using a form, but I am struggling to get my head around how I copy the table contents.
Any help would be appreciated. Thanks in advance.
Lee
Hit a wall here. I want to copy a table contents (table name - ROUTE) into a copied template when creating a new sheet record.
The table ROUTE is constantly updating each week and is sat on a sheet named MASTER SHEET.
I will create a new sheet called WEEK 1, 2, 3 etc at the start of each week and will need the ROUTE table contents to be copied into and sorted alphabetically into a cells B5:B74 on the new created WEEK sheet.
Here is my code so far that creates the new sheet using a form, but I am struggling to get my head around how I copy the table contents.
Any help would be appreciated. Thanks in advance.
Lee
VBA Code:
Private Sub CommandButton1_Click()
Dim i As Long, n As Long
Dim sh As Worksheet, Nws As Worksheet
Dim Ary As Variant
Set sh = ThisWorkbook.Sheets("DATA ENTRY")
Ary = Array("B4", "C2", "O2")
For i = 1 To 2
If Me.Controls("TextBox" & i).Value = "" Then
MsgBox "PLEASE COMPLETE ALL DATA FIELDS", vbCritical
Exit Sub
End If
Next i
'CHECK FOR DUPLICATE NAME
If Application.WorksheetFunction.CountIf(sh.Range("B:B"), Me.TextBox1.Value) > 0 Then
MsgBox "THAT WEEK NUMBER ALREADY EXISTS IN THE DATABASE - PLEASE DELETE THE EXISTING RECORD IF YOU WISH TO USE THIS WEEK NUMBER", vbCritical
Exit Sub
End If
Sheets("Template").Visible = True
Sheets("Template").Copy , Sheets(Sheets.Count)
Set Nws = ActiveSheet
Nws.Name = UCase(Me.TextBox1.Value)
n = sh.Range("B" & Rows.Count).End(xlUp).Row + 1
For i = 1 To 2
sh.Range("A" & n).Offset(, i).Value = UCase(Me.Controls("Textbox" & i).Value)
Nws.Range(Ary(i - 1)).Value = UCase(Me.Controls("Textbox" & i).Value)
Me.Controls("Textbox" & i) = ""
Next i
sh.Range("A" & n).Offset(, i).Value = LCase(Me.Controls("Textbox" & i).Value)
Nws.Range(Ary(i - 1)).Value = UCase(Me.Controls("Textbox" & i).Value)
Me.Controls("Textbox" & i) = ""
MsgBox "NEW WEEK HAS BEEN ADDED", vbInformation
Sheets("Template").Visible = xlVeryHidden
Unload add_frm
End Sub