Insert columns copied to clipboard results in vaues not forumlas

[mendozalaura]

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]

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]

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]

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]

THANK-YOU THANK-YOU COMPUTER GOD!!

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,

 

[Dave Peterson]

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.

THANK-YOU THANK-YOU COMPUTER GOD!!