Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Unanswered: Taking data from three workbooks combining into one workbook.

    Hello and please forgive me for asking such a lengthy question my first time on here but I have been given a task to write a macro that will do the following and I have no experience in macro writing. Here is what I need:

    I want to open a workbook and run a macro that will look for data in three different workbooks, take some of the data from each workbook and create a new workbook with this data.

    From Workbook one I need to start at column B row 8 and pull the data until it finds an empty cell in this column. I need to copy and paste the data from columns B, D, E, G, H, I, J, K and L. What I need to do I paste them into specific columns within the workbook I am saving them as follows. B to A, D to G, E to H, G to I, H to M, I to L, J to N, K to O and L to P.

    Next I need to open workbook 2 start at B8 again and do the same as above only coping the data in columns B, D, F and H. I also need to put this data into specific columns.
    B to A, D to B, F to G and H to K. I need to add this data to the data from above.

    I need to repeat this process for a third workbook. Then I would like to sort the new workbook by column A and save this as a new workbook.

    Being new to macro writing I told my boss I would need lots of help with this. Does someone have any code that does this type of thing so I can see how to do this?

    Thanks,

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    This is not a simple task!

    Before getting onto the code, there are a few things to consider:
    • Will the source workbooks always be the same?
    • Will the source books be in the same directory as each other?
    • Will the source lists always be the same size?

    Once you've established that, the macro will be easier to write.

    My gut instinct would be to write something that opens all the source files at once, combines the source values into total amounts and then dumps it in the destination.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Jul 2009
    Posts
    4

    That's what I said.

    That was what I said when asked to do this. I wanted something a little easier to start with. To answer your question, yes the spreadsheets will always have the same name and be stored in the same location. This is spreadsheet that is used once per week for a meeting and only the copy that is created will be saved long term, the other three will get over written every time they are created.

  4. #4
    Join Date
    Jul 2009
    Posts
    4

    Starting point.

    I have this set of code which I can run from the first spreadsheet and it will create the new spreadsheet. What I do not know how to do is modify the code so I can run this from the second spreadsheet and append to the spreadsheet this macro created.

    Dim startrow, endrow
    Dim r1 As Range, r As Range
    Dim bk As Workbook, sh As Worksheet
    Dim v As Variant, i As Long, ar As Range
    Dim fName As String, sh1 As Worksheet

    Set rng = Range(Range("B8"), _
    Cells(Rows.Count, 1).End(xlUp))

    Set sh1 = ActiveSheet
    startrow = InputBox("enter the start row number")
    endrow = InputBox("enter the ending row number")

    If Not IsNumeric(startrow) Then

    MsgBox "Invalid data"
    Exit Sub
    End If

    Set r1 = sh1.Range(sh1.Rows(startrow), sh1.Rows(endrow))
    Set r = Intersect(r1.EntireRow, sh1.Range("B:B,D,E:E,G:G,H:H,I:I,J:J,K:K,L:L"))
    Set bk = Workbooks.Open("C:\temp\test_spreadsheet.xls")
    Set sh = bk.Worksheets("sheet1")
    v = Array(1, 7, 8, 9, 13, 12, 14, 15, 16)
    i = LBound(v)

    For Each ar In r.Areas
    ' add code to find the first blank cell and start the paste on that row:
    ar.Copy
    sh.Cells(4, v(i)).PasteSpecial xlValues
    sh.Cells(4, v(i)).PasteSpecial xlFormats
    i = i + 1

    Next ar
    fName = Application.GetSaveAsFilename()
    bk.SaveAs fName
    bk.Close SaveChanges:=False
    End Sub

  5. #5
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Talking

    Right.

    Normally I frown on giving someone exactly what they ask for, but in this case it seemed to be the best course of action. Attached is an Excel 2000 workbook with two sheets and a command button. The command button is fully coded. Enter the required information in B1:B4 and click the button. There's very minimal error checking on it.

    The code is customisable, but as it stands, it will accept any three files as input provided that the source tables are all on the uppermost sheets, and that they all have the same number of rows. Once the values are compiled, the totals sheet is copied into a new workbook and saved as a new file. The totals are then cleared, ready for the next time.

    I've tried to comment the code as helpfully as possible. If you're not sure about anything, step through it to see what it does. If it still doesn't make sense, let me know.

    Hopefully this will point you in the right direction.
    Attached Files Attached Files
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  6. #6
    Join Date
    Jul 2009
    Posts
    4

    thank you,

    I actually do have to go through this code and make some changes as I made up the columns for the second and third spreadsheets. I am greatful for this help and have showed this to my boss and let them know this is not my work and I can take no credit for getting this to work.

  7. #7
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome!

    You will need to redo some of the looping structures if the columns aren't consistent across the source files. It might be worth creating a table within the "Control" worksheet to hold the column mappings, and point the sub there instead of hard-coded values.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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