PDA

View Full Version : Copy and Add to Range



brorick
06-04-2008, 10:36 AM
I want to copy range (f2:i2) on worksheet(1) to range("ProdList") on worksheet(3). This is what happens behind the scenes on my form. I can easily copy the data. I am having a difficult time pasting it to the range list.

grichey
06-04-2008, 11:15 AM
Sub test1()
'Change sheet names as you need them
'set your range for prod list here
ThisWorkbook.Names.Add Name:="ProdList", _
RefersTo:="=Sheet3!F5:I5", Visible:=True
'set your copy range here
Sheets("sheet1").Select
Range("I2:F2").Select
Selection.Copy Destination:=Worksheets("Sheet3").Range("ProdList")
End Sub

Bob Phillips
06-04-2008, 03:16 PM
Worksheets(1).Range("F2:I2").Copy Worksheets(3).Range("ProdList")

brorick
06-04-2008, 07:04 PM
Grichey and XLD, thank you for your responses. I will give both a try.

brorick
06-04-2008, 10:06 PM
I have come up with the following code. Oddly enough it seems to work except for the last line of code. Although I do not receive any errors and the reference to RMydata displays the correct cells $A$1:$D$2 the information is not posted to Worksheets("frmInvoice").Range(Cells(myRgRow, myRgColumn)). It is like nothing happened and I can't seem to figure why this is happening. If by some miracle I can get it to work it would be nice to figure out how to include an offset in my code so the headers are not included in the copied data ($A$1:$D$2). I only need cells $A$2:$D$2.

Dim RMyData As String

RMyData = Worksheets("frmRequest").Range("ProdList").Address 'ProdList = $A$1:$D$2
Set rgMyData = Range(RMyData)
Dim myRgRow As Long
Dim myRgColumn As Long

myRgRow = Range(RMyData).End(xlUp).Row + 1
myRgColumn = Range(RMyData).Column

Worksheets("frmInvoice").Range(Cells(myRgRow, myRgColumn)) = Range(RMyData).Address

Bob Phillips
06-05-2008, 12:08 AM
I am not seeing tyhe problem. Where are you using offset? That last line is only setting a single cell value, not the whole row.

brorick
06-05-2008, 06:42 PM
I have inlcuded a sample spreadsheet. As you can see the code I have in the module is not working the way I would like. Any thoughts? :help