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,

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.

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.

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

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.

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.

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.