Copy and Paste Title

[mlmrob]

Good Afternoon all,

Is there a macro for copying data from Sheet 2 to Sheet 1.

I have the day's horseracing cards on sheet 1 and I have the title of each race on sheet 2. I want to copy the race title for each race and paste it into the correct cell on sheet 1.

Example...the first race title is in cell B3 on sheet 2 and that would pasted into cell B1 on sheet 1. The second race title would be in Cell Cell B4 on sheet 2 and so on but the cells for the titles to be pasted in on sheet 1 change due to the amount of runners per race each day. They could go down as far as 2000 rows on sheet 1.

At the minute I copy the cell on Sheet 2 and paste it into the appropriate cell on sheet 1. It doesn't take long just looking for way for a macro to do the work.

I have posted a link of exactly how the racecard and the titles appear. Any help would be appreciated.

Kindest Regards

Rob

Link to sheet

https://1drv.ms/x/s!Al-DktU5BJ8fgUmWR9ZtjMR687JI

 

[HankJ]

This is a suggested approach, rather than code.
Column A in Sheet 1. Count the number of lines with a given time, taken from Table in Sheet 2. This will tell how many rows you have for each table. This will allow you to copy from Sheet 2 Column B and paste into Sheet 1 Column B Row x.

The formats of the times don't look the same.

If this is not clear, get back and I'll try again... I've been known to be not concise.
Hj

 

[mlmrob]

Thanks Hank. I will give that a go. I will have to get the format of the times the same though.

 

[Fluff]

How about

Sub mlmrob()
   Dim Rng As Range
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Sheets("Sheet2").Range("A:A").SpecialCells(xlConstants).Offset(, 1).Value
   For Each Rng In Sheets("sheet1").Range("B:B").SpecialCells(xlBlanks).Areas
      i = i + 1
      If Ary(i, 1) = "" Then i = i + 1
      Rng.Offset(Rng.Count - 1).Resize(1, 1).Value = Ary(i, 1)
   Next Rng
End Sub

 

[mlmrob]

Gopod Morning Fluff, thanks you for that code, I will try that today and get back.

 

[mlmrob]

Works a treat, thanks Fluff, much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback