Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste special syntax Book to Book
Copy RngFrm in workbook A to RngTo in workbook B.
RngFrm and RngTo are from InputBoxes. RngFrm.Copy RngTo All this works just fine. Want to add the PasteSpecial.Values because RngFrm is formulas and I want RngTo as values only. I've tried With RngTo / End With but cannot get the proper syntax PasteSpecial in without errors. Thanks. Howard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste special syntax Book to Book
Copy RngFrm in workbook A to RngTo in workbook B.
RngFrm and RngTo are from InputBoxes. RngFrm.Copy RngTo All this works just fine. Want to add the PasteSpecial.Values because RngFrm is formulas and I want RngTo as values only. I've tried With RngTo / End With but cannot get the proper syntax PasteSpecial in without errors. Thanks. Howard Just assign the value directly... RngTo = RngFrm -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste special syntax Book to Book
Just assign the value directly... RngTo = RngFrm -- Garry Well, I sure thought would work, but there must be something upstream missing. I tried a RngFrm.Copy just below the first InputBox but that did not work either. Here is the whole code. Howard Sub CopyBookToBook() Dim RngFrm As Range Dim RngTo As Range Set RngFrm = Application.InputBox(Prompt:="Enter a Copy from Range.", _ Title:="Enter Copy from Column", Type:=8) If RngFrm Is Nothing Then Exit Sub 'RngFrm.Copy Workbooks.Open Filename:= _ "C:\Users\Howard Kittle\Documents\Copy To This Book.xlsm" Application.Goto Workbooks("Copy To This Book").Sheets("Sheet1").Range("A1") Set RngTo = Application.InputBox(Prompt:="Enter a Copy to Range.", _ Title:="Enter Copy to Column", Type:=8) If RngTo Is Nothing Then Exit Sub MsgBox RngTo.Address RngTo = RngFrm ActiveWorkbook.Save 'ActiveWorkbook.Close End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste special syntax Book to Book
Just assign the value directly... RngTo = RngFrm -- Garry Well, I sure thought would work, but there must be something upstream missing. I tried a RngFrm.Copy just below the first InputBox but that did not work either. Here is the whole code. Howard Sub CopyBookToBook() Dim RngFrm As Range Dim RngTo As Range Set RngFrm = Application.InputBox(Prompt:="Enter a Copy from Range.", _ Title:="Enter Copy from Column", Type:=8) If RngFrm Is Nothing Then Exit Sub 'RngFrm.Copy Workbooks.Open Filename:= _ "C:\Users\Howard Kittle\Documents\Copy To This Book.xlsm" Application.Goto Workbooks("Copy To This Book").Sheets("Sheet1").Range("A1") Set RngTo = Application.InputBox(Prompt:="Enter a Copy to Range.", _ Title:="Enter Copy to Column", Type:=8) If RngTo Is Nothing Then Exit Sub MsgBox RngTo.Address RngTo = RngFrm ActiveWorkbook.Save 'ActiveWorkbook.Close End Sub Works for me! Note that both ranges must be the same size/shape... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste special syntax Book to Book
Works for me! Note that both ranges must be the same size/shape... -- Garry Okay, something sinister is messing with me. I use mouse and select D1:D10 for first input. Use mouse and select the same range for the second input on the destination sheet OK = nothing! Code is in a standard module. Before changing the RngTo = RngFrm from RngFrm.Copy RngTo all I had to do was select a single cell, and it would be the upper left cell of any size range. I need a bigger hammer!! Howard |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste special syntax Book to Book
Hi Howard,
Am Sun, 20 Jul 2014 20:28:45 -0700 (PDT) schrieb L. Howard: Before changing the RngTo = RngFrm from RngFrm.Copy RngTo all I had to do was select a single cell, and it would be the upper left cell of any size range. try: Sub CopyBookToBook() Dim RngFrm As Range Dim RngTo As Range Dim arrFrm As Variant Set RngFrm = Application.InputBox(Prompt:="Enter a Copy from Range.", _ Title:="Enter Copy from Column", Type:=8) If RngFrm Is Nothing Then Exit Sub arrFrm = RngFrm 'Workbooks.Open Filename:= _ "C:\Users\Howard Kittle\Documents\Copy To This Book.xlsm" 'Application.Goto Workbooks("Copy To This Book").Sheets("Sheet1").Range("A1") Set RngTo = Application.InputBox(Prompt:="Enter a single cell.", _ Title:="Enter Copy to Column", Type:=8) ' If RngTo Is Nothing Then Exit Sub MsgBox RngTo.Address RngTo.Resize(UBound(arrFrm), 1) = arrFrm 'ActiveWorkbook.Save 'ActiveWorkbook.Close End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste special syntax Book to Book
Hi Howard,
Am Mon, 21 Jul 2014 08:17:03 +0200 schrieb Claus Busch: try: bit more universally. You can copy from columns, from rows and also a single cell: Sub CopyBookToBook() Dim RngFrm As Range Dim RngTo As Range Dim varFrm As Variant Set RngFrm = Application.InputBox(Prompt:="Enter a Copy from Range.", _ Title:="Enter Copy from Column", Type:=8) If RngFrm Is Nothing Then Exit Sub varFrm = RngFrm 'Workbooks.Open Filename:= _ "C:\Users\Howard Kittle\Documents\Copy To This Book.xlsm" 'Application.Goto Workbooks("Copy To This Book").Sheets("Sheet1").Range("A1") Set RngTo = Application.InputBox(Prompt:="Enter a single cell.", _ Title:="Enter Copy to Column", Type:=8) ' If RngTo Is Nothing Then Exit Sub MsgBox RngTo.Address If IsArray(varFrm) Then RngTo.Resize(RngFrm.Rows.Count, RngFrm.Columns.Count) = varFrm Else RngTo = varFrm End If 'ActiveWorkbook.Save 'ActiveWorkbook.Close End Sub Regards Claus B. -- Vista Ultimate / Windows7 Office 2007 Ultimate / 2010 Professional |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Paste special syntax Book to Book
bit more universally. You can copy from columns, from rows and also a single cell: Regards Claus B. Works as you say and that is perfect, and quite versatile. Thanks. Regards, Howard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy worksheets to new book without linking to original book | Excel Discussion (Misc queries) | |||
Excel Work Book Copied Into Word With Paste Special, Excel Workboo | Excel Discussion (Misc queries) | |||
'BeforeClose' code problems:book won't close if more than one book is open | Excel Programming | |||
I would like to copy & "paste special" an entire book | Excel Worksheet Functions | |||
Open book, check for macros, close book | Excel Programming |