Moving closing balances to opening balance on a new sheet using VBA

[Derek Armitage]

Moving Closing Balance to Opening balances.xlsx
	B
3
Sheet1

Hello

I'm looking to move closing balances to opening balance on a new sheet using VBA. See mini sheet at top.

Goal:
1) Copy a new sheet outlined in the VBA code

'Copy Sheet
ThisWorkbook.Sheets(1).Copy after:=Sheets(1)
Set ws = ThisWorkbook.ActiveSheet

'Rename Sheet
ws.Name = wsname
ws.Move Before:=ThisWorkbook.Sheets(1)

2) On the new sheet
a) Find the "closing cash balances" in F (column is static and will not change). However the "closing cash balance" row numbers will be dynamic (depending on the number of transactions).
b) Take the "closing cash balances" and put them as "opening cash balances" in F7 and F8. The "opening cash balances" will be static and will not change.

3) On the new sheet
a) add one year in the date cells in B7 and B8. The cell references will also be static as they are associated with the "opening cash balances"
b) add one year in the date cells in column B with respect to the "closing cash balance" which will be dynamic.
c) is it possible to take into account leap years. Not a big deal if it is not possible.
Thanks
Derek