Code clean up for workbook to workbook copy

[bowhunt24]

Hello...I have two similar workbooks. One will be distributed to various people and one will be a master. On a weekly basis all the individual sheets will be sent to the controller and will be consolidated into the master sheet....working on a macro that will be embedded in the sheet so that the controller can hit a button and easily move all the data over to the master sheet (of course assuming that both workbooks are open).

Here is the code I have to start with:

Sub MOVE()
'
' MOVE Macro
'

'
    Range("B5:O9").Select
    Selection.Copy
    Windows("MASTER NE METRO SB TRACKER ver 1.3.xlsm").Activate
    Application.WindowState = xlMinimized
    Range("B9").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B5").Select
    Windows("NE METRO SB TRACKER ver 1.3.xlsm").Activate
End Sub

A couple things I would like for it to do that I can't figure out how to program...
1) Copy any entered data, excluding blanks. Each sheet will have a different amount of "leads" so I can't just set it to copy the same range every time.

2) When it copies to the Master Sheet, it starts copying at the first available slot. Again, I can't set it to start pasting to a certain cell as that cell may already be full with data from a previous move.

Hope this makes sense! I've uploaded both sheets too to make it easier.

Thanks for all the help
Evan

[arthurbr]

Also read the red couloured sentence in my sig...

[bowhunt24]

Wow...I'm zero for two today. Good work me.....

Sorry again...

[bowhunt24]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Sorry....I'll fixed up. I'll make sure to do that in the future. I'm a n00b!!!!! Ahhhhh!!!!