Excel help, copying data to another workbook.

Thread: Excel help, copying data to another workbook.

  1. Soulassassin's Avatar

    Soulassassin said:

    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. akimba's Avatar

    akimba said:

    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 :-(
     
  3. Mystical_2K's Avatar

    Mystical_2K said:

    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.
     
  4. Soulassassin's Avatar

    Soulassassin said:

    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. Mystical_2K's Avatar

    Mystical_2K said:

    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: [View]
    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.
     
  6. Soulassassin's Avatar

    Soulassassin said:

    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: [View]
    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. Mystical_2K's Avatar

    Mystical_2K said:

    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.
     
  8. Mystical_2K's Avatar

    Mystical_2K said:

    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.
     
  9. akimba's Avatar

    akimba said:

    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
     
  10. Soulassassin's Avatar

    Soulassassin said:

    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: [View]
    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. Soulassassin's Avatar

    Soulassassin said:

    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. akimba's Avatar

    akimba said:

    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
     
  13. Mystical_2K's Avatar

    Mystical_2K said:

    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.
     
  14. Soulassassin's Avatar

    Soulassassin said:

    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. akimba's Avatar

    akimba said:

    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 ;-)
     
  16. Soulassassin's Avatar

    Soulassassin said:

    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. akimba's Avatar

    akimba said:

    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. akimba's Avatar

    akimba said:

    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. akimba's Avatar

    akimba said:

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

    What is it you are manufacturing?
     
  20. Soulassassin's Avatar

    Soulassassin said:

    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.