Solved: how to copy range and paste in same range in another workbook

[uau007_2137]

How to copy range and paste in same range but in another workbook

Hi All,

I am new to this forum. Need help on my coding.

I am trying to copy an already selected range of cells in a workbook and pasting them into another workbook in the same range as the source workbook. I wrote the following code but it is stuck where I try to select the named range in the destination workbook.

Where am I going wrong?:banghead:

++++++++++++++++++
Private Sub Send_Hour_Val_Click()

'Definitions:
Dim flPath As String
Dim SelRange As Range
Set SelRange = Selection

'path:
flPath = "C:\Documents and Settings\mngr\My Documents\testing sheet.xlsx"

'copy selection:
SelRange.Copy

'open destination workbook:
Workbooks.Open flPath

'activate destination sheet
Sheets("Testing").Activate
'select destination range
Range("SelRange").Select

'paste special, save and close
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub
+++++++++++++++

[BrianMH]

This will work for you but there are better ways of doing things. If you let us know what exactly you want to do we can probably make it better. Is the range you are copying over fixed? Does it include multiple cells in the range?

Private Sub Send_Hour_Val_Click()
'Definitions:
Dim flPath As String
Dim SelRange As Range
Dim sAddress As String
Set SelRange = Selection
Dim wbPaste As Workbook
sAddress = SelRange.Address
'path:
flPath = "C:\Documents and Settings\mngr\My Documents\testing sheet.xlsx"
'copy selection:
SelRange.Copy
'open destination workbook:
Set wbPaste = Workbooks.Open(flPath)

'activate destination sheet
With wbPaste.Sheets("Testing")
.Activate
'select destination range
.Range(sAddress).Select
End With
'paste special, save and close
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
wbPaste.Save
wbPaste.Close
End Sub

[uau007_2137]

hi brian,

my range is not fixed. it may be one or multiple cells, multiple continuous columns or multiple continuous rows etc.

i tried this code and is working. thanks:friends:

[BrianMH]

No problem. Make sure to mark the thread as solved.