Retaining data on clipboard after close without prompt.

R

rUSSrEX

I am having trouble getting the clipboard to retain data without asking the
user if he wants to save it on close.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub Inventory_Upload_CleanUp()
'Sub Delete_M_Series()
'
' Delete_M_Series Macro
' Macro recorded 8/9/2006 by Russell
'
Application.DisplayAlerts = False

Columns("B:B").Select
Selection.Delete Shift:=xlLeft
Columns("C:E").Select
Selection.Delete Shift:=xlLeft
Columns(3).Select
Selection.NumberFormat = "0"


i = 1
While Cells(, 3) <> ""
If Cells(, 3) < 0 Then
Rows().Select
Selection.Delete Shift:=xlUp
Else
i = i + 1
End If
Wend


Cells.Find(What:="M10217SHD", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete

'------------------------------
'text to columns
'------------------------------
Columns("A:A").Select
Selection.Copy
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1)), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll Down:=426
ActiveWindow.ScrollRow = 426
ActiveWindow.ScrollRow = 423
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 413
ActiveWindow.ScrollRow = 401
ActiveWindow.ScrollRow = 376
ActiveWindow.ScrollRow = 363
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 323
ActiveWindow.ScrollRow = 310
ActiveWindow.ScrollRow = 281
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
Selection.ColumnWidth = 6.71
Selection.ColumnWidth = 7.14
Range("B1").Select
ActiveCell.FormulaR1C1 = "Finish"
Range("B2").Select
Columns("B:B").ColumnWidth = 10.86
Range("D1").Select
ActiveCell.FormulaR1C1 = "QTY"
Columns("D:D").Select
Selection.ColumnWidth = 5.14
Range("E2").Select

'------------------------------
'select copy and close excel
'------------------------------

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Application.CutCopyMode = True

Sheets("Sheet1").Name = "Inventory"

ChDir "C:\BrambleNow"
ActiveWorkbook.SaveAs filename:="C:\Documents and
Settings\Owner\Desktop\Inventory_Upload.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close

End Sub

The users need to retain the data on the clip board so that it can be pasted
into another application.

Thank you to anyone who can help with what I a sure is a simple question for
someone who knows what they are doing unlike myself.

Thank you,
Russell
 
D

Dave Peterson

There's lots of things that clear the cutcopymode.

This line cleared it for me:
Application.CutCopyMode = True
(I'd delete that line)

And if I recall correctly, saving will also do this.

I think I'd change the order so that the .copy is done after the save.

(I didn't test that last portion, but that's the way it works when I do it
manually.)
I am having trouble getting the clipboard to retain data without asking the
user if he wants to save it on close.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub Inventory_Upload_CleanUp()
'Sub Delete_M_Series()
'
' Delete_M_Series Macro
' Macro recorded 8/9/2006 by Russell
'
Application.DisplayAlerts = False

Columns("B:B").Select
Selection.Delete Shift:=xlLeft
Columns("C:E").Select
Selection.Delete Shift:=xlLeft
Columns(3).Select
Selection.NumberFormat = "0"


i = 1
While Cells(, 3) <> ""
If Cells(, 3) < 0 Then
Rows().Select
Selection.Delete Shift:=xlUp
Else
i = i + 1
End If
Wend


Cells.Find(What:="M10217SHD", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete

'------------------------------
'text to columns
'------------------------------
Columns("A:A").Select
Selection.Copy
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1)), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll Down:=426
ActiveWindow.ScrollRow = 426
ActiveWindow.ScrollRow = 423
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 413
ActiveWindow.ScrollRow = 401
ActiveWindow.ScrollRow = 376
ActiveWindow.ScrollRow = 363
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 323
ActiveWindow.ScrollRow = 310
ActiveWindow.ScrollRow = 281
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
Selection.ColumnWidth = 6.71
Selection.ColumnWidth = 7.14
Range("B1").Select
ActiveCell.FormulaR1C1 = "Finish"
Range("B2").Select
Columns("B:B").ColumnWidth = 10.86
Range("D1").Select
ActiveCell.FormulaR1C1 = "QTY"
Columns("D:D").Select
Selection.ColumnWidth = 5.14
Range("E2").Select

'------------------------------
'select copy and close excel
'------------------------------

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Application.CutCopyMode = True

Sheets("Sheet1").Name = "Inventory"

