Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range and Assign a Defined Name to the Pasted Range
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range and Assign a Defined Name to the Pasted Range
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 Sub -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=159845 Microsoft Office Help |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range and Assign a Defined Name to the Pasted Range
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 "sgltaylor" wrote: 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range and Assign a Defined Name to the Pasted Range
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Range and Assign a Defined Name to the Pasted Range
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 "Mike H" wrote: 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 "sgltaylor" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy formulas in defined range | Excel Programming | |||
Copy formulas in defined range | Excel Programming | |||
Copy pasted name range | Excel Programming | |||
Name defined Range - Copy? | Excel Discussion (Misc queries) | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming |