Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Macro to Copy data from one file and paste into another

    I've recorded a macro to copy data from one workbook and paste the values into the workbook from which I initiate the macro. In the recorded version, I simply close the source workbook, but if there's more than one file open the macro may not work as expected.

    How do I get the code to return to the target workbook from which the macro is initiated?
    The filename in the code ' Windows("OT&TimeApprovalReport_2012-02-24.xlsm").Activate will change daily.

    Here's what I've got so far, between recorded and edited code:

    Sub GetTW_Data()
    ' GetTW_Data Macro

    'Opens source file (this filename never changes)
    Workbooks.Open Filename:="\\lv10021\finance\DOR\DailyPayroll\tw_d aily.xls"
    Windows("tw_daily.xls").Activate

    'Selects, then copies data from tw_daily
    Range("a1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy

    'Return to target workbook
    ' ActiveWorkbook.Close

    Range("G7").Select
    ActiveSheet.PasteSpecial Paste:=xlPasteValues
    ' Windows("OT&TimeApprovalReport_2012-02-24.xlsm").Activate
    End Sub

  2. #2
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    At the the beginning of your Macro, grab the full path of whichever workbook which is running the code as follows:

    ActiveWorkbookPath = ThisWorkbook.Path & "\" & ThisWorkbook.Name

    Than, at the end, substitute ActiveWorkbookPath for the filename in your last Activate statement.

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Create a variable as type Workbook: Dim wkbkCurrent as Workbook.
    Add a line at the start of your code: Set wkbkCurrent = ActiveWorkbook.
    Add a line before you select the target cell: wkbkCurrent.Activate.
    After you close the copied FROM workbook add a line: Set wkbkCurrent = Nothing.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Further to the other answers, here's my version:

    Sub GetTW_Data()

    ThisWorkbook.Activate 'start in THIS workbook
    Sheets(1).Select 'switch to data import sheet

    'Opens source file (this filename never changes)
    Workbooks.Open Filename:="\\lv10021\finance\DOR\DailyPayroll\tw_daily.xls", ReadOnly:=True


    Workbooks("tw_daily.xls").Activate 'switch to source workbook
    Sheets(1).Select 'switch to source data sheet
    [a1].CurrentRegion.Copy 'Copy data to clipboard

    ThisWorkbook.Activate 'Return to THIS workbook

    [G7].PasteSpecial Paste:=xlPasteValues 'paste data to import start cell
    [a1].select 'cancels highlighted paste region

    Workbooks("tw_daily.xls").Close 'close source data workbook

    End Sub

    'RZ notes:
    I always prefer to start my macro to specifically begin in a known location, i.e. when I'm on a specified sheet in a specified workbook i.e. generally the workbook that actually contains the code that is running.
    You can adjust Sheets(1) to a named sheet as required, in both files.

    Your previous code either assumes that the source data file only has one sheet in it or will always be saved when on the 'correct' data sheet.

    I prefer opening a source datafile in ReadOnly mode, since this won't interfere with another User currently working with the same file, and you don't need to bother with warning messages telling you this.

    Also, when I'm 'pasting' in data from another workbook, I prefer to clear the import area first. This way, if you run the macro again later and there is now 'less' data in the source file, you won't be left with any 'remnants' from a previous import.

    zeddy

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    Further to my previous post, in my 'original' code, my code comments are aligned up to a tab stop, but don't retain this when I post here. Also, the Workbooks.Open line is all on one line, not wrapped.

    zeddy

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thanks Zeddy. Your code works great.

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by zeddy View Post
    Hi

    Further to my previous post, in my 'original' code, my code comments are aligned up to a tab stop, but don't retain this when I post here. Also, the Workbooks.Open line is all on one line, not wrapped.

    zeddy
    Zeddy,

    When you post code surround it with [code] your code here [/code] tags. It will hold the indention and also make it easy for the reader to select & copy the code.

    Note: the tags will not show up in your post, I've surrounded them with other tags to keep them from being interpreted.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. The Following User Says Thank You to RetiredGeek For This Useful Post:

    zeddy (2012-03-02)

  9. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi RG

    Thanks for letting me know how to do that.
    Now what about those lovely jpg images and other stuff I see in replies.
    Do you just copy them and paste them into the 'quick reply' panel??

    zeddy

  10. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,435
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Quote Originally Posted by zeddy View Post
    Hi RG

    Thanks for letting me know how to do that.
    Now what about those lovely jpg images and other stuff I see in replies.
    Do you just copy them and paste them into the 'quick reply' panel??

    zeddy
    Zeddy,

    Sorry no cut & paste. What you do is have the file saved on your disk then position your cursor where you want the graphic then click the icon that looks like a picture frame 3rd from right. You'll get a browse button and you just find the file and click the Upload button. There is another way to do it via the Go Advanced button then scroll down to Manage Attachments button. This will let you attach sample files like .xls etc. and also graphics but they will all be at the bottom of your post rather than interspersed.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  11. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Nur Hessa (2015-05-03)

  12. #10
    New Lounger
    Join Date
    Dec 2012
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I found your post extreamly helpful and solved almost all my challenges as they were very similiar except I'm using word.

    below is my code and unfortunatly it isnt working as it is getting stuck at "Selection.EndKey Unit:=wdLine"

    ThisDocument.Activate 'start in THIS Document
    'Opens source file (this filename never changes)
    Application.Documents.Open FileName:="S:\ARRANGEMENT FILES\0 Secretarial Masters\0 FILE START-UP\Old Arrangement File\part 2 NEW MASTER - Arrangement File.docx", ReadOnly:=True
    Documents("S:\ARRANGEMENT FILES\0 Secretarial Masters\0 FILE START-UP\Old Arrangement File\part 2 NEW MASTER - Arrangement File.docx").Activate 'switch to source file
    Selection.WholeStory
    Selection.Copy 'Copy data to clipboard
    ActiveWindow.Close
    ThisDocument.Activate 'Return to THIS Document
    Selection.EndKey Unit:=wdLine
    Selection.PasteAndFormat (wdFormatOriginalFormatting)
    Documents("S:\ARRANGEMENT FILES\0 Secretarial Masters\0 FILE START-UP\Old Arrangement File\part 2 NEW MASTER - Arrangement File.docx").Close

  13. #11
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    988
    Thanks
    56
    Thanked 105 Times in 90 Posts
    It is a syntax issue - see here: http://msdn.microsoft.com/en-us/libr.../ff195593.aspx

    Having said that, you really need to post this in the Word forum as you are dealing with Word-specific VBA matters: http://windowssecrets.com/forums/for...ord-Processing

  14. #12
    New Lounger
    Join Date
    Mar 2015
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question question, similar

    was not sure if I should start a new thread or not, suppose I will see if anyone answers this! my problem is similar, except I need to copy all data from current workbook/worksheet to one the user selects.

    here is my code, only problem I am having as of now is that it opens the selected workbook, and stops running code.

    this is making me nuts, I have done this a lot before, but it has been at least 2 years and I cannot find the old code! errgg!! I don't remember it being that big of a deal!!

    Code:
    Public Sub SaveSheets(sName As String)
        Dim sh As Worksheet
        Dim wbDest As Workbook
        Dim wbSource As Workbook
        Dim s As String
        Dim fd As FileDialog
        Dim scmdName As String
     
        If sName Like "*Tech*" Then
            scmdName = "cmdSaveTech"
        Else
            scmdName = "cmdSaveCost"
        End If
        
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
        
        With fd
            .AllowMultiSelect = False
            .Title = "Choose file to save Worksheet in..."
            If .Show = -1 Then
                s = .SelectedItems(1)
            Else
                Exit Sub
            End If
        End With
        
        'Set the object variable to Nothing.
        Set fd = Nothing
        Application.ScreenUpdating = False
        
        Set wbSource = Application.ActiveWorkbook             
        Set wbDest = Workbooks.Open(s)  <<<<-------------------STOPS HERE
        wbSource.Activate
        wbSource.Worksheets(sName).Cells.Copy
        wbDest.Sheets.Add After:=ActiveSheet
        wbDest.ActiveSheet.Paste
        wbDest.ActiveSheet.Name = sName
        wbSource.Application.CutCopyMode = False
        With wbDest.Worksheets(sName).Rows("7:12").Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
            :=xlBetween
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        wbDest.Worksheets(sName).Range("D7:G12").Copy
        wbDest.Worksheets(sName).Range("D7:G12").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        wbDest.Worksheets(sName).Shapes.Range(Array(scmdName)).Delete
       Application.DisplayAlerts = False
       With wbDest
           .Save
           .Close
       End With
        
    End Sub

  15. #13
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    was not sure if I should start a new thread or not
    alfaista,

    Since the OPs issue is not 100% resolved, this would be considered "hijacking" the post. Perhaps one of the moderators may advise the best course of action. If moved, maybe this solution can be moved along with it. I revised your code that will open the destination workbook in a much cleaner fashion. The code runs through nicely up to the line:

    wbDest.Worksheets(sName).Shapes.Range(Array(scmdNa me)).Delete

    which refers to an object in your workbook that I do not have enough information to continue with but well past the line of code you stated was problematic..

    HTH,
    Maud

    Code:
    Public Sub SaveSheets(sName As String)
    Application.ScreenUpdating = False
    '-------------------------------------
    'DECLARE AND SET VARIABLES
        Dim sh As Worksheet
        Dim wbDest As Workbook
        Dim wbSource As Workbook
        Dim s As String
        Dim scmdName As String
        Set wbSource = ThisWorkbook
    '-------------------------------------
    'DETERMINE SHAPE NAME BASED ON PASSED PARAMETER
        If sName Like "*Tech*" Then
            scmdName = "cmdSaveTech"
        Else
            scmdName = "cmdSaveCost"
        End If
    '--------------------------------------
    'GET DESTINATION FILE
        On Error Resume Next
        ChDir (Path)
        s = Application.GetOpenFilename _
            (Title:="Choose file to save Worksheet in...", _
            FileFilter:="Xlsx Files *.xlsx (*.xlsx),")
        If s = "False" Then
            Exit Sub
        Else
            Workbooks.Open Filename:=s  'OPEN THE DESIRED FILE
        ChDir (OldDir)
        End If
        On Error GoTo 0
    '--------------------------------------
    'COPY SOURCEFILE SHEET TO DESTINATION WORKBOOK
        Set wbDest = Application.ActiveWorkbook
        wbSource.Activate
        wbSource.Worksheets(sName).Cells.Copy
        wbDest.Sheets.Add After:=ActiveSheet
        wbDest.ActiveSheet.Paste
        wbDest.ActiveSheet.Name = sName
        wbSource.Application.CutCopyMode = False
    '--------------------------------------
    'CONTINUE WITH REST OF CODE
        With wbDest.Worksheets(sName).Rows("7:12").Validation
            .Delete
            .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
            :=xlBetween
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = True
        End With
        wbDest.Worksheets(sName).Range("D7:G12").Copy
        wbDest.Worksheets(sName).Range("D7:G12").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        wbDest.Worksheets(sName).Shapes.Range(Array(scmdName)).Delete
        Application.DisplayAlerts = False
        With wbDest
           .Save
           .Close
        End With
        Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    My arms are extended for a hand slapping if I should not have responded but I couldn't help myself.
    Last edited by Maudibe; 2015-03-11 at 20:12.

  16. #14
    New Lounger
    Join Date
    May 2015
    Posts
    3
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Post TransferData In Another Sheet

    Hi,Friends

    First, I am a new member at here..
    I need you help for solve my task in excel.
    My problem was
    i. How the data in sheet 1 could be transferred
    to sheet 2 ? ( as example 2: complete )

    If we refer to the EXAMPLE 2:COMPLETE, students data
    can be printed by simply selecting their name.

    I hope that (EXAMPLE 1:MY ASSIGNMENT) has a functional
    just like EXAMPLE 2:COMPLETE.

    I attach a copy of the data for refrence in resolving my problem.

    Thank You.. HESSA 😂😂😂😂😂Screenshot_2015-05-03-17-12-13.png
    Attached Files Attached Files

  17. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,826
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Nur

    Welcome to the Lounge!

    From my limited Malaysian, see my attached file..
    Click dropdown to select name.
    Marks and Grade are returned for selected person.
    I added missing item PI.

    zeddy
    Attached Files Attached Files

  18. The Following User Says Thank You to zeddy For This Useful Post:

    Nur Hessa (2015-05-05)

Page 1 of 2 12 LastLast

Posting Permissions

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