+ Reply to Thread
Results 1 to 4 of 4

Macro/VBA code to Copy data from primary worksheet to a worksheet opened by macro

Hybrid View

  1. 04-26-2013, 09:38 AM #1
    MHamid
    MHamid is offline
    Registered User
    Join Date
    12-03-2012
    Location
    New Castle, DE
    MS-Off Ver
    Excel 2007
    Posts
    65

    Macro/VBA code to Copy data from primary worksheet to a worksheet opened by macro

    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
    Reply With QuoteRegister To Reply
  2. 04-26-2013, 11:09 AM #2
    MHamid
    MHamid is offline
    Registered User
    Join Date
    12-03-2012
    Location
    New Castle, DE
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Macro/VBA code to Copy data from primary worksheet to a worksheet opened by macro

    Never mind, I just solved my own problem. On to my fnal problem ...
    Reply With QuoteRegister To Reply
  3. 04-29-2013, 02:07 AM #3
    arlu1201
    arlu1201 is offline
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,167

    Re: Macro/VBA code to Copy data from primary worksheet to a worksheet opened by macro

    If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]
    Reply With QuoteRegister To Reply
  4. 04-30-2013, 11:10 AM #4
    MHamid
    MHamid is offline
    Registered User
    Join Date
    12-03-2012
    Location
    New Castle, DE
    MS-Off Ver
    Excel 2007
    Posts
    65

    Re: Macro/VBA code to Copy data from primary worksheet to a worksheet opened by macro

    Not a problem. I got it to work by using the following code:
        Sheets("937698").Select
    Cells.Select
    With Selection.Copy
        Range("A1").Select
        strFName = Range("I1").Value
        Workbooks.Open Filename:=strFName
            Sheets.Add
        ActiveSheet.Paste
        Range("A1").Select
        Sheets("Sheet1").Select
        Application.CutCopyMode = False
        Sheets("Sheet1").Move After:=Sheets(22)
        Sheets("Sheet1").Select
        Sheets("Sheet1").Move After:=Sheets(21)
        Sheets("Sheet1").Select
        Range("G1:I1").Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Range("G2").Select
        Application.CutCopyMode = False
        Sheets("Sheet1").Name = Format(Range("G1").Value, "mm-dd")
        ActiveWorkbook.Save
        Windows("Esc Disb Automation.xls").Activate
    End With
    If anyone knows of a shorter way to accomplish this, please let me know. Otherwise, this did solve my problem.


    Thank you!
    ~Miriam
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1