how to use offset range and or resize

breilly00

New Member
Joined
Sep 15, 2008
Messages
31
I have a sheet that represents a score card for a sporting event. Row 3 contains the dates of the game, row 4 contains the day of the week and row 5 contains a ‘points’ heading. The remaining rows contain the scores of the games and the team name
For example.

  • Row 3 contains 01/01/19, 01/08,19, 01/15/19 etc. for 19 weeks
  • Row 4 contains Wed, Wed, Wed, etc.
  • Row 5 contains pts, pts, pts, pts, etc.
  • Row 6 thru 23 contains Team name (col1), Scores(col2 to end)
I am trying to copy certain columns to another spread sheet. The logic behind the copy is to take the current date search for a date greater than a date in row 3 and then copy the previous 3(smaller) dates and scores (rows 6 thru end). I can get the dates copied to another sheet by using the following
For Each mycell In Sheets("Scores").Range("B3", _
Sheets("Scores").Cells(3, LastCol))
If mycell.Value > LDate Then
Sheets("Scores").Range(mycell, mycell.Offset(0, -2)).Copy _
Destination:=Sheets("Standings").Range("H3"
Etc. etc

But, I am at a loss on how to move from the active cell in the date row down to row 6 and select the previous 3 columns and the rows (6-23) that contain the scores. The closest that I can is coping 1 column of scores to the different sheet using the following.
mycell.EntireColumn.Resize(Rows.Count - 5).Offset(5).Copy _
' Destination:=Sheets("standings").Range("H4")

A sample of my sheet is below. How would I search for a date, once found, copy the values and date to a new sheet for a total of 3 dates?
17-Sep
24-Sep
1-Oct
8-Oct
15-Oct
17-Oct
22-Oct
Tuesday
Tuesday
Tuesday
Tuesday
Tuesday
Thursday
Tuesday
Pts
Pts
Pts
Pts
Pts
Pts
Pts
1-Team Name
1
1
1
1
1
1
1
2-Team Name
1
1
1
1
1
1
1
3-Team Name
1
1
2


1
2
4-Team Name
2
x
1


2
2
5-Team Name
2
2
2



2

<tbody>
</tbody>
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,396
Office Version
365
Platform
Windows
How about
Code:
mycell.offset(3,-3).resize(18,3).copy Sheets("Standings").Range("H3")
 

breilly00

New Member
Joined
Sep 15, 2008
Messages
31
Wow Wow Wow. I was looking all over the web to find out how to do this And, you with a single line of code showed me the light.

TY Very Much
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
32,396
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,082,500
Messages
5,365,935
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top