Populate a worksheet from an imported template. [Closed]

Report
Posts
1
Registration date
Friday May 5, 2017
Status
Member
Last seen
May 5, 2017
-
Posts
2720
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 19, 2021
-
Good Afternoon

Currently in my spread sheet There is a Main page, on that there is a button called 'CREATE NEW CLIENT'. When clicked it runs this macro to import a template used for each client



Sub AddTemplateSheet()
Dim sTmplt As String
Dim sht As Object

sTmplt = "G:\Shareddrivefolder\shareddrivelocation\Family Expenditure\Client Template\Family Expenditure Client Template.xltm"
Set sht = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
ActiveWorkbook.Sheets.Add Before:=Sheets("Last"), Type:=sTmplt

MsgBox "IMPORTANT!" & vbNewLine & "Message"

Dim shtC As Worksheet

Set shtC = Worksheets(Worksheets.Count - 14)

End Sub




That template is the completed with the clients details such as name, address etc.

Each template has a set of columns which start at the below cells



Date - C23

Bus - F23

Bus&Train - G23

Invoice - H23

Vouchers - M23

Cash - N23

PC2 - O23



Also in the workbook there are pages for Each month of the Financial year 'April_2017' and so on.

Is there a way, determined by what date is entered on the clients page, the data is duplicated onto the financial year month page?

The financial year columns start as below



Date - C15

Invoice - I15

Bus - J15

Bus and Train - K15

Cash - L15

PC2 - M15

Voucher - N15



Otherwise it means entering the details twice.

Also, on the clients page,

E4 - Name

E5 - Ref Number

Can that be duplicated in to columns on the financial year page

The name column starts at E15 and the Ref number column begins at H15

This needs to work for every template which is imported.

There is a first and last page where the clients page is concerned. The page before the first client page is called 'Totals' and the page after the last client page is called 'Last'

Hope you can help.

Many thanks

Luke

1 reply

Posts
2720
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 19, 2021
459
Hi Luke,

So you want to copy specific data from entered in the newly added template sheet to the corresponding month sheet.

Does the month sheet have a totals row? if so then which row is used for that?

Best regards,
Trowa

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!