Excel Macros - Help Needed

paddyrg

Do you have any choice about the file format? Just I think Excel actually sounds like the wrong tool for the job truth be told. Sending a workbook with sheets with a header row and one line of data sounds like the system could be reviewed and better options found with a bit of consideration.

What do we actually want to achieve? Do the people sending the sheets fill them in daily by hand? Or some kind of template? Would they fill in a web form instead? If so, you could set up a google docs spreadsheet with a 'form' in about 30 mins, and that does exactly what you describe, you would just send the form link to everyone, it would automatically add a new row each time for each form submission.

Or get the datagrams submitted in simple xml, or even csv files and have a bulk import job into a database

And a database sounds like the right place for this data to be going, not into rows in a worksheet. Or maybe, anyway, as I don't know the exact circumstances - but from my years in data migrations and data-aware applications, everything you describe feels wrong in my guts and suggests there is a much better answer out there for the sake of thinking about it a little.

rmg1

As a quick and very simple macro, this should go some way to solve your problem. I've assumed all workbooks will be open in the same instance of Excel.

Sub copy_across()
Dim wb1 As Workbook, wb2 As Workbook, ws1 As Worksheet, ws2 As Worksheet
Set wb1 = ThisWorkbook
Set ws1 = wb1.ActiveSheet
Set wb2 = Workbooks("Your_workbook_name")
Set ws2 = wb2.ActiveSheet
ws2.Rows("2:2").Copy Destination:=ws1.Range("A65535").End(xlUp).Offset(1, 0)
Application.CutCopyMode = False
Set ws1 = Nothing
Set ws2 = Nothing
Set wb1 = Nothing
Set wb2 = Nothing
End Sub

You'll need to change the name of "Book2" to whatever your workbook is called but that should give you basically what you want.

LucianH

rmg1 wrote: »

As a quick and very simple macro, this should go some way to solve your problem..

Nice one:

ws2.Rows("2:2").Copy Destination:=ws1.Range("A65535").End(xlUp).Offset( 1, 0)
Application.CutCopyMode = False

Very elegant

rmg1

LucianH - Thanks. You wouldn't believe how many times I've come across this scenario and this is what I've managed to pare my code down to.

It does actually help that the files aren't stored on a drive in the first place as you don't have to worry about trying to find them.