Save Excel Workbook in different location

Question

I am writing a program that is copying and pasting data from one workbook to another. I want to save one workbook in a different location, close it, then open a new workbook from a file and do the same thing(I am copying data from multiple workbooks and pasting this data into one master workbook). I also need help reversing the signs (for example: I will copy the number 1 from wbk1 and I need to paste -1 in wbk2). Please help! I am new to VBA and dont understand why my code won't work!

Here's my code:

Function GetBook() As String GetBook = ActiveWorkbook.Name End Function

Sub Paste() Dim wbk As Workbook Dim wbkH As Workbook Dim fso As Object Dim COID As String

Set wbk = Workbooks("0_Master Footnote Operating Lease May 2014_LIVE_essbase") COID = "6985" 'Facility number used to search in wbk

Set wbkH = Workbooks(GetBook)

'Subtractions wbkH.Activate 'Select Hospitals document Sheets("Additions & Expirations").Select 'select ws

Columns("G:G").Select
Range("G:G").Activate
Selection.Find(What:="Total Lease", After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Select 'Selects entire row

With ActiveCell
    Range(Cells(.Row, "H"), Cells(.Row, "H")).Select 'Select first total in column
    Selection.Copy

    'enter in hospitals COID
    wbk.Activate
    Sheets("Compare CY to PY").Select

    Columns("C:C").Select
    Range("C:C").Activate
    Selection.Find(What:=COID, After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

    ActiveCell.EntireRow.Select
    With ActiveCell
    Range(Cells(.Row, "J"), Cells(.Row, "J")).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False 'clears clipboard
    End With  'I NEED TO CHANGE SIGN ON THIS POSTED VALUE (EX. 1 TO -1)
End With

wbkH.Activate 'Select Hospitals document
Sheets("Misc Reconciling Items").Select 'select ws

Columns("A:A").Select
Range("A:A").Activate
Selection.Find(What:="Annualized", After:=ActiveCell, LookIn:=xlValues, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireRow.Select 'Selects entire row

With ActiveCell
    Range(Cells(.Row, "D"), Cells(.Row, "D")).Select 'Select first total in column
    Selection.Copy

    'enter in hospitals COID
    wbk.Activate
    Sheets("Compare CY to PY").Select

    Columns("C:C").Select
    Range("C:C").Activate
    Selection.Find(What:=COID, After:=ActiveCell, LookIn:=xlValues, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate

    ActiveCell.EntireRow.Select
    With ActiveCell
    Range(Cells(.Row, "L"), Cells(.Row, "L")).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False 'clears clipboard
    End With 'I NEED TO CHANGE SIGN ON THIS POSTED VALUE (EX. 1 TO -1)
End With

wbkH.Activate
ActiveWorkbook.SaveAs ("C:\Program Files\" & GetBook) 'THIS CODE WONT WORK AND I HAVE TRIED VARIOUS CODES.

Please narrow down your question and limit your code pertinent to the question (currently it looks like a request for project dev). Rgds, — Alexander Bell

Alexander Bell Alexander Bell · Accepted Answer · 2014-06-30T19:45:22

As per you topic header, it seems that the only problem is just saving Excel Workbook (re: the line ActiveWorkbook.SaveAs ("C:\Program Files\" & GetBook) 'THIS CODE WONT WORK AND I HAVE TRIED VARIOUS CODES). If this is correct, then couple code snippets can help (C#):

Exampl 1. Close and save

object misValue = System.Reflection.Missing.Value;
ActiveWorkbook.Close(true, filePath, misValue);

Example 2 (from http://msdn.microsoft.com/en-us/library/h1e33e36.aspx)

this.SaveAs(@"C:\Book1.xml", missing,
    missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
    missing, missing, missing, missing, missing);

Example 3 (from http://msdn.microsoft.com/en-us/library/h1e33e36.aspx)

this.Application.ActiveWorkbook.SaveAs(@"C:\Test\Book1.xml", 
    Excel.XlSaveAsAccessMode.xlNoChange);

Regards,

Save Excel Workbook in different location

1 Answers