I have a master report dragging blocks of data via a macro, from man
individual order spreadsheets 001.xls, 002.xls etc.

My problem is that I have to continually open the master report an
take the ' out from infront of the macro line associated with ne
orders in order to allow the macro to open the new order spreadshee
and get the required data.

Is there some way I can get the macro to ignore the line of code for a
order number who's spreadsheet does not yet exist.

Okane

--
okane
-----------------------------------------------------------------------
okanem's Profile: http://www.excelforum.com/member.php...nfo&userid=930
View this thread: http://www.excelforum.com/showthread.php?threadid=55821

There is always (almost always) a way, but you will have to post your code...
--
HTH...

Jim Thomlinson


"okanem" wrote:


I have a master report dragging blocks of data via a macro, from many
individual order spreadsheets 001.xls, 002.xls etc.

My problem is that I have to continually open the master report and
take the ' out from infront of the macro line associated with new
orders in order to allow the macro to open the new order spreadsheet
and get the required data.

Is there some way I can get the macro to ignore the line of code for an
order number who's spreadsheet does not yet exist.

Okanem


--
okanem
------------------------------------------------------------------------
okanem's Profile: http://www.excelforum.com/member.php...fo&userid=9301
View this thread: http://www.excelforum.com/showthread...hreadid=558216

I managed to put the code per order sheet on one line so I can just
remove one ' to activate new orders that are on the system instad of
four or five '

Anyway, here is my code for the first few orders that are already
there. I have 10000 lines of code in the macro, each line accounting
for a new order number, from 50054.xls onward to 60000.xls are all
remed out using a ' before the Workbooks.Open command as I have
demonstrated for 50002.xls.

Workbooks.Open
Filename:="\\Ctserver\common\Hawk\System\Orders\50 000.xls",
ReadOnly:=True: Sheets("Hide").Visible = True: Sheets("Hide").Select:
Range("A4:BB4").Select: Selection.Copy:
Windows("hawkmasterrep.xls").Activate: Sheets("Data").Select:
Range("A4").Select: Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False:
Windows("50000.xls").Activate: Application.CutCopyMode = False:
ActiveWorkbook.Close (False)

Workbooks.Open
Filename:="\\Ctserver\common\Hawk\System\Orders\50 001.xls",
ReadOnly:=True: Sheets("Hide").Visible = True: Sheets("Hide").Select:
Range("A4:BB4").Select: Selection.Copy:
Windows("hawkmasterrep.xls").Activate: Sheets("Data").Select:
Range("A5").Select: Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False:
Windows("50001.xls").Activate: Application.CutCopyMode = False:
ActiveWorkbook.Close (False)

' Workbooks.Open
Filename:="\\Ctserver\common\Hawk\System\Orders\50 002.xls",
ReadOnly:=True: Sheets("Hide").Visible = True: Sheets("Hide").Select:
Range("A4:BB4").Select: Selection.Copy:
Windows("hawkmasterrep.xls").Activate: Sheets("Data").Select:
Range("A6").Select: Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False:
Windows("50002.xls").Activate: Application.CutCopyMode = False:
ActiveWorkbook.Close (False)


All they do is simply open a read only 50000.xls or 50001.xls etc then
unhide a sheet called 'Hide', copy and paste special one line of data
(A4 to BB4) into consecutive rows in the hawkmasterreport.xls, then it
closes the 5000?.xls sheet.

So what I am after is if the order sheet, lets say 50008.xls, isnt in
the orders folder then the macro does not attempt to open it without me
having to go in and make sure the line has a ' at the start of it.

Hope someone can help.

Rgds
Okanem


--
okanem
------------------------------------------------------------------------
okanem's Profile: http://www.excelforum.com/member.php...fo&userid=9301
View this thread: http://www.excelforum.com/showthread...hreadid=558216

