Set Rng = Range("S2:S293")
 

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.

Set Rng = Range("S2:S293")
 

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.

Set Rng = Range("S2:S293")
 

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")

Set Rng = Range("S2:S293")
 

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.

Set Rng = Range("S2:S293")
 

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.

Set Rng = Range("S2:S293")
 

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.

Set Rng = Range("S2:S293")
 

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

Perfect!