Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert columns copied to clipboard results in vaues not forumlas
Okay,
This is a little twisted. Start with an already existing excel file. I need to open another existing workbook, copy columns L though BR, close the file without saving, then paste the columns from the clipboard into the original excel file. All of this works, I can turn off the alerts for "large amount of data pasted to clipboard etc" however when the columns are pasted from the clipboard into the original excel file, they are pasted as values and not forumlas. Am I making any sense, does anyone have ideas. Code so faris: Starting with the original excel shet already open... Then... Workbooks.Open Filename:= _ "\\Server\shared\temporary\Laura\Batch summary Sub Assembly.xls" Application.Goto Reference:="Sub_transfer" Selection.Copy Application.DisplayAlerts = False ActiveWindow.Close SaveChanges:=False Application.CutCopyMode = True Columns("L:L").Select ActiveSheet.Paste Application.DisplayAlerts = True End Sub All help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert columns copied to clipboard results in vaues not forumlas
You could add: application.cutcopymode = false
before you close the workbook. But that means you'll have to rearrange your code: dim RngToCopy as range dim DestCell as range set destcell = activesheet.range("L1") 'let excel resize it. Workbooks.Open Filename:= _ "\\Server\shared\temporary\Laura\Batch summary Sub Assembly.xls" set rngtocopy = activeworkbook.names("Sub_transfer").referstorange rngtocopy.copy _ destination:=destcell application.cutcopymode = false rngtocopy.parent.close savechanges:=false End Sub (untested, watch out for typos) mendozalaura wrote: Okay, This is a little twisted. Start with an already existing excel file. I need to open another existing workbook, copy columns L though BR, close the file without saving, then paste the columns from the clipboard into the original excel file. All of this works, I can turn off the alerts for "large amount of data pasted to clipboard etc" however when the columns are pasted from the clipboard into the original excel file, they are pasted as values and not forumlas. Am I making any sense, does anyone have ideas. Code so faris: Starting with the original excel shet already open... Then... Workbooks.Open Filename:= _ "\\Server\shared\temporary\Laura\Batch summary Sub Assembly.xls" Application.Goto Reference:="Sub_transfer" Selection.Copy Application.DisplayAlerts = False ActiveWindow.Close SaveChanges:=False Application.CutCopyMode = True Columns("L:L").Select ActiveSheet.Paste Application.DisplayAlerts = True End Sub All help would be appreciated. -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert columns copied to clipboard results in vaues not foruml
Hi Dave,
Works perfectly up to the last line "rngtocopy.parent.close savechanges:=false" at which point I get an Error 438 - Object does not support this property or method. Can you help? Thanks "Dave Peterson" wrote: You could add: application.cutcopymode = false before you close the workbook. But that means you'll have to rearrange your code: dim RngToCopy as range dim DestCell as range set destcell = activesheet.range("L1") 'let excel resize it. Workbooks.Open Filename:= _ "\\Server\shared\temporary\Laura\Batch summary Sub Assembly.xls" set rngtocopy = activeworkbook.names("Sub_transfer").referstorange rngtocopy.copy _ destination:=destcell application.cutcopymode = false rngtocopy.parent.close savechanges:=false End Sub (untested, watch out for typos) mendozalaura wrote: Okay, This is a little twisted. Start with an already existing excel file. I need to open another existing workbook, copy columns L though BR, close the file without saving, then paste the columns from the clipboard into the original excel file. All of this works, I can turn off the alerts for "large amount of data pasted to clipboard etc" however when the columns are pasted from the clipboard into the original excel file, they are pasted as values and not forumlas. Am I making any sense, does anyone have ideas. Code so faris: Starting with the original excel shet already open... Then... Workbooks.Open Filename:= _ "\\Server\shared\temporary\Laura\Batch summary Sub Assembly.xls" Application.Goto Reference:="Sub_transfer" Selection.Copy Application.DisplayAlerts = False ActiveWindow.Close SaveChanges:=False Application.CutCopyMode = True Columns("L:L").Select ActiveSheet.Paste Application.DisplayAlerts = True End Sub All help would be appreciated. -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert columns copied to clipboard results in vaues not foruml
Oops. I left out a parent!
rngtocopy.parent.parent.close savechanges:=false (the parent of the rngtocopy is the worksheet. That parent is the workbook.) Sorry, mendozalaura wrote: Hi Dave, Works perfectly up to the last line "rngtocopy.parent.close savechanges:=false" at which point I get an Error 438 - Object does not support this property or method. Can you help? Thanks "Dave Peterson" wrote: You could add: application.cutcopymode = false before you close the workbook. But that means you'll have to rearrange your code: dim RngToCopy as range dim DestCell as range set destcell = activesheet.range("L1") 'let excel resize it. Workbooks.Open Filename:= _ "\\Server\shared\temporary\Laura\Batch summary Sub Assembly.xls" set rngtocopy = activeworkbook.names("Sub_transfer").referstorange rngtocopy.copy _ destination:=destcell application.cutcopymode = false rngtocopy.parent.close savechanges:=false End Sub (untested, watch out for typos) mendozalaura wrote: Okay, This is a little twisted. Start with an already existing excel file. I need to open another existing workbook, copy columns L though BR, close the file without saving, then paste the columns from the clipboard into the original excel file. All of this works, I can turn off the alerts for "large amount of data pasted to clipboard etc" however when the columns are pasted from the clipboard into the original excel file, they are pasted as values and not forumlas. Am I making any sense, does anyone have ideas. Code so faris: Starting with the original excel shet already open... Then... Workbooks.Open Filename:= _ "\\Server\shared\temporary\Laura\Batch summary Sub Assembly.xls" Application.Goto Reference:="Sub_transfer" Selection.Copy Application.DisplayAlerts = False ActiveWindow.Close SaveChanges:=False Application.CutCopyMode = True Columns("L:L").Select ActiveSheet.Paste Application.DisplayAlerts = True End Sub All help would be appreciated. -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert columns copied to clipboard results in vaues not foruml
THANK-YOU THANK-YOU COMPUTER GOD!!
"Dave Peterson" wrote: Oops. I left out a parent! rngtocopy.parent.parent.close savechanges:=false (the parent of the rngtocopy is the worksheet. That parent is the workbook.) Sorry, mendozalaura wrote: Hi Dave, Works perfectly up to the last line "rngtocopy.parent.close savechanges:=false" at which point I get an Error 438 - Object does not support this property or method. Can you help? Thanks "Dave Peterson" wrote: You could add: application.cutcopymode = false before you close the workbook. But that means you'll have to rearrange your code: dim RngToCopy as range dim DestCell as range set destcell = activesheet.range("L1") 'let excel resize it. Workbooks.Open Filename:= _ "\\Server\shared\temporary\Laura\Batch summary Sub Assembly.xls" set rngtocopy = activeworkbook.names("Sub_transfer").referstorange rngtocopy.copy _ destination:=destcell application.cutcopymode = false rngtocopy.parent.close savechanges:=false End Sub (untested, watch out for typos) mendozalaura wrote: Okay, This is a little twisted. Start with an already existing excel file. I need to open another existing workbook, copy columns L though BR, close the file without saving, then paste the columns from the clipboard into the original excel file. All of this works, I can turn off the alerts for "large amount of data pasted to clipboard etc" however when the columns are pasted from the clipboard into the original excel file, they are pasted as values and not forumlas. Am I making any sense, does anyone have ideas. Code so faris: Starting with the original excel shet already open... Then... Workbooks.Open Filename:= _ "\\Server\shared\temporary\Laura\Batch summary Sub Assembly.xls" Application.Goto Reference:="Sub_transfer" Selection.Copy Application.DisplayAlerts = False ActiveWindow.Close SaveChanges:=False Application.CutCopyMode = True Columns("L:L").Select ActiveSheet.Paste Application.DisplayAlerts = True End Sub All help would be appreciated. -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert columns copied to clipboard results in vaues not foruml
I don't think gods make the kinds of mistakes (or the quantity of mistakes) that
I make. But I'm glad you're happy. mendozalaura wrote: THANK-YOU THANK-YOU COMPUTER GOD!! "Dave Peterson" wrote: Oops. I left out a parent! rngtocopy.parent.parent.close savechanges:=false (the parent of the rngtocopy is the worksheet. That parent is the workbook.) Sorry, mendozalaura wrote: Hi Dave, Works perfectly up to the last line "rngtocopy.parent.close savechanges:=false" at which point I get an Error 438 - Object does not support this property or method. Can you help? Thanks "Dave Peterson" wrote: You could add: application.cutcopymode = false before you close the workbook. But that means you'll have to rearrange your code: dim RngToCopy as range dim DestCell as range set destcell = activesheet.range("L1") 'let excel resize it. Workbooks.Open Filename:= _ "\\Server\shared\temporary\Laura\Batch summary Sub Assembly.xls" set rngtocopy = activeworkbook.names("Sub_transfer").referstorange rngtocopy.copy _ destination:=destcell application.cutcopymode = false rngtocopy.parent.close savechanges:=false End Sub (untested, watch out for typos) mendozalaura wrote: Okay, This is a little twisted. Start with an already existing excel file. I need to open another existing workbook, copy columns L though BR, close the file without saving, then paste the columns from the clipboard into the original excel file. All of this works, I can turn off the alerts for "large amount of data pasted to clipboard etc" however when the columns are pasted from the clipboard into the original excel file, they are pasted as values and not forumlas. Am I making any sense, does anyone have ideas. Code so faris: Starting with the original excel shet already open... Then... Workbooks.Open Filename:= _ "\\Server\shared\temporary\Laura\Batch summary Sub Assembly.xls" Application.Goto Reference:="Sub_transfer" Selection.Copy Application.DisplayAlerts = False ActiveWindow.Close SaveChanges:=False Application.CutCopyMode = True Columns("L:L").Select ActiveSheet.Paste Application.DisplayAlerts = True End Sub All help would be appreciated. -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
No indication that copied chart is on the clipboard | Charts and Charting in Excel | |||
Formula results to be copied into a different cell | New Users to Excel | |||
Copied formula produces unexpected copied results | New Users to Excel | |||
Insert row not coping all forumlas | Excel Discussion (Misc queries) | |||
How do I display copied autoshapes completely in clipboard | Charts and Charting in Excel |