Solved

Excel spreadsheet

Posted on 2014-09-04
3
192 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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

I recently resolved a client's Office 2013 installation problem and wanted to offer an observation that may help you with troubleshooting similar issues. The client ordered three Dell Optiplex system units with the Windows 7 downgrade option inst…
This article will shed light on the latest trends when it comes to your resume building needs. For far too long, the traditional CV format has monopolized the recruitment market.
This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now