Our boss gave us this project and said "I want this by Monday"...basi
background:

we're a car company and so what we'd like to do is have each salesma
have a worksheet that they enter various data into each day and the
every morning, the sales manager can open another worksheet tha
automatically compiles the previous day's data from the individua
sales person's worksheets. We want the individual workbooks protecte
because these guys have a habit of playing pranks on each other and i
would not be unlike them to screw with each other's workbooks if the
could.

Problem is...while the two of us working on this are pretty exce
savvy, our VBA knowledge is very limited.

We have this code:

Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveWindow.SmallScroll Down:=-18
ActiveWindow.Panes(3).Activate
Range("C309").Select
ActiveWindow.SmallScroll Down:=-312
Windows("Sales 1.xls").Activate
Windows("Sales MGR Tracking board.xls").Activate
Range("B6").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=42
Range("B51").Select
Windows("Sales 1.xls").Activate
ActiveWindow.Close
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
Range("C55").Select
ActiveWindow.SmallScroll Down:=51
Range("B96").Select
Windows("Sales 2.xls").Activate
ActiveWindow.Close
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
Range("B102").Select
ActiveWindow.SmallScroll Down:=39
Range("B141").Select
Windows("Sales 3.xls").Activate
ActiveWindow.Close
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=42
Range("B186").Select
Windows("Sales 4.xls").Activate
ActiveWindow.Close
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=45
Range("B231").Select
Windows("Sales 5.xls").Activate
ActiveWindow.Close
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
Windows("Sales 6.xls").Activate
ActiveWindow.Close
Range("B230").Select
ActiveWindow.SmallScroll Down:=48
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
Range("C281").Select
Windows("Sales 7.xls").Activate
ActiveWindow.Close
Range("D279").Select
End Sub

BUT, as is, we get the error *Run-time error '9': Subscript out o
range. * When we unprotect all the workbooks, then that error goes awa
but then it still won't run unless all of the workbooks are open first


Is there a way to write the code to include the individual workboo
passwords, and a prompt to open them first (or write the code in such
way that they don't need to be open first in order to run correctly?)

I know this is long...don't even know if it makes sense. that's part o
my problem... I don't know enough about VBA to even know if I'
articulating my problem/need correctly.

*-ANY*- Help would be greatly appreciated

--
Message posted from http://www.ExcelForum.com

There's not enough information to be certain of what you're doing there.

Just examining your code, here's some handy tips!

Range("B2:AI43").Select
Selection.Copy

can be rewritten as
Range("B2:AI43").Copy

ActiveWindow.SmallScroll Down:=-18 can be omitted, it's just visual fluff
which doesn't do data stuff.

If you want it to work so that you start off without any workbooks open then
that's when you should start macro recording.

While recording, try to minimise the amount of actions you perform.

When in doubt, click on the word then press F1.

Try to record a macro for just one workbook, then try to modify that macro
to suit the others.

These are all just suggestions, use at your discretion

Rob



"MissJen " wrote in message
...
Our boss gave us this project and said "I want this by Monday"...basic
background:

we're a car company and so what we'd like to do is have each salesman
have a worksheet that they enter various data into each day and then
every morning, the sales manager can open another worksheet that
automatically compiles the previous day's data from the individual
sales person's worksheets. We want the individual workbooks protected
because these guys have a habit of playing pranks on each other and it
would not be unlike them to screw with each other's workbooks if they
could.

Problem is...while the two of us working on this are pretty excel
savvy, our VBA knowledge is very limited.

We have this code:

Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveWindow.SmallScroll Down:=-18
ActiveWindow.Panes(3).Activate
Range("C309").Select
ActiveWindow.SmallScroll Down:=-312
Windows("Sales 1.xls").Activate
Windows("Sales MGR Tracking board.xls").Activate
Range("B6").Select
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=42
Range("B51").Select
Windows("Sales 1.xls").Activate
ActiveWindow.Close
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
Range("C55").Select
ActiveWindow.SmallScroll Down:=51
Range("B96").Select
Windows("Sales 2.xls").Activate
ActiveWindow.Close
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
Range("B102").Select
ActiveWindow.SmallScroll Down:=39
Range("B141").Select
Windows("Sales 3.xls").Activate
ActiveWindow.Close
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=42
Range("B186").Select
Windows("Sales 4.xls").Activate
ActiveWindow.Close
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=45
Range("B231").Select
Windows("Sales 5.xls").Activate
ActiveWindow.Close
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
Windows("Sales 6.xls").Activate
ActiveWindow.Close
Range("B230").Select
ActiveWindow.SmallScroll Down:=48
Range("B2:AI43").Select
Selection.Copy
Windows("Sales MGR Tracking board.xls").Activate
ActiveSheet.Paste
Range("C281").Select
Windows("Sales 7.xls").Activate
ActiveWindow.Close
Range("D279").Select
End Sub

BUT, as is, we get the error *Run-time error '9': Subscript out of
range. * When we unprotect all the workbooks, then that error goes away
but then it still won't run unless all of the workbooks are open first.


Is there a way to write the code to include the individual workbook
passwords, and a prompt to open them first (or write the code in such a
way that they don't need to be open first in order to run correctly?)

I know this is long...don't even know if it makes sense. that's part of
my problem... I don't know enough about VBA to even know if I'm
articulating my problem/need correctly.

*-ANY*- Help would be greatly appreciated!


---
Message posted from http://www.ExcelForum.com/