[RESOLVED] Selection Range in Excel as object variable

[VBA666]

I want to select a range of cells of about 1 to 10 rows and 5 to 50 columns from one workbook and based on that selection will set the values in a different workbook. Here is my code that I cannot make work.

Public Sub DataTransferTwo()
    Dim MyRange As Range
    Dim Pname As String
    Dim Loc As String
    Dim Clt As String
    Dim MyDate As Date
    '
 Workbooks("Master Project Status.xls").Worksheets "Proposals").Activate
        Set MyRange = Selection.Value        (This is far as I get then errors-Object Required or Wrong Method/Property)
        With MyRange
        MyDate = MyRange("", Range("B1").End(xlDown).Value)
        Pname = MyRange("", Range("D1").End(xlDown).Value)
        Clt = MyRange("", Range("E1").End(xlDown).Value)
        Loc = MyRange("", Range("G1").End(xlDown).Value)
       End With
    '
    Workbooks("Proposal Tracking.xlsx").Worksheets("ACT").Activate
    Range("B4000").End(xlUp).Offset(1, 0).Select
    Selection = Pname
    Range("D4000").End(xlUp).Offset(1, 0).Select
    Selection = Clt
    Range("E4000").End(xlUp).Offset(1, 0).Select
    Selection = Loc
    Range("G4000").End(xlUp).Offset(1, 0).Select
    Selection = MyDate

End Sub

Any help would be greatly appreciated

[RobDog888]

Thread Moved

Welcome to the Forums

[VBA666]

Where should I have posted this???

[westconn1]

Set MyRange = Selection.Value (This is far as I get then errors-Object Required or Wrong Method/Property)

you are trying to assign a value to a range object
try
set myrange = selection.range

Where should I have posted this???

here

[si_the_geek]

In case you haven't noticed the breadcrumb links above the posts (and below them), "here" is the Office Development forum.

You had posted in the VB6 forum, but the "VB Editor" in Office programs is actually VBA rather than VB, so the VB6 forum is not really apt (they have some similarities, but lots of differences too).

[VBA666]

Wesconn1...

Well ....it didn't quite correct the problem.

Public Sub DataTransferTwo()
    Dim Pname As ( I changed this from String to Range to Variant to Long and nothing mattered)
    Dim Loc As Variant
    Dim Clt As Variant
    Dim MyDate As Date
    Dim MyRange As Variant
    Set MyRange = Selection (This time I got a property error when I included .Range to Selction so I just removed it and it went by only to create another error below)
        '
With MyRange
    Pname = MyRange("", Range("D1").End(xlDown).Value) ( Now I get Error 13 Typematch)
    Clt = MyRange("", Range("E1").End(xlDown).Value)
    Loc = MyRange("", Range("G1").End(xlDown).Value)
    MyDate = MyRange("", Range("B1").End(xlDown).Value)
End With
'
 '
    Workbooks("Proposal Tracking.xlsx").Worksheets("ACT").Activate
    Range("B4000").End(xlUp).Offset(1, 0).Select
    Selection = Pname
    Range("D4000").End(xlUp).Offset(1, 0).Select
    Selection = Clt
    Range("E4000").End(xlUp).Offset(1, 0).Select
    Selection = Loc
    Range("G4000").End(xlUp).Offset(1, 0).Select
    Selection = MyDate
End Sub

I aapreciate any help you can give
thanks VBA666

[westconn1]

as you give no indication as what part is not working now, hard to make suggestions

do you get error, incorrect result or nothing happen?

[VBA666]

as you give no indication as what part is not working now, hard to make suggestions

do you get error, incorrect result or nothing happen?

I appolgize for not updating this status issue it was resolved after I sent this.