?
Solved

Excel spreadsheet

Posted on 2014-09-04
3
Medium Priority
?
200 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article describes how to use the Send to Mail Recipient command. The instructions apply generally to Office 2007 and later versions, but Microsoft® Word 2013 was used for the specific steps and figures.  What is Send to Mail Recipient? Send…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

771 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