Copy Range and Assign a Defined Name to the Pasted Range

[sgltaylor]

Hi All,

I need some help with the following:

I am looking for the code which will allow me to copy a range
from one worksheet to another and at the same time assign
a defined name to the pasted area. For example, the range
A1 to A30 on worksheet 2 is copied and pasted to worksheet 1
starting in cell B30. A defined name of "result" is also created
at the same time.

The trick is the worksheets will not always be the same and
neither will the pasted ranges or the address where the
range is to be pasted.

Any help would be greatly appreciated.

Thanks,

Steve

 

[joel]

This code is self explanitory

Sub CopyandDefine()

Set SourceRange = Sheets("Sheet1").Range("A1:C3")
Set DestStart = Sheets("Sheet2").Range("B30")

SourceRows = SourceRange.Rows.Count
SourceCols = SourceRange.Columns.Count

Set DestEnd = DestStart.Offset(SourceRows - 1, SourceCols - 1)
Set DestRange = Range(DestStart, DestEnd)

DestString = DestRange.Address(external:=True, ReferenceStyle:=xlR1C1)

ActiveWorkbook.Names.Add Name:="joel", RefersToR1C1:="=" & DestString

End Su

 

[Mike H]

Hi,

Try this

Sub sonic()
Dim SrcRange As Range
Dim DestRange As Range
Set SrcRange = Application.InputBox(prompt:="Select cells to copy", Type:=8)
Set DestRange = Application.InputBox(prompt:="Select top left cell of
destination range", Type:=8)
If Not SrcRange Is Nothing And Not DestRange Is Nothing Then
SrcRange.Copy Destination:=DestRange
Else
MsgBox "You must select the Source and destination ranges"
End If
End Sub

Mike

 

[marcus]

Hi Steve
T
he worksheet not always being the same is no problem you just run the
code from the sheet your on, the start sheet. The destination sheet
for your paste must have some sort of rule, there must be some way to
identify which sheet you are about to paste to.

For example you may have a unique word on the destination sheet that
you can look for prior to pasting. Can you give some more information
on this please. There should also be some sort of rule for the range
size to copy. Is it the used range on the start sheet? Does it
always start in a given point like A1. Anyways if you could provide
more information you will get a better answer. In the mean time here
is an answer with some smarts built in.

Ask any questions you wish if it does not help.

Take care

Marcus

'Run this from the sheet you wish to copy from.

Option Explicit
Sub MoreInfoPls()
Dim sh As Worksheet
Dim ws As Worksheet
Dim lw As Integer
Dim lwb As Integer
Dim lwc As Integer

Set sh = ActiveSheet
Set ws = Sheets("Sheet1")
lw = Range("A" & Rows.Count).End(xlUp).Row
lwb = ws.Range("B" & Rows.Count).End(xlUp).Row
Range("A1:B" & lw).Copy 'Copies to the last used row in col B
ws.Range("B" & lwb).PasteSpecial xlValues 'Pastes just the vals
lwc = ws.Range("C" & Rows.Count).End(xlUp).Row 'Last used row in C
ws.Range("B" & lwb & ":C" & lwc).Name = "Result"

End Sub

 

[Mike H]

I forgot about naming the range

Sub sonic()
Dim MySelection As Range, SrcRange As Range
Dim DestRange As Range
Set SrcRange = Application.InputBox(prompt:="Select cells to copy", Type:=8)
Set DestRange = Application.InputBox(prompt:="Select top left cell of
destination range", Type:=8)
If Not SrcRange Is Nothing And Not DestRange Is Nothing Then
SrcRange.Copy Destination:=DestRange
ActiveWorkbook.Names.Add Name:="result", _
RefersToR1C1:=DestRange.Resize(SrcRange.Rows.Count, SrcRange.Columns.Count)
Else
MsgBox "You must select the Source and destioantion ranges"
End If
End Sub

Mike