Copy table contents into new sheet from template

Lee Rabbit

New Member
Joined
Apr 30, 2020
Messages
30
Office Version
  1. 2010
Platform
  1. 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

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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Watch MrExcel Video

Forum statistics

Threads
1,113,909
Messages
5,544,999
Members
410,647
Latest member
LegenDSlayeR
Top