Hello,

I have a new problem. I created a code that will open a file based on a cell. My dilema is that once the file is opened, I want it to go back to the original file (Esc Disb Automation.xls) and copy the data in the selected tab into a new tab in the opened file (the one that was opened by the macro) and paste it there and rename that file based on a date in a specific cell (G1) in that sheet. Basically, once the script below opens the file specified in cell I1, I want it to:
1) Go back to "Esc Disb Automation.XLS"
2) Go to sheet labeled "944300"
3) Copy that whoel sheet
4) Go back to the spreadsheet that was opened from cell I1
5) Paste the sheet into the opened workbook from cell I1
6) Rename the tab based on cell G1
7) Save the file

The path name in cell I1 is based on the date in cell G1. If the date was 3/30/2013, then the file it will open will be the March-2013 file. For instancem, the current file name in cell I1 is K:\shared\InvestorBankRec\Reconciliations\944300 ESCROW DISB\2013 Reconciliation\944300 - Bank Statement - 2013\944300 - 03-13 Statements.xls
However, if the date is 4/22/2013 the file name would be K:\shared\InvestorBankRec\Reconciliations\944300 ESCROW DISB\2013 Reconciliation\944300 - Bank Statement - 2013\944300 - 04-13 Statements.xls

I don't want the code to read the exact file name because the name of the file will change every month but daily activity will be saved to the monthly file. Is there a way to get the data to copy and paste into the file that is opened from my macro below?

If I need to explain more, please let me know. If there is a much better way to do this, please let me know as well. I need this done for 4 other sheets in the Esc Disb Automation.xls and was thinking to just have it run for one tab (the 944300) and copy it for the other 4 tabs (937698, 937839, 976601, and 644597445). Can this be looped?



Sub Saving()
'
' Saving Macro
' Macro recorded 4/25/2013 by Miriam Hamid
'

