copy range from one worksheet to another vba

[ratnesh darne]

I want to copy range from one worksheet to another with one reference.

like if range is A1:AU5 and have reference value in cell AW1 or AW1:AW5, How can i copy the same range by same reference to another worksheet?

 

[VoG]

Maybe like this

Sub a()
Range("A1:AU5").Copy Destination:=Range(Range("AW1").Value)
End Sub

 

[Hashiru]

Hi Try this one

Sub RangeTransfer()

Set ws = Application.Worksheets(1)
Set wsn = Application.Worksheets(2)

With ws
.Range("A1:AU5").Copy Destination:=wsn.Range("A1")
.Range("AW1:AW5").Copy Destination:=wsn.Range("AW1")
End With

End Sub

This assumes that you are transfering from worksheet name sheet1 to sheet2 (Name your sheets accordingly)

Hope it works

 

[Hashiru]

Hi Try this one

Sub RangeTransfer()
Set ws = Application.Worksheets(1)
Set wsn = Application.Worksheets(2)
With ws
.Range("A1:AU5").Copy Destination:=wsn.Range("A1")
.Range("AW1:AW5").Copy Destination:=wsn.Range("AW1")
End with
End Sub

Hope it works

 

[ratnesh darne]

how i test it, I mean where i put formula =rangetranfer() in which cell?