Trying to move a BLOCK of data using 'OFFSET'
 

To All,

I thought this would be a no brainer ... but I'm eating those words
....
I have a userform that successfully allows the user to select a list
entry. The ListBox1.ListIndex = 0 (-12)and are saved in a worksheet
cell whose range-name is "ScheduleOffset" on a sheet called
"DataSheet".

My goal is simple ... HEADER_0 is the (DESTINATION) Range-name of the
top-left-cell (in "A6") on DataSheet. I'm trying to copy 50 columns
and 7 rows of data starting with/at HEADER_0.

My SOURCE data starts at "BaseRow" (cell "A15")- which is the top left
cell of the first block of data (50 columns x 7 rows)'
All my other SOURCE blocks are 9 rows down from the preceeding block.
"A24","A33","A42" ... etc.

I calculate my row by multipling the 'value' stored in
"ScheduleOffset" by 9. Then I "OFFSET" from "BaseRow" to get to the
Top left cell of a specific BLOCK of data. Then I try to copy the 50
columns (includes 0-49) and 7 rows to my DESTINATION.

The code fails to execute in the indicated line below ...

CAN SOMEONE PLEASE SHOW ME HOW TO MOVE A BLOCK OF "VALUES" TO A
DESTINATION RANGE????
'''''''''''''''''''''''''''''''''''
Sub CopyOffsetSchedule()
Application.ScreenUpdating = False
Sheets("DataSheet").Visible = True
Sheets("DataSheet").Select
Dim r As Integer: Dim c As Integer
For r = 0 To 7
For c = 0 To 49
'FAILS on next line
Range("BaseRow").Offset(9 * Range("ScheduleOffset").Value + r,
c).Select
Application.CutCopyMode = False
Selection.Copy

Range("HEADER_0").Offset(0, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Next
Next
Sheets("DataSheet").Visible = False
End Sub
'''''''''''''''''''''''''''

Thanks in advance ...

Jim Pellechi

Trying to move a BLOCK of data using 'OFFSET'
 

JimP,
Replace this part of the code
Dim r As Integer: Dim c As Integer
For r = 0 To 7
For c = 0 To 49
'FAILS on next line
Range("BaseRow").Offset(9 * Range("ScheduleOffset").Value + r,
c).Select
Application.CutCopyMode = False
Selection.Copy

Range("HEADER_0").Offset(0, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Next
Next

with this

Range("BaseRow").Offset _
(9 * (Range("ScheduleOffset").Value) _
, 0).Resize(9, 50).Copy _
Destination:=Range("HEADER_0")

HTH
Cecil

"JimP" wrote in message
om...
To All,

I thought this would be a no brainer ... but I'm eating those words
...
I have a userform that successfully allows the user to select a list
entry. The ListBox1.ListIndex = 0 (-12)and are saved in a worksheet
cell whose range-name is "ScheduleOffset" on a sheet called
"DataSheet".

My goal is simple ... HEADER_0 is the (DESTINATION) Range-name of the
top-left-cell (in "A6") on DataSheet. I'm trying to copy 50 columns
and 7 rows of data starting with/at HEADER_0.

My SOURCE data starts at "BaseRow" (cell "A15")- which is the top left
cell of the first block of data (50 columns x 7 rows)'
All my other SOURCE blocks are 9 rows down from the preceeding block.
"A24","A33","A42" ... etc.

I calculate my row by multipling the 'value' stored in
"ScheduleOffset" by 9. Then I "OFFSET" from "BaseRow" to get to the
Top left cell of a specific BLOCK of data. Then I try to copy the 50
columns (includes 0-49) and 7 rows to my DESTINATION.

The code fails to execute in the indicated line below ...

CAN SOMEONE PLEASE SHOW ME HOW TO MOVE A BLOCK OF "VALUES" TO A
DESTINATION RANGE????
'''''''''''''''''''''''''''''''''''
Sub CopyOffsetSchedule()
Application.ScreenUpdating = False
Sheets("DataSheet").Visible = True
Sheets("DataSheet").Select
Dim r As Integer: Dim c As Integer
For r = 0 To 7
For c = 0 To 49
'FAILS on next line
Range("BaseRow").Offset(9 * Range("ScheduleOffset").Value + r,
c).Select
Application.CutCopyMode = False
Selection.Copy

Range("HEADER_0").Offset(0, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Next
Next
Sheets("DataSheet").Visible = False
End Sub
'''''''''''''''''''''''''''

Thanks in advance ...

Jim Pellechi

Trying to move a BLOCK of data using 'OFFSET'
 

Works like a charm, THANKS

JimP



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!