May be you need use a Application.GetOpenFileName(Multiselect=True)
Or use Dir("*.xls) on the requirted folder to list all.

Once you have your filelist, just Loop your code to process each one.

NickHK

P.S. I'm sure you have a good reason to put all your code on a single with
":", but it makes it incredibly unreadable.
Do you have something against whitespace ?

"okanem" wrote in
message ...

I managed to put the code per order sheet on one line so I can just
remove one ' to activate new orders that are on the system instad of
four or five '

Anyway, here is my code for the first few orders that are already
there. I have 10000 lines of code in the macro, each line accounting
for a new order number, from 50054.xls onward to 60000.xls are all
remed out using a ' before the Workbooks.Open command as I have
demonstrated for 50002.xls.

Workbooks.Open
Filename:="\\Ctserver\common\Hawk\System\Orders\50 000.xls",
ReadOnly:=True: Sheets("Hide").Visible = True: Sheets("Hide").Select:
Range("A4:BB4").Select: Selection.Copy:
Windows("hawkmasterrep.xls").Activate: Sheets("Data").Select:
Range("A4").Select: Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False:
Windows("50000.xls").Activate: Application.CutCopyMode = False:
ActiveWorkbook.Close (False)

Workbooks.Open
Filename:="\\Ctserver\common\Hawk\System\Orders\50 001.xls",
ReadOnly:=True: Sheets("Hide").Visible = True: Sheets("Hide").Select:
Range("A4:BB4").Select: Selection.Copy:
Windows("hawkmasterrep.xls").Activate: Sheets("Data").Select:
Range("A5").Select: Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False:
Windows("50001.xls").Activate: Application.CutCopyMode = False:
ActiveWorkbook.Close (False)

' Workbooks.Open
Filename:="\\Ctserver\common\Hawk\System\Orders\50 002.xls",
ReadOnly:=True: Sheets("Hide").Visible = True: Sheets("Hide").Select:
Range("A4:BB4").Select: Selection.Copy:
Windows("hawkmasterrep.xls").Activate: Sheets("Data").Select:
Range("A6").Select: Selection.PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=False:
Windows("50002.xls").Activate: Application.CutCopyMode = False:
ActiveWorkbook.Close (False)


All they do is simply open a read only 50000.xls or 50001.xls etc then
unhide a sheet called 'Hide', copy and paste special one line of data
(A4 to BB4) into consecutive rows in the hawkmasterreport.xls, then it
closes the 5000?.xls sheet.

So what I am after is if the order sheet, lets say 50008.xls, isnt in
the orders folder then the macro does not attempt to open it without me
having to go in and make sure the line has a ' at the start of it.

Hope someone can help.

Rgds
Okanem


--
okanem
------------------------------------------------------------------------
okanem's Profile:
http://www.excelforum.com/member.php...fo&userid=9301
View this thread: http://www.excelforum.com/showthread...hreadid=558216

Nick, thanks for the reply, its not the white space I dislike, merely
the number of ' I have to delete if the code per order is multi line.

Can you elaborate on the Application.GetOpenFileName(Multiselect=True)
and show me how to incorporate it in my code.

Rgds
Okanem


--
okanem
------------------------------------------------------------------------
okanem's Profile: http://www.excelforum.com/member.php...fo&userid=9301
View this thread: http://www.excelforum.com/showthread...hreadid=558216

Not tested at all but something along these lines:

Private Sub CommandButton4_Click()
Dim FileNames As Variant
Dim FileCount as Long
Dim DestWB As Workbook

Set DestWB = Application.Workbooks.Open("hawkmasterrep.xls") 'Add full
path

FileNames =GetOpenFileName(Multiselect=true)
If filenames<false then
For FileCount=0 to UBound(filenames)
With Workbooks.Open(Filenames(filecount), ReadOnly:=True)
.Sheets("Hide").Visible = True
.Sheets("Hide").Range("A4:BB4").Copy

DestWB.Sheets("Data").Range("A2").Offset(filecount ,
0).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
.Close (False)
End With
FileCount=FileCount+1
loop
end if

NickHK

"okanem" wrote in
message ...

Nick, thanks for the reply, its not the white space I dislike, merely
the number of ' I have to delete if the code per order is multi line.

Can you elaborate on the Application.GetOpenFileName(Multiselect=True)
and show me how to incorporate it in my code.

Rgds
Okanem


--
okanem
------------------------------------------------------------------------
okanem's Profile:
http://www.excelforum.com/member.php...fo&userid=9301
View this thread: http://www.excelforum.com/showthread...hreadid=558216