Excel spreedsheet

Posted on 2014-09-08
Last Modified: 2014-09-10
I have an excel spreadsheet that I have saved as a template. I input people's data in to the worksheet and save it as that persons name. I have a macro on the template that I need to run at the end of each month but it will not work after I save the worksheet under the persons name. how can I get around this problem. Any help would be great. I will attach the macro if that will help. Thank you
Question by:jodyreid
  • 4
  • 3
LVL 13

Expert Comment

by:Russell Fox
ID: 40310743
Make sure you are saving the renamed workbook as a macro-enabled workbook, xlsm, rather than the standard xlsx.
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40310839

looks like you run Excel 2003 or earlier, so the only file extension you need is .xls, both for macro and non-macro files.

The code you posted names several files verbatim.  It looks like it has been created by the macro recorder. It  opens a file called

T:\Occupational Therapy\Therapy.xls

and then assumes that another file called

John-Smith 03-08-1944.xls

is already open in another window.  Finally, the file is saved in

T:\Occupational Therapy\Export Files\140544.xls

This macro will not magically adjust if the file name is renamed. If you need a dynamic solution, you need to make the code dynamic.  

Can you explain in words what the macro is supposed to do? Then we can help construct it to work with other files.

cheers, teylyn

Author Comment

ID: 40311736
I have a template that every month we save for every patient in the office under the name of that person. Every day there is data added to that worksheet. At the end of the month i copy the end totals and paste them to a new work sheet and save that new work sheet under the number of the patient. I have the macro open both worksheets and copy the data from one to the other i have it copy the patients number and save the second worksheet under that number. The macro works great for the first patient but when i change the patient it stops at the line John-Smith 03-08-1944.xls. Is there a way to have it runn from all patients every when the worksheets name changes all the time.
Thank you for your time
Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.


Author Comment

ID: 40312181
Just checking to see if anyone can help me with this problem. I am not well versed with writing macros or any VBA stuff. Thank you for you time.

Author Comment

ID: 40312363
This is my workbook “Template.xlsx”. I need to open Workbook “Data.xlsx” and rename or save it at the end with the person number in cell G3. I then need to copy data from cell H6,H7,H9,H10,H12 and H13 and paste it to cells J5 to j11 in new workbook “451535”. The data in H6 on is in this format “=SUM(C6:G6)” and needs to be changed to the value of the sum so it can be copied over. I am going to be using the 2 workbooks about 60 times each month for the 60 people we will be working with so the macro needs to names and data each time. Is this possible. Thank you.
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 40313206
What version of Excel are you using? The code you posted above is for 2003 or earlier, but the files you post are 2007 or later. Please clarify. This is important.

What is the structure of the Data.xlsx file?

If you use a template, why don't you use a real template, i.e. an xltx or xltm file, which will create a new file when it is opened?

What are you doing with over 60 workbooks created each month? That looks like a very inefficient way of handling data. Have you considered using a database approach instead of individual files with identical tables?
LVL 50

Accepted Solution

Ingeborg Hawighorst earned 500 total points
ID: 40313556
Here is some code I came up with. See attached file with the code in the code module.  

You need to change the file paths in the code to reflect your situation.  You can save the template file with a different name, just remember to save it as a macro-enabled file with the xlsm extension. When you run the macro, the file name of the file that runs the macro does not matter.

Sub SavePatientFile()
Dim InputPath As String
Dim InputFile As String
Dim OutputPath As String
Dim OutputFile As String
Dim PatientNumber As String
Dim SourceFile As Workbook
Dim SourceSheet As Worksheet
Dim TargetFile As Workbook
Dim TargetSheet As Worksheet

Set SourceFile = ThisWorkbook  ' This is the file that the macro runs from
Set SourceSheet = SourceFile.Worksheets("Sheet1") ' this is the worksheet with the patient data that we want to copy

InputPath = "T:\dump\input\" ' this is the folder with the Data file.                   **** CHANGE THIS TO REFLECT YOUR PATH ****
InputFile = "Data.xlsx" ' this is the file name of the Data file.                       **** CHANGE THIS TO YOUR DATA FILE NAME ****
PatientNumber = SourceSheet.Range("E3").Value ' the patient number in cell E3 of the current file
OutputPath = "T:\dump\output\" ' this is the folder where the new file must be saved    **** CHANGE THIS TO REFLECT YOUR PATH ****
OutputFile = PatientNumber & ".xlsx" ' this is the file name of the new file, using the patient number

    MsgBox "Patient number is " & PatientNumber ' just checking. Delete this row once you are confident things run fine

    Workbooks.Open Filename:=InputPath & InputFile
    Set TargetFile = ActiveWorkbook ' the newly opened input file becomes the target file for copy and paste
    Set TargetSheet = TargetFile.Worksheets("Sheet1") ' this is the worksheet into which the patient data will be pasted.
    SourceSheet.Range("H6:H7").Copy ' copy the first two cells ....
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False '  ... and paste the values only
    SourceSheet.Range("H9:H10").Copy  ' copy the next two cells
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
' save the data file as a new file in the output folder, with the patient number as the name.
    ActiveWorkbook.SaveAs Filename:=OutputPath & OutputFile, FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
    TargetFile.Close ' close the newly saved file
    SourceFile.Close ' close the source file
End Sub

Open in new window


Author Closing Comment

ID: 40314740
Thank you for your help on this. This worked great for me. Thank you again.

Featured Post

Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question