Populate a worksheet from an imported template.

LukeSykes Posts 1 Registration date Friday May 5, 2017 Status Member Last seen May 5, 2017 - May 5, 2017 at 07:12 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 - May 8, 2017 at 12:06 PM
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


1 response

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
May 8, 2017 at 12:06 PM
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,