Use Access Database to auto create Excel template

MARK1111

New Member
Joined
Jan 23, 2019
Messages
12
Hi All,

I've come up against an issue. I'm sure it possible, only I don't have the knowledge.
I currently use Excel to keep track of what customers owe and when they pay. In it's basic form, it consists of two boxes for every day of the year. One records what they owe for any particular day and the other box is used to record when they pay a sum of money. There is a bit more to it but that summarises what it is used for. At the minute, I have a blank template and each time we get a new customer, I have to populate the customers name and then create a link to that particular sheet and paste it in to a summary sheet. As has happened these last few weeks, I've re-designed it because it is running slowly. I had some macro buttons that replicated themselves over and over, meaning there were hundreds pasted on top of each other on each page. I assume that this was the cause of the slow down.
The issue is, each time I tweak it, I have to enter 150+ customers names and the duplicating, pasting etc becomes repetitive and gives the opportunity for mistakes to be made.
What I am looking for is a way of creating a database of customers (it only needs to consist of the customers name) and a way of having it automatically duplicate the template spreadsheet and naming it as per the name on the customer database. Then, being able to access / search it either from within Access or Excel.
Does this sound possible and is there a reasonably simple way of doing it?
I'd appreciate your feedback.
Thanks,

Mark
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,845
You want a database to copy a workbook template as a new workbook or new sheet in an existing workbook, based on a table of names? Then you are going to continue using all these sheets/books to manage the payments? Not sure, but it's likely the repetition of data and all the associated formulae and lookups that you must have that's slowing down Excel.

Why not just move the whole process into Access? What you seem to have is a process that fits a db that you're trying to make work in a spreadsheet.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,667
Office Version
2013
Platform
Windows
Not sure what exactly could be said but if you did put it in Access, such as suggested by Micron, then it would be possible to output excel sheets as needed to satisfy clients or other users. Even a re-designed excel model could also create its own excel output as well. Whatever, this current setup does sound like it's in need of repair.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,786
Messages
5,482,923
Members
407,368
Latest member
FunkyFriedChicken

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top