Hanging on sheet close
 

I'm using the following code

With
Workbooks("BillDispositionDetail-r.html").Sheets("BillDispositionDetail-r")
.Range(.Cells(1, 1), .Cells(ReadyRows, 18)).Copy
End With

Workbooks("BD-Master.xls").Sheets("BDData").Cells(1, 1).PasteSpecial
Paste:=xlPasteValues

With Workbooks("BD-Master.xls").Sheets("BDData")
.Range(.Cells(3, 18), .Cells(ReadyRows, 18)).Value = "Ready"
End With

Workbooks("BillDispositionDetail-r.html").Close


When I run it on a small amount of sample data, approx a 150k file it works
fine. When I use production data, 6-7 mb files it hangs on the
workbooks.close

Above this I have Application.DisplayAlerts = False

I believe that it hangs because of the normal dialog saying you've placed a
large amount of data on the clipboard. Is there some way around this or do
I need to change my code so that it closes the source files after formatting
the tables?

Jesse

Hanging on sheet close
 

Jesse,

You could try clearing the clipboard with:

Application.CutCopyMode = False

hth,

Doug

"Jesse" wrote in message
news:EJyzb.2767$d35.622@edtnps84...
I'm using the following code

With

Workbooks("BillDispositionDetail-r.html").Sheets("BillDispositionDetail-r")
.Range(.Cells(1, 1), .Cells(ReadyRows, 18)).Copy
End With

Workbooks("BD-Master.xls").Sheets("BDData").Cells(1, 1).PasteSpecial
Paste:=xlPasteValues

With Workbooks("BD-Master.xls").Sheets("BDData")
.Range(.Cells(3, 18), .Cells(ReadyRows, 18)).Value = "Ready"
End With

Workbooks("BillDispositionDetail-r.html").Close


When I run it on a small amount of sample data, approx a 150k file it
works
fine. When I use production data, 6-7 mb files it hangs on the
workbooks.close

Above this I have Application.DisplayAlerts = False

I believe that it hangs because of the normal dialog saying you've placed
a
large amount of data on the clipboard. Is there some way around this or
do
I need to change my code so that it closes the source files after
formatting
the tables?

Jesse

Hanging on sheet close
 

This worked great Doug, thanks.

Jesse



"Doug Glancy" wrote in message
...
Jesse,

You could try clearing the clipboard with:

Application.CutCopyMode = False

hth,

Doug

"Jesse" wrote in message
news:EJyzb.2767$d35.622@edtnps84...
I'm using the following code

With


Workbooks("BillDispositionDetail-r.html").Sheets("BillDispositionDetail-r")
.Range(.Cells(1, 1), .Cells(ReadyRows, 18)).Copy
End With

Workbooks("BD-Master.xls").Sheets("BDData").Cells(1, 1).PasteSpecial
Paste:=xlPasteValues

With Workbooks("BD-Master.xls").Sheets("BDData")
.Range(.Cells(3, 18), .Cells(ReadyRows, 18)).Value = "Ready"
End With

Workbooks("BillDispositionDetail-r.html").Close


When I run it on a small amount of sample data, approx a 150k file it
works
fine. When I use production data, 6-7 mb files it hangs on the
workbooks.close

Above this I have Application.DisplayAlerts = False

I believe that it hangs because of the normal dialog saying you've
placed
a
large amount of data on the clipboard. Is there some way around this or
do
I need to change my code so that it closes the source files after
formatting
the tables?

Jesse