Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to Open and Compile Multiple Worksheets that are password protected
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PAssword protected file will not open | Excel Discussion (Misc queries) | |||
Excel macro to open a password protected workbook | Excel Discussion (Misc queries) | |||
Password Protect Macro - Compile Error | Excel Discussion (Misc queries) | |||
Excel Macro to open password protected workbooks? | Excel Discussion (Misc queries) | |||
open Access DB that is password protected | Excel Programming |