Paste special syntax Book to Book

[L. Howard]

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

 

[GS]

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

 

[L. Howard]

Just assign the value directly...



RngTo = RngFrm

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

 

[GS]

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

 

[L. Howard]

Works for me! Note that both ranges must be the same size/shape...

Okay, something sinister is messing with me.

I use mouse and select D110 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

 

[Claus Busch]

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.

 

[Claus Busch]

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.

 

[L. Howard]

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