Solved

Excel spreadsheet

Posted on 2014-09-04
3
196 Views
Last Modified: 2014-09-13
I have a excel spreadsheet that I enter data in and then save as to a new folder but under different name. I have a macro on that spreadsheet that runs and exports data to a new text file. The macro works fine before I save the template to the new location and under the new name. the new file is looking for the template name.

 Range("C4").Select
    Windows("Templete.xls").Activate
    The new file is
Range("C4").Select
    Windows("John-Smyth 01-03-1944.xls").Activate
Is there a way to keep using the template and sill have the macro run without this error.
Thank you.
0
Comment
Question by:jodyreid
  • 2
3 Comments
 

Expert Comment

by:Michael Hernandez
ID: 40304356
Not sure what error you are receiving.
Seems you're manually saving and naming the file.

You're better off referencing the workbook using the activeworkbook property.
You can use it to get the name of the file, the path,  close, save etc.
    Dim Sourcewb As Workbook
    Dim Destwb As Workbook
    
'Save the name of the current file (your template)
    Set Sourcewb = ActiveWorkbook

'Copy the sheet to a new workbook
    ActiveSheet.Copy
    Set Destwb = ActiveWorkbook

'go back to the template file. Or make sure you're on the new file Destwb.Activate
    Sourcewb.Activate



   

Open in new window

0
 

Accepted Solution

by:
jodyreid earned 0 total points
ID: 40305955
Not sure how this works with my macro. This is my Macro.
Sub ExportData()
'
' ExportData Macro
'



'
    ChDir "T:\Occupational Therapy"
    Workbooks.Open Filename:="T:\Occupational Therapy\Therapy.xls"
    Windows("John-Smyth 03-03-1944.xls").Activate
    Range("E5:H5").Select
    ActiveCell.FormulaR1C1 = "140544"
    Windows("Therapy.xls").Activate
    Range("A4").Select
    ActiveSheet.Paste
    Selection.AutoFill Destination:=Range("A4:A17"), Type:=xlFillDefault
    Range("A4:A17").Select
    Range("E4").Select
    Selection.Copy
    Range("E5:E17").Select
    ActiveSheet.Paste
    Range("C4").Select
    Windows("John-Smyth 03-03-1944.xls").Activate
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    Range("AH10:AH31").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AH10:AH11").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Therapy.xls").Activate
    ActiveSheet.Paste
    Range("C6").Select
    Windows("John-Smyth 03-03-1944.xls").Activate
    Range("AH13:AH14").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Therapy.xls").Activate
    ActiveSheet.Paste
    Range("C8").Select
    Windows("John-Smyth 03-03-1944.xls").Activate
    Range("AH16:AH17").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Therapy.xls").Activate
    ActiveSheet.Paste
    Range("C10").Select
    Windows("John-Smyth 03-03-1944.xls").Activate
    Range("AH19:AH20").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Therapy.xls").Activate
    ActiveSheet.Paste
    Range("C12").Select
    Windows("John-Smyth 03-03-1944.xls").Activate
    Range("AH22:AH23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Therapy.xls").Activate
    ActiveSheet.Paste
    Range("C14").Select
    Windows("John-Smyth 03-03-1944.xls").Activate
    Range("AH25:AH26").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Therapy.xls").Activate
    ActiveSheet.Paste
    Range("C16").Select
    Windows("John-Smyth 03-03-1944.xls").Activate
    Range("AH28:AH29").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Therapy.xls").Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "140544"
    ChDir "T:\Occupational Therapy\Export Files"
    ActiveWorkbook.SaveAs Filename:= _
        "T:\Occupational Therapy\Export Files\140544.xls", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    ActiveWindow.Close
End Sub

Thank you for you help with this.
0
 

Author Closing Comment

by:jodyreid
ID: 40320683
I did not get an answer that worked for my problem. Will post again. Thank you
0

Featured Post

Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
LARGE INDEX formula 3 83
Order table with macro 3 93
Excel 2007 Formula to Remove Null/#Value Results from Cell 1 67
Need conditional formating when doubles 14 20
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Companies keep a much closer eye on costs today, so changing to new Technology – Microsoft Office 365 is the smartest move to take.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

828 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