VBA Copy/Paste/Delete multiple times into next available column

[tiggerru]

Hi all!
A little stuck but I'll give as much info as I can.

Version: Excel 2016

I have a range of data from A1:N44 that need to be copied into the next columns thus creating a new page. I've been able to get it to work creating multiple pages but can't for the life of me figure out deleting the pages. It will delete only what was recently pasted ie. if I create 3 pages, it will only delete page 3 and can't delete page 2.

Bugs:

• page boundaries are off every time I add or delete a page; they should be confined to 14 columns and 44 rows.
• I have a cell that states the page number ("Page 1 of 4") but I can't think of a way to update this as pages are added or deleted. Multiple cells, Page | Current page | Total Pages ?

Any help is appreciated. Thanks!

Sub NewPage_Click()
    If MsgBox("This will CREATE a new page. Are you sure?", vbYesNo) = vbNo Then Exit Sub
        
    Dim WS As Worksheet
    Dim LastRow As Long, LastCol As Long
     
    Application.ScreenUpdating = False
     
    Set WS = Sheets("Sheet1")
    LastRow = WS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    LastCol = WS.Cells.Find("*", SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column + 1
               
    'Copy/Paste Settings
    WS.Range("A1:N" & LastRow).Copy
    WS.Cells(1, LastCol).PasteSpecial Paste:=xlPasteAll
    WS.Cells(1, LastCol).PasteSpecial Paste:=xlPasteColumnWidths
    
    Selection.Name = "PasteRange"
    Application.CutCopyMode = 0
    Application.ScreenUpdating = True
    
End Sub


Sub DeletePage_Click()
    
    If MsgBox("This will DELETE a page. Are you sure?", vbYesNo) = vbNo Then Exit Sub
    
    Dim WS As Worksheet
    Application.ScreenUpdating = False
     
    Set WS = Sheets("Sheet1")
    On Error Resume Next
    WS.Range("PasteRange").EntireColumn.Delete
    Application.ScreenUpdating = True
    
End Sub

 

[tiggerru]

Anyone have any direction?