Having a changing date inside a macro or VBA code

[Kdub133]

Hello,

New to the forum and to VBA in general. Have a question about how to have a cell have a variable date that then changes the macro run depending on that date? For example....lets say the date is in B2 and I am pulling information from a website that is date dependent, then how do I do that macro. I will paste the code I have below and hopefully someone can help me in deciding what is wrong with it. When I try and run it it says Variable not found on "B2". Not sure if there are other problems in the code but I do know that if I put in a actual date where I have the text (B2, etc) field then the code works. Any help would be much appreciated!

Sub Button1_Click()

' Losses Macro
' MLC for whatever day
'

Workbooks.Open Filename:= _
"https://www.midwestiso.org/Library/Repository/Market%20Reports/" & Text(B2, "yyyymmdd") & "_da_lmp.csv"
ActiveWindow.Visible = False
Windows(" & Text(B2, "yyyymmdd") & "_da_lmp.csv").Visible = True

Rows("3532:5294").Select
Selection.Copy
Windows("Monthly MLC avg.xlsm").Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AD17").Select
End Sub

[:) Sixthsense :)]

Instead of B2 mention it as Range("B2").Value

Please Login or Register  to view this content.

[Kdub133]

Thank you Sixthsense for you help. I am still having issues as when I leave the word text in there I keep getting a error saying that the sub or function is not defined. When I take the text word out it gives me a compile error of expected: named parameter around the date formatting. Not sure if you could help me get around this issue? Thank you again for your help. I pasted the code below that seems to be close.

Sub Button1_Click()
'
' Losses Macro
' MLC for whatever day
'


Workbooks.Open Filename:= _
"https://www.midwestiso.org/Library/Repository/Market%20Reports/" & Range("B2").Value, "yyyymmdd") & "_da_lmp.csv"
ActiveWindow.Visible = False
Windows(Format(Range("B2"), "yyyymmdd") & "_da_lmp.csv").Visible = True

Rows("3532:5294").Select
Selection.Copy
Windows("Monthly MLC avg.xlsm").Activate
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlNormal
ActiveWindow.WindowState = xlMaximized
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("AD17").Select
End Sub

[:) Sixthsense :)]

Missed the Format Function in your above code refer the below code

Please Login or Register  to view this content.

[Kdub133]

Thank you SO much sixthsense! This issue is solved.

[:) Sixthsense :)]

Glad it helps you and thanks for the feedback