Sub DataSave()
Dim Rng As Range
Set Rng = Range("S2:S293")
Sheets("Sheet1").Range("C" & Range("A1")).Resize
(Rng.Cells.Count).Value = Rng.Value
Beep
End Sub

Thanks to your help, this is working nicely.
Trying something new, I am on Sheet2 and I would like to use
Set Rng = Range("S2:S293")
to select cells on Sheet1 and copy to sheet2.

When you set the Rng variable the first time, include the sheet reference and
it will apply throughout the code to that sheet:

Set Rng = Sheets("Sheet1").Range("S2:S293")

Now anytime you use Rng it applies to Sheet 1.

"Fan924" wrote:

Sub DataSave()
Dim Rng As Range
Set Rng = Range("S2:S293")
Sheets("Sheet1").Range("C" & Range("A1")).Resize
(Rng.Cells.Count).Value = Rng.Value
Beep
End Sub

Thanks to your help, this is working nicely.
Trying something new, I am on Sheet2 and I would like to use
Set Rng = Range("S2:S293")
to select cells on Sheet1 and copy to sheet2.

On Mar 1, 8:00*pm, Fan924 wrote:
Sub DataSave()
Dim Rng As Range
Set Rng = Range("S2:S293")
Sheets("Sheet1").Range("C" & Range("A1")).Resize
(Rng.Cells.Count).Value = Rng.Value
* * Beep
* * End Sub

Thanks to your help, this is working nicely.
Trying something new, I am on Sheet2 and I would like to use
Set Rng = Range("S2:S293")
to select cells on Sheet1 and copy to sheet2.

rng.copy destination:=worksheets("Sheet2").range("A1")

Sub DataLoad()
Dim Rng As Range
Dim MySheet As Variant
Dim MyTarget As Variant
MySheet = ActiveSheet.Name
MyTarget = Range("A1").Value + 27
Sheets("Sheet1").Select
Set Rng = Range("E" & MyTarget & ":Q" & MyTarget + 12)
Sheets(MySheet).Range("E2:Q14").Value = Rng.Value
Sheets(MySheet).Select
Beep
End Sub

I have it reduced to this. Everything works. I had to use Sheets
("Sheet1").Select before
Set Rng = Range(). There is a bad flicker from changing sheets.
Looking for alternatives.

See if this version gets rid of the flicker:

Sub DataLoad()
Dim Rng As Range
Dim MySheet As Variant
Dim MyTarget As Variant
Set MySheet = ActiveSheet
MyTarget = Range("A1").Value + 27
Set Rng = Sheets("Sheet1") _
.Range("E" & MyTarget & ":Q" & MyTarget + 12)
MySheet.Range("E2:Q14").Value = Rng.Value
Beep
End Sub




"Fan924" wrote:

Sub DataLoad()
Dim Rng As Range
Dim MySheet As Variant
Dim MyTarget As Variant
MySheet = ActiveSheet.Name
MyTarget = Range("A1").Value + 27
Sheets("Sheet1").Select
Set Rng = Range("E" & MyTarget & ":Q" & MyTarget + 12)
Sheets(MySheet).Range("E2:Q14").Value = Rng.Value
Sheets(MySheet).Select
Beep
End Sub

I have it reduced to this. Everything works. I had to use Sheets
("Sheet1").Select before
Set Rng = Range(). There is a bad flicker from changing sheets.
Looking for alternatives.

see if this does what you want:

Sub DataLoad()
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim MyTarget As Long
Set ws2 = ActiveSheet
Set ws = Worksheets("Sheet1")
MyTarget = ws2.Range("A1").Value + 27
With ws
ws2.Range("E2:Q14").Value = .Range("E" & MyTarget & ":Q" & MyTarget
_
+ 12).Value
End With
End Sub

--


Gary K



"Fan924" wrote in message
...
Sub DataLoad()
Dim Rng As Range
Dim MySheet As Variant
Dim MyTarget As Variant
MySheet = ActiveSheet.Name
MyTarget = Range("A1").Value + 27
Sheets("Sheet1").Select
Set Rng = Range("E" & MyTarget & ":Q" & MyTarget + 12)
Sheets(MySheet).Range("E2:Q14").Value = Rng.Value
Sheets(MySheet).Select
Beep
End Sub

I have it reduced to this. Everything works. I had to use Sheets
("Sheet1").Select before
Set Rng = Range(). There is a bad flicker from changing sheets.
Looking for alternatives.

On Mar 2, 5:42 am, "Gary Keramidas" wrote:
see if this does what you want:

Perfect!