Close

Page 1 of 2 12 LastLast
Results 1 to 20 of 26
  1. #1
    DF Jedi Soulassassin's Avatar
    Join Date
    Jan 2002
    Location
    Newcastle UK
    Posts
    2,735
    Thanks
    270
    Thanked:        338
    Karma Level
    399

    Help Excel help, copying data to another workbook.

    Hi,

    I currently have an excel document that when I press a button it runs a macro and saves a line of data to the end of a list in another sheet called DATACOLLECT. The data runs from B2 to F2, then after each button press the data is stored on the next line down.(B3 to F3 etc). What I want is another macro to call from the initial button press that will also save that last line to another workbook say called HISTORY and add it to the end of another list on say SHEET1 from cells P to T this time. Assume both files are in the same folder.

    Can this be done?

    Also if the History workbook is opened on another (networked) Pc will it cause an error as the files are stored on a network drive and usually cant be changed if they are open on another Pc?

    Hope this makes sense.

  2. #2
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,798
    Thanks
    1,002
    Thanked:        761
    Karma Level
    294

    Default Re: Excel help, copying data to another workbook.

    Yep can be done ;-) and Yep if History is open by someone else then it wont be able to save it down :-(

    Thanks to akimba

    Soulassassin (22nd May 2017) 


  3. #3
    DF VIP Member
    Mystical_2K's Avatar
    Join Date
    Jun 2002
    Location
    Sunnyvale
    Posts
    4,634
    Thanks
    1,160
    Thanked:        955
    Karma Level
    669

    Default Re: Excel help, copying data to another workbook.

    Yep its very doable mate, but if someone else has the document open then it will most likely throw up an error when it tries to save, the code for adding it into the new file will be very similar to the current one, just pointing it at a different book and location on the sheet
    You know he grew up as a little shitspark from the old shitflint and then he turned into a shitbonfire and driven by the winds of his monumental ignorance he turned into a raging shitfirestorm. If I get to be married to Barb I'll have total control of Sunnyvale and then I can unleash the shitnami tidal wave that will engulf Ricky and extinguish his shitflames forever. And with any luck he'll drown in the undershit of that wave. Shitwaves.

    Thanks to Mystical_2K

    Soulassassin (22nd May 2017) 


  4. #4
    DF Jedi Soulassassin's Avatar
    Join Date
    Jan 2002
    Location
    Newcastle UK
    Posts
    2,735
    Thanks
    270
    Thanked:        338
    Karma Level
    399

    Default Re: Excel help, copying data to another workbook.

    Does anyone have an example macro that does this where I can rename the ranges/sheets and doc names?
    Im not that good with Excel and everything I've done so for is from Googling, just can find the answer to this one.

    Also will it be possible to add something so if it does error it just ignores it?

  5. #5
    DF VIP Member
    Mystical_2K's Avatar
    Join Date
    Jun 2002
    Location
    Sunnyvale
    Posts
    4,634
    Thanks
    1,160
    Thanked:        955
    Karma Level
    669

    Default Re: Excel help, copying data to another workbook.

    Here is a basic example mate,

    Haven't had time to do you a more in-depth one but it does copy a range of values from the source workbook into a destination workbook and I have kept the code really straight forward (i hope!)

    I have also attached a working example (well it worked for me but this is excel)

    Extract the folder to "c:\"

    source file will be: C:\excel\source.xlsm
    destination file will be C:\excel\destination.xlsx

    Code:
    Sub Copy()
        Dim InputFile As Workbook
        Dim OutputFile As Workbook
        
        Set InputFile = ActiveWorkbook
        Set OutputFile = Workbooks.Open("c:\excel\destination.xlsx")
    
        'Now, copy what you want from InputFile:
        InputFile.Sheets("Sheet1").Activate
        InputFile.Sheets("Sheet1").Range("A1:B10").Copy
    
        OutputFile.Sheets("Sheet1").Activate
        OutputFile.Sheets("Sheet1").Range("A1").PasteSpecial
        OutputFile.Close savechanges:=True
    End Sub
    happy to help more but would have to be later in the week

    [Only registered and activated users can see links. ]
    You know he grew up as a little shitspark from the old shitflint and then he turned into a shitbonfire and driven by the winds of his monumental ignorance he turned into a raging shitfirestorm. If I get to be married to Barb I'll have total control of Sunnyvale and then I can unleash the shitnami tidal wave that will engulf Ricky and extinguish his shitflames forever. And with any luck he'll drown in the undershit of that wave. Shitwaves.

    Thanks to Mystical_2K

    Soulassassin (22nd May 2017) 


  6. #6
    DF Jedi Soulassassin's Avatar
    Join Date
    Jan 2002
    Location
    Newcastle UK
    Posts
    2,735
    Thanks
    270
    Thanked:        338
    Karma Level
    399

    Default Re: Excel help, copying data to another workbook.

    Quote Originally Posted by Mystical_2K View Post
    Here is a basic example mate,

    Haven't had time to do you a more in-depth one but it does copy a range of values from the source workbook into a destination workbook and I have kept the code really straight forward (i hope!)

    I have also attached a working example (well it worked for me but this is excel)

    Extract the folder to "c:\"

    source file will be: C:\excel\source.xlsm
    destination file will be C:\excel\destination.xlsx

    Code:
    Sub Copy()
        Dim InputFile As Workbook
        Dim OutputFile As Workbook
        
        Set InputFile = ActiveWorkbook
        Set OutputFile = Workbooks.Open("c:\excel\destination.xlsx")
    
        'Now, copy what you want from InputFile:
        InputFile.Sheets("Sheet1").Activate
        InputFile.Sheets("Sheet1").Range("A1:B10").Copy
    
        OutputFile.Sheets("Sheet1").Activate
        OutputFile.Sheets("Sheet1").Range("A1").PasteSpecial
        OutputFile.Close savechanges:=True
    End Sub
    happy to help more but would have to be later in the week

    [Only registered and activated users can see links. ]
    Thank you for your time, if I'm correct does this copy range A1 to B10 from the source and pastes it to A1 in the destination?
    However what I am after is copying the end row of a list (constantly being added to) from B to F and pasting it in another document into the end row of that likely longer list (i.e not the same row)

  7. #7
    DF VIP Member
    Mystical_2K's Avatar
    Join Date
    Jun 2002
    Location
    Sunnyvale
    Posts
    4,634
    Thanks
    1,160
    Thanked:        955
    Karma Level
    669

    Default Re: Excel help, copying data to another workbook.

    Quote Originally Posted by Soulassassin View Post
    Thank you for your time, if I'm correct does this copy range A1 to B10 from the source and pastes it to A1 in the destination?
    However what I am after is copying the end row of a list (constantly being added to) from B to F and pasting it in another document into the end row of that likely longer list (i.e not the same row)
    Yes mate, thats what it does, I will take another look and see what I can put together
    You know he grew up as a little shitspark from the old shitflint and then he turned into a shitbonfire and driven by the winds of his monumental ignorance he turned into a raging shitfirestorm. If I get to be married to Barb I'll have total control of Sunnyvale and then I can unleash the shitnami tidal wave that will engulf Ricky and extinguish his shitflames forever. And with any luck he'll drown in the undershit of that wave. Shitwaves.

    Thanks to Mystical_2K

    Soulassassin (23rd May 2017) 


  8. #8
    DF VIP Member
    Mystical_2K's Avatar
    Join Date
    Jun 2002
    Location
    Sunnyvale
    Posts
    4,634
    Thanks
    1,160
    Thanked:        955
    Karma Level
    669

    Default Re: Excel help, copying data to another workbook.

    oh and just to confirm when you say a list do you mean cells or a drop down list?
    You know he grew up as a little shitspark from the old shitflint and then he turned into a shitbonfire and driven by the winds of his monumental ignorance he turned into a raging shitfirestorm. If I get to be married to Barb I'll have total control of Sunnyvale and then I can unleash the shitnami tidal wave that will engulf Ricky and extinguish his shitflames forever. And with any luck he'll drown in the undershit of that wave. Shitwaves.

    Thanks to Mystical_2K

    Soulassassin (23rd May 2017) 


  9. #9
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,798
    Thanks
    1,002
    Thanked:        761
    Karma Level
    294

    Default Re: Excel help, copying data to another workbook.

    You want to find the last row there are a few techniques for this but I wrote myself a little function to do it
    Function LastRow(iColumn, iStart, iWorksheet) As Integer
    Dim lrn
    lrn = iStart
    Do Until Worksheets(iWorksheet).Range(iColumn & Trim(str(lrn))) = ""
    lrn = lrn + 1
    Loop
    LastRow = lrn - 1
    End Function

    So to find the lastrow you can do something like this

    Dim iRow as Integer
    iRow = LastRowNumber("A", 8, "Sheet1")

    InputFile.Sheets("Sheet1").Range("A" & CSTR(iRow) & ":B" CSTR(iRow)).Copy

    You could then do the LastRowNumber Function on the Log sheet to find the last row and do
    OutputFile.Sheets("Sheet1").Activate
    OutputFile.Sheets("Sheet1").Range("A"&CSTR(iRow+1)).Select
    Selection.PasteSpecial

    Thanks to akimba

    Soulassassin (23rd May 2017) 


  10. #10
    DF Jedi Soulassassin's Avatar
    Join Date
    Jan 2002
    Location
    Newcastle UK
    Posts
    2,735
    Thanks
    270
    Thanked:        338
    Karma Level
    399

    Default Re: Excel help, copying data to another workbook.

    Thank you for the help peeps, I've found this bit code that does almost exactly what I want. The only slight problem is that it doesn't let me paste a specific range, it just copies the entire row. However I overcame this by just saving the souce data in the same columns.

    Code:
    Private Sub LastRowToExport()
    
    
    Dim lastS1Row As Long       'Last Source Row
    Dim nextS2Row As Long       'Next Target Row
    Dim lastCol As Long         'Last Column on Source Sheet
    Dim s1Sheet As Worksheet, s2Sheet As Worksheet
    Dim source As String        'Source worksheet name
    Dim target As String        'Target worksheet name
    Dim path As String
    
    
        '---SET SHEET NAMES HERE---
        source = "Product"     'Source Worksheet Name
        path = "C:\data.xlsx"  'Target File Path including file name and extension
        target = "exportsheet" 'Target Worksheet Name
    
    
        'WARNING - THIS LINE WILL DISABLE NORMAL BREAKING - IN CASE OF LOOP
        Application.EnableCancelKey = xlDisabled    'Disables breaking when opening new book    
    
    
        'Define worksheets
        Set s1Sheet = ThisWorkbook.Sheets(source)           'Source Sheet
        Set s2Sheet = Workbooks.Open(path).Sheets(target)   'Target Sheet
    
    
        'Get the last row on each sheet and set the NEXT Row on the target.  Also total columns.
        lastS1Row = s1Sheet.Range("A" & Rows.count).End(xlUp).row       
        nextS2Row = s2Sheet.Range("A" & Rows.count).End(xlUp).row + 1   
        lastCol = s1Sheet.Cells(1, Columns.count).End(xlToLeft).column  'Headers in Row 1
    
    
        '---COPY ENTIRE ROW---  Loop through Cells by column
        For lCol = 1 To lastCol
            s2Sheet.Cells(nextS2Row, lCol) = s1Sheet.Cells(lastS1Row, lCol)  
        Next lCol
    
    
        'WRAP UP, SAVE EXPORTED SHEET, REACTIVATE SOURCE SHEET
        s2Sheet.Activate
        ActiveWorkbook.Close SaveChanges:=True
        s1Sheet.Activate
    
    
    End Sub

  11. #11
    DF Jedi Soulassassin's Avatar
    Join Date
    Jan 2002
    Location
    Newcastle UK
    Posts
    2,735
    Thanks
    270
    Thanked:        338
    Karma Level
    399

    Default Re: Excel help, copying data to another workbook.

    Okay, new problem. This is part of another macro that copies from 'DATAINPUT3' cell 'E33' and pastes it to the end of column P in sheet 'DATACOLLECT'.
    How do I make it paste the value only? It currently pastes everything.

    Application.ScreenUpdating = False
    Dim Lastrow As Long
    Lastrow = Sheets("DATACOLLECT").Cells(Rows.Count, "P").End(xlUp).Row + 1
    'Op into DATACOLLECT
    Sheets("DATAINPUT3").Range("E33").Copy Destination:=Sheets("DATACOLLECT").Range("P" & Lastrow)

  12. #12
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,798
    Thanks
    1,002
    Thanked:        761
    Karma Level
    294

    Default Re: Excel help, copying data to another workbook.

    Excel is really easy for beginner coding as you can record your actions and it will script it for you ;-)

    If you press record a macro and then

    copy a cell and then paste special values only

    then stop the macro recording

    if you then go into VBA probably under Module 2 (it records macros to new modules) you will see the code you need ;-)

    But here is the code

    .PasteSpecial Paste:=xlPasteValues

    Thanks to akimba

    Soulassassin (25th May 2017) 


  13. #13
    DF VIP Member
    Mystical_2K's Avatar
    Join Date
    Jun 2002
    Location
    Sunnyvale
    Posts
    4,634
    Thanks
    1,160
    Thanked:        955
    Karma Level
    669

    Default Re: Excel help, copying data to another workbook.

    ^ What he said
    You know he grew up as a little shitspark from the old shitflint and then he turned into a shitbonfire and driven by the winds of his monumental ignorance he turned into a raging shitfirestorm. If I get to be married to Barb I'll have total control of Sunnyvale and then I can unleash the shitnami tidal wave that will engulf Ricky and extinguish his shitflames forever. And with any luck he'll drown in the undershit of that wave. Shitwaves.

    Thanks to Mystical_2K

    akimba (24th May 2017) 


  14. #14
    DF Jedi Soulassassin's Avatar
    Join Date
    Jan 2002
    Location
    Newcastle UK
    Posts
    2,735
    Thanks
    270
    Thanked:        338
    Karma Level
    399

    Default Re: Excel help, copying data to another workbook.

    When I record a macro it just copies and pastes (special) to that one cell. What this does is copies and pastes to the end of a row which for some reason I can't get the macro to record.
    Where in the line of code (post #11) do I add the '.PasteSpecial Paste:=xlPasteValues' ?


  15. #15
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,798
    Thanks
    1,002
    Thanked:        761
    Karma Level
    294

    Default Re: Excel help, copying data to another workbook.

    Yeah record a macro to get the command needed and apply it to where needed

    The problem with your script you found is you are not copying and pasting it is making a reference to the cells

    s2Sheet.Cells(nextS2Row, lCol) = s1Sheet.Cells(lastS1Row, lCol)

    PM me the file dude this will take seconds to do and I will annotate the VBA so you can learn from it ;-)

    2 Thanks given to akimba

    Ganty (25th May 2017), Soulassassin (25th May 2017) 


  16. #16
    DF Jedi Soulassassin's Avatar
    Join Date
    Jan 2002
    Location
    Newcastle UK
    Posts
    2,735
    Thanks
    270
    Thanked:        338
    Karma Level
    399

    Default Re: Excel help, copying data to another workbook.

    Can't find a way to send a file through PM, unless I'm missing something obvious.

    Attached it here anyhow, excuse the file but its something I'm just modifying. The layout etc is not me.

    Right here goes,

    Go to DATAINPUT3 tab and click 31 Next,
    This saves various bits of data to DATACOLLECT
    Two columns, S and T both get there data from the DATAINPUT tab which has two drop down lists (date and shift)
    When pasted to the DATACOLLECT the cell it's pasted to becomes a list formulated cell and also copies font size etc
    This is what I need changing to value only.

    Thank you

  17. #17
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,798
    Thanks
    1,002
    Thanked:        761
    Karma Level
    294

    Default Re: Excel help, copying data to another workbook.

    So I have redone your code for the Datacollect

    From

    Application.ScreenUpdating = False
    Dim Lastrow As Long
    Lastrow = Sheets("DATACOLLECT").Cells(Rows.Count, "P").End(xlUp).Row + 1
    'Op into DATACOLLECT
    Sheets("DATAINPUT3").Range("E33").Copy Destination:=Sheets("DATACOLLECT").Range("P" & Lastrow)
    'Weld into DATACOLLECT
    Sheets("DATAINPUT3").Range("E34").Copy Destination:=Sheets("DATACOLLECT").Range("Q" & Lastrow)
    'Concern to DATACOLLECT
    Sheets("DATAINPUT3").Range("E35").Copy Destination:=Sheets("DATACOLLECT").Range("R" & Lastrow)
    'Date from DATAINPUT to DATACOLLECT
    Sheets("DATAINPUT").Range("F2").Copy Destination:=Sheets("DATACOLLECT").Range("S" & Lastrow)
    'Shift from DATAINPUT to DATACOLLECT
    Sheets("DATAINPUT").Range("G2").Copy Destination:=Sheets("DATACOLLECT").Range("T" & Lastrow)

    To

    Sheets("DATAINPUT3").Range("E33:E35").Copy

    'TransPose the results to do it as 1 range
    Sheets("DATACOLLECT").Activate
    Sheets("DATACOLLECT").Range("P" & Lastrow).Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Sheets("DATAINPUT").Range("F2:G2").Copy
    Sheets("DATACOLLECT").Activate
    Sheets("DATACOLLECT").Range("S" & Lastrow).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False

  18. #18
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,798
    Thanks
    1,002
    Thanked:        761
    Karma Level
    294

    Default Re: Excel help, copying data to another workbook.

    Theres a link to this file which you didn't supply
    C:\Users\XXXX\Desktop\Line 5 P32S FM Touch Up Daily - Apr to June 17.xlsm

    But you should be able get a grasp of what I have done

    Also this is not a great idea as tis is date related you will have to change your VBA code every couple of months i.e. July to Sept 17.xlsm

  19. #19
    DF Jedi akimba's Avatar
    Join Date
    Jun 2006
    Location
    UK
    Posts
    2,798
    Thanks
    1,002
    Thanked:        761
    Karma Level
    294

    Default Re: Excel help, copying data to another workbook.

    What is it you are manufacturing?

  20. #20
    DF Jedi Soulassassin's Avatar
    Join Date
    Jan 2002
    Location
    Newcastle UK
    Posts
    2,735
    Thanks
    270
    Thanked:        338
    Karma Level
    399

    Default Re: Excel help, copying data to another workbook.

    That works except I only replaced the code from Date and shift as the other Data was being missed, but that was probably just my wording. Thank you very much.

    The link to the file I remembered after I'd left for work that that would cause an error, is there a way 'skip on error' by any chance as this would also happen if somebody opened that file on the network?

    The name of the file was just the one I was given for an example and I would have to change it to the location on the works computer anyway so that's just local for me atm, but thanks for the heads up.

    The file is for the front member of a car we manufacture, that is the part under the engine that all the wheel linkage is all bolted to.

Page 1 of 2 12 LastLast

Similar Threads

  1. Replies: 0
    Last Post: 23rd July 2012, 06:50 PM
  2. Replies: 14
    Last Post: 15th April 2012, 02:46 AM
  3. copying data from 20gb to 60gb
    By eblaster101 in forum Microsoft Consoles
    Replies: 2
    Last Post: 18th December 2008, 07:28 PM
  4. Data matching in excel..
    By daz73 in forum PC Software
    Replies: 2
    Last Post: 19th August 2005, 08:57 PM
  5. The Official MUFC Maths Workbook
    By kanu690 in forum Football
    Replies: 5
    Last Post: 4th January 2003, 06:04 PM

Social Networking 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
  •