Solved

Excel spreadsheet

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

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

Outline Suppose you have some simple text based data in Excel that you would like to display as a PowerPoint presentation. Of course it would be possible to write some fairly complex VBA code that created a new slide for each line of the Excel data…
: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
This video teaches the viewer how to align pictures around text while keeping the text properly aligned in the document.
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…

803 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