change date to value in VBA

[zakasnak]

I have a macro that I run to pull sheets from a workbook into their own file. The file name is created based on value in certain cells. Currently, my code puts today's date at the end of the file name, but I would like for it to pull the date from cell C8. I'm having problems converting my code to make this happen.

Sub SaveWS_to_NewWBs()
Dim wb As Workbook
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    ws.Copy
    Set wb = ActiveWorkbook
    wb.SaveAs ThisWorkbook.Path & "\" & ws.Range("A8").Value & " " & ws.Name & " " & ws.Range("X8").Value & " " & Format(Date, "yyyymmdd") & ".xls"
    wb.Close False
Next ws
End Sub

I would like for this part: & Format(Date, "yyyymmdd") to pull from C8. I would also like to use the format "yyyymmdd".

Help?

 

[Bill_Biggs]

If I am following you correctly, and assuming that C8 is a date to begin with (such as =Now() or something else that actually has a date encoded) then this should do it:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SaveWS_to_NewWBs()<br><SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> B <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>B = Range("C8").Value<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>    ws.Copy<br>    <SPAN style="color:#00007F">Set</SPAN> wb = ActiveWorkbook<br>    wb.SaveAs ThisWorkbook.Path & "\" & ws.Range("A8").Value & " " & ws.Name & " " & ws.Range("X8").Value & " " & Format(B, "yyyymmdd") & ".xls"<br>    wb.Close <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH's.

 

[zakasnak]

If I am following you correctly, and assuming that C8 is a date to begin with (such as =Now() or something else that actually has a date encoded) then this should do it:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> SaveWS_to_NewWBs()<br><SPAN style="color:#00007F">Dim</SPAN> wb <SPAN style="color:#00007F">As</SPAN> Workbook<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> B <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>B = Range("C8").Value<br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets<br>****ws.Copy<br>****<SPAN style="color:#00007F">Set</SPAN> wb = ActiveWorkbook<br>****wb.SaveAs ThisWorkbook.Path & "\" & ws.Range("A8").Value & " " & ws.Name & " " & ws.Range("X8").Value & " " & Format(B, "yyyymmdd") & ".xls"<br>****wb.Close <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">Next</SPAN> ws<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

HTH's.

I'm not following.... it looks like the same syntax. Did you mean & Format (C8, "yyyymmdd")?

 

[zakasnak]

AND yes, the date in C8 is in mm/dd/yyyy format.

 

[zakasnak]

I tried the code both with "B" & "C8", but it didn't put the date at the end of the file name. Any other ideas?

 

[Bill_Biggs]

Sorry, Carole, I was out sick yesterday and did not get to reply. I will look it over and see if I can figure something out for you. Be back soon!

 

[Bill_Biggs]

OK, put the word "Hello" in A8 of a blank workbook. I placed the date 03/03/2009 in C8. And I put the word "Again" in the X8. I named the sheet, "My Name." I ran the macro and found the following workbook saved in my C drive. (Using the precise macro I posted earlier)

Hello My Name Again 20090303.xls

This worked the same way when I put an equation in C8 [(=Now()], although in that case I got the actual date for today, but int he same format as above.

Tell me what you have in A8, C8 and X8 and the name of the sheet (and tell me if C8 has an equation in it or not).

 

[zakasnak]

OK, put the word "Hello" in A8 of a blank workbook. I placed the date 03/03/2009 in C8. And I put the word "Again" in the X8. I named the sheet, "My Name." I ran the macro and found the following workbook saved in my C drive. (Using the precise macro I posted earlier)

Hello My Name Again 20090303.xls

This worked the same way when I put an equation in C8 [(=Now()], although in that case I got the actual date for today, but int he same format as above.

Tell me what you have in A8, C8 and X8 and the name of the sheet (and tell me if C8 has an equation in it or not).

A8 = PO #
C8 = PO date, formatted mm/dd/yyyy, no formula
X8 = Buyer nickname

So I'm clear, this code will know to pull the data from cell C8 if it references "B"? I guess I don't understand that.

Format(B, "yyyymmdd")

I'll try it on today's POs & let you know what happens....

 

[Bill_Biggs]

Yes, see where you give B a value in the 5th line of the macro? That is why it will know that it is going to take the date in C8 and change it the yyyymmdd for you, provided that is ALL you nave in C8. Just a date.

Let me know!

 

[zakasnak]

Yes, see where you give B a value in the 5th line of the macro? That is why it will know that it is going to take the date in C8 and change it the yyyymmdd for you, provided that is ALL you nave in C8. Just a date.

Let me know!

Worked perfectly! Thank you so much!

Carole

PS Jesus loves me, too! How wonderful is that?!