ChDir "C:\BrambleNow"
ActiveWorkbook.SaveAs filename:="C:\Documents and
Settings\Owner\Desktop\Inventory_Upload.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close

End Sub

The users need to retain the data on the clip board so that it can be pasted
into another application.

Thank you to anyone who can help with what I a sure is a simple question for
someone who knows what they are doing unlike myself.

Thank you,
Russell
Click to expand...
 
R

rUSSrEX

Thanks Dave! Issue resolved.

Here are the changes that were made.
-------------------------------------------------------

ChDir "C:\BrambleNow"
ActiveWorkbook.SaveAs filename:="C:\Documents and
Settings\Owner\Desktop\Inventory_Upload.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False


Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

ActiveWindow.Close

End Sub
-------------------------------------------------------


Dave Peterson said:
There's lots of things that clear the cutcopymode.

This line cleared it for me:
Application.CutCopyMode = True
(I'd delete that line)

And if I recall correctly, saving will also do this.

I think I'd change the order so that the .copy is done after the save.

(I didn't test that last portion, but that's the way it works when I do it
manually.)
I am having trouble getting the clipboard to retain data without asking the
user if he wants to save it on close.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub Inventory_Upload_CleanUp()
'Sub Delete_M_Series()
'
' Delete_M_Series Macro
' Macro recorded 8/9/2006 by Russell
'
Application.DisplayAlerts = False

Columns("B:B").Select
Selection.Delete Shift:=xlLeft
Columns("C:E").Select
Selection.Delete Shift:=xlLeft
Columns(3).Select
Selection.NumberFormat = "0"


i = 1
While Cells(, 3) <> ""
If Cells(, 3) < 0 Then
Rows().Select
Selection.Delete Shift:=xlUp
Else
i = i + 1
End If
Wend


Cells.Find(What:="M10217SHD", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.EntireRow.Delete

'------------------------------
'text to columns
'------------------------------
Columns("A:A").Select
Selection.Copy
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("A1"),
DataType:=xlFixedWidth, _
FieldInfo:=Array(Array(0, 1), Array(5, 1)), TrailingMinusNumbers:=True
ActiveWindow.SmallScroll Down:=426
ActiveWindow.ScrollRow = 426
ActiveWindow.ScrollRow = 423
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 413
ActiveWindow.ScrollRow = 401
ActiveWindow.ScrollRow = 376
ActiveWindow.ScrollRow = 363
ActiveWindow.ScrollRow = 337
ActiveWindow.ScrollRow = 323
ActiveWindow.ScrollRow = 310
ActiveWindow.ScrollRow = 281
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 239
ActiveWindow.ScrollRow = 228
ActiveWindow.ScrollRow = 217
ActiveWindow.ScrollRow = 196
ActiveWindow.ScrollRow = 186
ActiveWindow.ScrollRow = 162
ActiveWindow.ScrollRow = 150
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 125
ActiveWindow.ScrollRow = 118
ActiveWindow.ScrollRow = 109
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 65
ActiveWindow.ScrollRow = 57
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 45
ActiveWindow.ScrollRow = 36
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 27
ActiveWindow.ScrollRow = 25
ActiveWindow.ScrollRow = 23
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
Selection.ColumnWidth = 6.71
Selection.ColumnWidth = 7.14
Range("B1").Select
ActiveCell.FormulaR1C1 = "Finish"
Range("B2").Select
Columns("B:B").ColumnWidth = 10.86
Range("D1").Select
ActiveCell.FormulaR1C1 = "QTY"
Columns("D:D").Select
Selection.ColumnWidth = 5.14
Range("E2").Select

'------------------------------
'select copy and close excel
'------------------------------

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Application.CutCopyMode = True

Sheets("Sheet1").Name = "Inventory"

ChDir "C:\BrambleNow"
ActiveWorkbook.SaveAs filename:="C:\Documents and
Settings\Owner\Desktop\Inventory_Upload.xls", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="",
ReadOnlyRecommended:=False _
, CreateBackup:=False
ActiveWindow.Close

End Sub

The users need to retain the data on the clip board so that it can be pasted
into another application.

Thank you to anyone who can help with what I a sure is a simple question for
someone who knows what they are doing unlike myself.

Thank you,
Russell
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

variable number of rows. 3
Macro Error 6
Need Help with VBA code for an Excel Model Macro 5
Macro Saving 1
Macro Help 0
Help! 1
PLEASE HELP...How do I update a pivot table with VBA? 0
Problem with Macro 7

Top