Copy, Rename, Move, and Clear a sheet-Checkbook

[Cdmassey987]

I am a GL Accountant and my co-workers and I use excel to track all entries across multiple entities on a daily basis (so we use a lot of different workbooks). We use two basic files for this task for each entity, a Checkbook and a Work Paper. In this post I want to focus on the checkbook.

At the end of every year, we have to create two new sheets in our check book. a reconciliation page and a register. We then have to clear all the information out of the new register and the reconciliation pages, they have done this manually for years. I started last year and I know there is an easier way to do this. What I did was break this down into steps, but need help with writing the rest of the macro(s) to complete the task. (please note I have a very limited knowledge of visual basic I am working on learning because clearly this would be useful for me to learn. the below macros I got from Excel.Tips.Net)

1) Copy a sheet rename it and add to the end of the tab list

2) Rename the Sheets
I was hoping to have the sheets named automaticaly. So for 2016 it would create the sheets and name them appropriately. my current macros (below) require sheets to be named when created which suffices, but if they can be named automatically how do I do that?

3) Clear out the old data
This is the part I'm not getting. first for the register, based on who created the checkbook the formatting may be different (SO my co-workers and I may need to standardize our checkbooks which is doable), but there also may be more or less data in the areas to be cleared based on the amount of business that entity conducted over the course of the year. Would I just clear an area I know to be well beyond what any entity would do in the course of business?

second, the Bank Rec, this sheet contains 12 bank rec forms. At the beginning of the year we usually just clear the whole new sheet and copy a form from the prior year and then clear out old data; so I was thinking, 1) I can either clear the entire sheet and have the macro copy and paste from a prior sheet and then clear out the unwanted data or 2) clear out all data but 1 form and then have it just clear out the form. but how do I get it to do this?

Quick recap and Questions:
Below are Macros to Copy, Place, and Rename necessary worksheets
Workbooks in question are not standardized
Worksheets in questions are not standardized
My co-workers do not use excel formulas (i.e. I use =SUM(a1:c1), they use =a1+b1+b2)
How can I edit my existing macro or create additional macros to:
Clear my new register?
Clear my new bank rec and copy needed form to it?

Sub NewBankRec()
Dim sName As String
Dim wks As Worksheet
Worksheets("2015 R").Copy after:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new Worksheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Loop
Set wks = Nothing

NewCheckRegister


End Sub


Sub NewCheckRegister()
Dim sName As String
Dim wks As Worksheet
Worksheets("2015").Copy after:=Sheets(Worksheets.Count)
Set wks = ActiveSheet
Do While sName <> wks.Name
sName = Application.InputBox _
(Prompt:="Enter new Worksheet name")
On Error Resume Next
wks.Name = sName
On Error GoTo 0
Loop
Set wks = Nothing
End Sub

 

[Cdmassey987]

Wasn't sure how to edit my post...but there have been some developments. I have changed the copy mechanism and assigned it to a button so it will always copy the active sheet. Removed the 1st and 2nd line after each sub and chanced the 3rd line to ActiveSheet.Copy.

I have also added code to Clear the Register Contents as well as formatting for a specific area. I am working on the Bank Rec Part. If I can I will post a copy of my completed workbook here for people to look at.