Copy range defined by cell contents (probably quick solution)

[hartsie]

I am trying to keep a file short and simple. I have basically defined the cell ranges in multiple cells, and I am trying to use VBA to automate copying and pasting values among sheets, using the cell ranges I have already defined. For example, within Sheet2, I have defined a range in Sheet1 from which I want to copy a range of data. So, we will say that in Sheet2, I have input into a cell the text "A1:BC1," which represents the range on Sheet1 from where I want to copy data. So, from Sheet2, I want to go to Sheet1, select the range of data (defined in Sheet2, "A1:BC1"), and paste it to Sheet3.

I have tried defining the range "A1:BC1" as a string. My simple VBA, which isn't working out for me right now, and its costing enough of my time that I am ready to call it quits, is below:

Dim CopRng As String

Sheets("Sheet2").Select
Range(Range("D6").Value = CopRng
Sheets("Sheet1").Activate
CopRng.Select


I can't seem to get to the point where I can define the range, and then select it. It has been a while since I have touched VBA and I am pulling my hair out trying to do something that I thought would be SO. SIMPLE.

I understand that I know very little about VBA. I thought this would be an easy task, but its proving to best me at every turn. I have scoured the interwebs, but apparently my search keywords aren't specific enough to find an answer.

 

[Fluff]

How about something like

Sub hartsie()
   Dim CopyRng As Range
   
   With Sheets("Sheet2")
      Sheets("Sheet1").Range(.Range("D6").Value).Copy Sheets("Sheets3").Range(.Range("D6").Value)
   End With
End Sub

 

[hartsie]

Beautiful! Bravo! You are a very good problem solver. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[hartsie]

@Fluff

UPDATE - I fixed it.

Sheets("Sheet1").Range(.Range("D6").Value).Copy Sheets("Sheets3").Range(.Range("D6").Value)

Do you know why I would get a 1004 error? It worked the first time around and now it is yelling at me.

'Select Method of Range Class Failed'

 

[Fluff]

Glad you sorted it & thanks for letting us know.