'
    Sheets("944300").Select
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "='Stmnt (All)'!R3C1"
    Range("G1").Select
    Selection.NumberFormat = "m/d/yyyy"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Switch!R506C12:R[756]C12,MIN(IF((R1C7>=Switch!R[505]C10:R757C10)*(R1C7<=Switch!R506C11:R757C11),MATCH(ROW(Switch!R[505]C12:R757C12),ROW(Switch!R507C12:R757C12)))))"
    Range("H1").Select
    Selection.FormulaArray = _
        "=INDEX(Switch!R506C12:R[756]C12,MIN(IF((R1C7>=Switch!R[505]C10:R757C10)*(R1C7<=Switch!R506C11:R757C11),MATCH(ROW(Switch!R[505]C12:R757C12),ROW(Switch!R507C12:R757C12)))))"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R1C8,Switch!C12:C13,2,0)"
    Range("H1").Select
    Selection.TextToColumns Destination:=Range("H1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, OtherChar _
        :=" ", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    Selection.FormulaArray = _
        "=INDEX(Switch!R506C12:R[756]C12,MIN(IF((R1C7>=Switch!R[505]C10:R757C10)*(R1C7<=Switch!R506C11:R757C11),MATCH(ROW(Switch!R[505]C12:R757C12),ROW(Switch!R506C12:R757C12)))))"
    Range("G1:I1").Select
    Selection.Copy
    Sheets("937698").Select
    Range("G1").Select
    ActiveSheet.Paste
    Sheets("937698").Select
    Range("H1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Switch!R2C12:R[252]C12,MIN(IF((R1C7>=Switch!R[1]C10:R253C10)*(R1C7<=Switch!R2C11:R253C11),MATCH(ROW(Switch!R[1]C12:R253C12),ROW(Switch!R2C12:R253C12)))))"
    Range("H1").Select
    Selection.FormulaArray = _
        "=INDEX(Switch!R2C12:R[252]C12,MIN(IF((R1C7>=Switch!R[1]C10:R253C10)*(R1C7<=Switch!R2C11:R253C11),MATCH(ROW(Switch!R[1]C12:R253C12),ROW(Switch!R2C12:R253C12)))))"
    ActiveWindow.SmallScroll ToRight:=0
    Range("G1:I1").Select
    Selection.Copy
    Sheets("937839").Select
    Range("G1").Select
    ActiveSheet.Paste
    Range("H1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Switch!R254C12:R[504]C12,MIN(IF((R1C7>=Switch!R[253]C10:R505C10)*(R1C7<=Switch!R254C11:R505C11),MATCH(ROW(Switch!R[253]C12:R505C12),ROW(Switch!R254C12:R505C12)))))"
    Range("H1").Select
    Selection.FormulaArray = _
        "=INDEX(Switch!R254C12:R[504]C12,MIN(IF((R1C7>=Switch!R[253]C10:R505C10)*(R1C7<=Switch!R254C11:R505C11),MATCH(ROW(Switch!R[253]C12:R505C12),ROW(Switch!R254C12:R505C12)))))"
    Range("G1:I1").Select
    Selection.Copy
    Sheets("976601").Select
    Range("G1").Select
    ActiveSheet.Paste
    Range("H1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Switch!R758C12:R[1008]C12,MIN(IF((R1C7>=Switch!R[757]C10:R1009C10)*(R1C7<=Switch!R758C11:R1009C11),MATCH(ROW(Switch!R[757]C12:R1009C12),ROW(Switch!R758C12:R1009C12)))))"
    Range("H1").Select
    Selection.FormulaArray = _
        "=INDEX(Switch!R758C12:R[1008]C12,MIN(IF((R1C7>=Switch!R[757]C10:R1009C10)*(R1C7<=Switch!R758C11:R1009C11),MATCH(ROW(Switch!R[757]C12:R1009C12),ROW(Switch!R758C12:R1009C12)))))"
    Range("G1:I1").Select
    Selection.Copy
    Sheets("644597445").Select
    Range("G1").Select
    ActiveSheet.Paste
    Range("H1").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = _
        "=INDEX(Switch!R1010C12:R[1260]C12,MIN(IF((R1C7>=Switch!R[1009]C10:R1261C10)*(R1C7<=Switch!R1010C11:R1261C11),MATCH(ROW(Switch!R[1009]C12:R1261C12),ROW(Switch!R1010C12:R1261C12)))))"
    Range("H1").Select
    Selection.FormulaArray = _
        "=INDEX(Switch!R1010C12:R[1260]C12,MIN(IF((R1C7>=Switch!R[1009]C10:R1261C10)*(R1C7<=Switch!R1010C11:R1261C11),MATCH(ROW(Switch!R[1009]C12:R1261C12),ROW(Switch!R1010C12:R1261C12)))))"
    Columns("B:B").Select
    Sheets("644597445").Activate
    Columns("B:B").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("H2").Select
    Sheets("644597445").Activate
    Cells.Select
    With Selection.Font
        .Name = "Tahoma"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
    End With
    Sheets("976601").Activate
    Columns("B:B").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("H2").Select
    Sheets("976601").Activate
    Cells.Select
    With Selection.Font
        .Name = "Tahoma"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
    End With
    Sheets("937839").Activate
    Columns("B:B").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("H2").Select
    Sheets("937839").Activate
    Cells.Select
    With Selection.Font
        .Name = "Tahoma"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
    End With
    Sheets("937698").Activate
    Columns("B:B").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("H2").Select
    Sheets("937698").Activate
    Cells.Select
    With Selection.Font
        .Name = "Tahoma"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
    End With
    Sheets("944300").Activate
    Columns("B:B").Select
    Columns("B:B").EntireColumn.AutoFit
    Range("H2").Select
    Sheets("944300").Activate
    Cells.Select
    With Selection.Font
        .Name = "Tahoma"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
    End With
    Sheets("Stmnt (All)").Select
    Range("AC1").Select
    ActiveWindow.SmallScroll ToRight:=-10
    Range("A1").Select
    Sheets("644597445").Select
    Range("A1").Select
    Sheets("976601").Select
    Range("A1").Select
    Sheets("937839").Select
    Range("A1").Select
    Sheets("937698").Select
    Range("A1").Select
    Sheets("944300").Select
    Range("A1").Select
    Sheets("944300").Select
    strFName = Range("I1").Value
    Workbooks.Open Filename:=strFName
    Windows("Esc Disb Automation.xls").Activate
    Sheets("944300").Select
    End Sub

Thank you,
Miriam