Macro - IF command

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

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\50000.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\50001.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\50002.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

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" <okanem.2agobn_1152084302.5327@excelforum-nospam.com> wrote in
message news:okanem.2agobn_1152084302.5327@excelforum-nospam.com...
>
> 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\50000.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\50001.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\50002.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

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" <okanem.2agvhz_1152093604.8513@excelforum-nospam.com> wrote in
message news:okanem.2agvhz_1152093604.8513@excelforum-nospam.com...
>
> 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
>