Range names

[John Wood]

What's the proper syntax when using a named data range in VBA. Here's what I got so far...then I come to grinding halt. It simply does not recognize the value that's in B28. If I put the named range within the Range function it works perfect.

Private Sub CommandButton1_Click()
Dim source As Range
Dim fdata As String
fdata = Range("B28").Text
Set source = Range("B28")
source.Copy
Range("A40").Select
Selection.PasteSpecial
End Sub

I'm starting to pull my hair out over this one!

 

[Andrew Poulsom]

If the name of the range is in B28:

fdata = Range(Range("B28").Value).Text

 

[John Wood]

I'm just not getting it. Here's my code so far. Pressing the command button for this module simply puts the word "Period2" or "Period1" into A40.

Private Sub CommandButton1_Click()
Dim source As Range
Dim fdata As String
fdata = Range("B28").Text
Set source = Range("B28")
source.Copy
Range("A40").Select
Selection.PasteSpecial xlPasteValues
End Sub

Your help is much appreciated. Very much appreciated!

 

[John Wood]

if I replace:

Set source = Range("B28")

with

Set source = Range("Period2")

or

Set source = Range("Period1")

it works fine but defeats the purpose of my module.

 

[Andrew Poulsom]

If B28 contains the text Period2:

Set source = Range(Range("B28").Value)

 

[John Wood]

Sorry, but,.... YOU ARE A GENIUS!!!!!!!!!!
Thank you so much. I was close in my thoughts and I guess that counts for something. It's working exactly the way I want!

 

[John Wood]

The only problem I have now is I want run that code from another worksheet. I've added the following first line to the code that was working on the original worksheet:

Private Sub CommandButton1_Click()
Sheets("sitedata").Activate
Range("A38:Q65").Select
Application.CutCopyMode = False
Selection.ClearContents
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = xlNone
Dim source As Range
Dim fdata As String
fdata = Range("b28").Text
Set source = Range(Range("B28").Value)
source.Copy
Range("c40").Select
Selection.PasteSpecial xlPasteValues
End Sub

I'm getting a runtime error 1004 Application/Object error at the second line. Any ideas?

 

[Andrew Poulsom]

Does this work?

Private Sub CommandButton1_Click()
    Dim source As Range
    Dim fdata As String
    Application.CutCopyMode = False
    With Sheets("sitedata")
        With .Range("A38:Q65")
            .ClearContents
            .Borders(xlDiagonalDown).LineStyle = xlNone
            .Borders(xlDiagonalUp).LineStyle = xlNone
            .Borders(xlEdgeLeft).LineStyle = xlNone
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlEdgeRight).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
            .Interior.ColorIndex = xlNone
        End With
        fdata = .Range("b28").Text
        Set source = .Range(.Range("B28").Value)
        source.Copy
        .Range("c40").PasteSpecial xlPasteValues
    End With
End Sub

 

[John Wood]

Like a charm. I'm going to sit down with a nice cup of coffee after work and try and work my way through what you wrote. I really, really want to understand this rather than just have genius' like you solving my problems. (although, WAHOOOOOOO, the site exists to help people like me!)

Thank you so much.