0

I sure hope I can properly explain this... I have a list of data. The data includes dates, times, and names of schools. I need this data from the worksheet I currently grabbed from the "get data" "from the web" function in excel. Here is an example of what I'm trying to accomplish based on the data I have on these spreadsheets:

Friday, August 17

Boys Soccer

Antioch at Vernon Hills, 4:30 p.m.

Stevenson at Warren, 5 p.m.

Girls Tennis

Vernon Hills at Warren, 6 p.m.

Round Lake at Wauconda, 6 p.m.

Saturday, August 18

Boys Golf

Warren at Antioch, 8:30 a.m.

The list goes on everyday of the week with different schedules and team names as well as the time the game is to be played.

Basically this data exists on the spreadsheet that I currently hold the data, only that the data itself is kind of all over the place. Granted the comma's before the time aren't on the spreadsheet and the "p.m." and "a.m." don't have periods in between them. So adding those to the code would be helpful. I'll share a screenshot of what the data I have looks like so it can be seen for reference.

Now, I have managed to build a macro that is doing what I technically want it to do, however I haven't been able to figure out the code that says move on to the next line in the range of data. In addition, I have 4 other tabs (aka; sheets) that have the same formatted data only for a different school. It would be ideal if one push of a button to organize the data from all spreadsheets in a manner as described in the example above. However, I'd be happy with just one button right now for one school and then taking that code and changing it up to use on a different school.

So it's known, what I'm attempting to accomplish overall here is a schedule of games for high school sports by date and type of sport.

Here is the code I am currently using:

Private Sub CommandButton1_Click()
Dim Eventname As String, Time As String, Opponent As String, Location As String, dateday As String
Worksheets("Vernon Hills").Select
Eventname = Range("A3")
Time = Range("B3")
Opponent = Range("C3")
Location = Range("D3")
dateday = Range("B2")
Worksheets("Main").Select
Worksheets("Main").Range("A1").Select
If Worksheets("Main").Range("A1").Offset(1, 0) <> "" Then
Worksheets("Main").Range("A1").End(xlDown).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = dateday
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Opponent
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Time
Worksheets("Vernon Hills").Select


End Sub

Lastly.... Here is a screenshot of the data I'm trying to scrape data from.

https://i.stack.imgur.com/KMkZo.jpg

You'll notice that cell A3 has the words "Golf:Girls" located in that particular cell. Also note that the date is in A2 and the name of the opponent is in C3. The Location column is just a way for me to reference "Home" vs. "Away" teams.

Thank you to anyone that can help me... I will gladly specify more if need be.

BigBen
  • 46,229
  • 7
  • 24
  • 40
Dave
  • 3
  • 3
  • You’ll need to be a little more specific about what exactly you want, because this could be a rather cumbersome job which is not the intent of this site – Marcucciboy2 Aug 15 '18 at 05:24
  • I recommend to read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and apply this to your code first. It will make your code much shorter more reliable and faster and easier to maintain. Did I understand it correctly that you just want to run this code on every worksheet? Then just build a loop around it that loops through your worksheets to run the code in very sheet. – Pᴇʜ Aug 15 '18 at 06:17
  • It seems your data is not very regular. Do you want to just use the Golf:Girls or all data? It better to handle the filter in the data service but not VBA – Seiya Su Aug 15 '18 at 08:47
  • Is there a web link/URL we can use ? It may be possible to just pull back specific information from the site. – QHarr Aug 16 '18 at 12:56

0 Answers0