Excel Gurus.. Plz halp.

[pabstblueribbon]

Other than simple excel sheets.. this is mostly out of my scope for what I do for work. I suck at pivot tables in excel. At least, thats what I think I need for this to work. I've read lots of wikis but I just can't seem to figure out what I need to do.

Basically I have this main sheets:

Schematic Schedule

Then I have more sheets or tabs in the same excel file:

Amtrak, Bnsf, Up, etc

In the columns of the tabs labeled amtrak, etc, I have fields like, railroad, road number, date requested, preliminary date, etcetera.

I want it to copy over the information in these tables to a corresponding column in the main Schematic Schedule Sheet. But the 'row' needs to be dynamic. Basically when any column is filled out on the tabs, I want it to populate the row when it has data..

I've been playing around with it, and I can get it to copy one instance of the field with =Amtrak!$A:$A

But I want it to pull from every railroad as they are filled in and I'm just not sure how to make it do that since the source field is going to differ from the Schematic Schedule field when multiple railroad fields start being populated..

Meh.. halp.

[pabstblueribbon]

So basically what I want to do is copy rows from multiple worksheets in the same workbook to one master sheet and place the data in the next available row.

[g++]

Unless im reading this wrong I think just going to the top of the sheet and typing out...

=Amtrak!1A:1A in the first cell and
=Amtrak!2A:2A in the second cell

should allow you to then fill the column with dynamically assigned variables. Not exactly "pro" but it should work.

[pabstblueribbon]

Hmm maybe I should elaborate.

What I need to do: Copy rows from multiple worksheets in the same workbook to one master sheet (Schematic Schedule) and place the data in the next available row.

I have Amtrak, BNSF, CN, CP, CSXT, KCS, Metrolink, NS, UP for my multiple worksheets. My main tracking worksheet is named Schematic Schedule.

Inside Amtrak, BNSF, CN, CP, CSXT, KCS, Metrolink, NS, UP I have these columns NOT rows (I listed them this way so that it was easier to read):
Customer
RN Range
Model Air Brake
Cab Signal
Request Date
Loco Schematic Number
Locomotive Schematic
Required Delivery Date
Wabtec Drawing Number
Wabtec PTC Schematic Prelim
Prelim Complete?
Prelim Delivered
Prelim Test
Rev A to SharePoint
PTC Scematic Rev A

I need to copy over the data, in these rows, in only these columns, to the Schematic Schedule worksheet to the next available row in that sheet:
Customer
Request Date
Prelim Delivered
Prelim Test
Rev A to Sharepoint

I'm sure this is very simple, I'm just ritarded. I've been pouring over forums and trying VBA, consolidate tool, and pivot table examples to very little avail.

I've included an example spreadsheet, I only filled out two rows for amtrak and bnsf and showed on the schematic schedule worksheet how I'd like them displayed. I just need the data to show up on the first page, then I can use filters/sort to sort them however I want.

[g++]

It sounds like you need a vba function. I avoid doing shit like this at work and they pay me so theres little hope of me doing it for you.

This article is very similiar though.

http://www.mrexcel.com/articles/copy-to-next-blank-row.php

Thats kind of what you want to do.

[pabstblueribbon]

It sounds like you need a vba function. I avoid doing shit like this at work and they pay me so theres little hope of me doing it for you.

This article is very similiar though.

http://www.mrexcel.com/articles/copy-to-next-blank-row.php

Thats kind of what you want to do.

Yeah, its a pain in the ass. Fuck this shit.

[g++]

Dont worry Celephalis will do it eventually. He still thinks programming is fun.

[pabstblueribbon]

Dont worry Celephalis will do it eventually. He still thinks programming is fun.

Heh. Well, I got this working. Unfortunately it copies every column. But it does build the master sheet like I want it too, it just does it for every column. Googled and googled on how to remove columns and select just by column and I'm just fail..


Sub Combine()
Dim J As Integer

On Error Resume Next
Sheets(1).Select
Worksheets.Add ' add a sheet in first place
Sheets(1).Name = "Combined"



' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A2").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select

' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
Next
End Sub

[g++]

Unfortunately the functions your using copy the whole sheet. You could use the function that dude is using for each column you dont want


If you dont want columns 2 and 3 i think

Selection.Resize(Selection.Column.2).Select
Selection.Resize(Selection.Column.3).Select

might get rid of them. If you follow that line of thought youl eventually get what you want i imagine

[pabstblueribbon]

Weeerrrrrd.

[pabstblueribbon]

So I ended up figuring it out.


Sub Combine()
Application.ScreenUpdating = False
Dim J As Integer



Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets("Schematic Schedule")
Newsh.Rows("1:" & Newsh.Rows.Count).Clear



' work through sheets
For J = 2 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active
Range("A2").Select
Selection.CurrentRegion.Select ' select all cells in this sheets

' select all lines except title
Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select



' copy cells selected in the new sheet on last line
Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)

Next
Range("A1:P1").Select
Range("P1").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Schematic Schedule").Select
Range("A1:P1").Select
Range("P1").Activate
ActiveSheet.Paste


Range("B:E,G:H,J:L,N:N,P:P").Select
Selection.Delete Shift:=xlToLeft
Range("A1").Select
Application.ScreenUpdating = True



End Sub


This does exactly what I want, and I put a little button up at the top to basically run the macro and update the schedule sheet. There is an event called Worksheet_change event that will allow me to get rid of the button and will execute any time a cell changes.

But I'm unsure how I apply it to every worksheet... anyone familiar with this and know how to use it?