Hey Excel Gurus,
I'm working on a vba macro that would allow for the copying from one sheet to another based on a date. I have a macro built on another sheet that does the same thing, but for some reason it isn't copying when I am trying to modify it for the new sheet. In this case, I have a date in cell B2 on sheet "Fixture Data Drop", as well as a range of data B5:B28 on the same sheet. On another sheet, FXDataCopy, there are a range of dates from B1:B32. I need the macro to match the date in B2 on "Fixture Data Drop" with the date and corresponding column on sheet "FXDataCopy" and paste the data. The macro I'm currently working with looks like this:
Sub PullFixtureDataDrop()
Dim sh1 As Worksheet, sh2 As Worksheet, r As Range, f As Range
Set sh1 = Sheets("Fixture Data Drop")
Set sh2 = Sheets("FXDataCopy")
If sh1.Range("B2").Value = "" Then
MsgBox "put a date in B2"
Exit Sub
End If
If Not f Is Nothing Then
f.Offset(1).Resize(r.Rows.Count, 1).Value = r.Value
Set r = sh1.Range("B5:B28")
Set f = sh2.Rows(1).Find(sh1.Range("B2").Value, , xlFormulas, xlWhole)
End If
End Sub
Any help would be greatly appreciated.
I'm working on a vba macro that would allow for the copying from one sheet to another based on a date. I have a macro built on another sheet that does the same thing, but for some reason it isn't copying when I am trying to modify it for the new sheet. In this case, I have a date in cell B2 on sheet "Fixture Data Drop", as well as a range of data B5:B28 on the same sheet. On another sheet, FXDataCopy, there are a range of dates from B1:B32. I need the macro to match the date in B2 on "Fixture Data Drop" with the date and corresponding column on sheet "FXDataCopy" and paste the data. The macro I'm currently working with looks like this:
Sub PullFixtureDataDrop()
Dim sh1 As Worksheet, sh2 As Worksheet, r As Range, f As Range
Set sh1 = Sheets("Fixture Data Drop")
Set sh2 = Sheets("FXDataCopy")
If sh1.Range("B2").Value = "" Then
MsgBox "put a date in B2"
Exit Sub
End If
If Not f Is Nothing Then
f.Offset(1).Resize(r.Rows.Count, 1).Value = r.Value
Set r = sh1.Range("B5:B28")
Set f = sh2.Rows(1).Find(sh1.Range("B2").Value, , xlFormulas, xlWhole)
End If
End Sub
Any help would be greatly appreciated.