VBA Not Working Correctly

[luke1438]

I am trying to copy paste into a new workbook, then save that workbook as a CSV file with a number in cell J1 followed by today's date. I have the following code. It is working with but saving it as "& ThisFile & DateStr" instead of the number in Cell J1 and today's date.

Sub NewBook()

    Range("A2:F200").Select
    Selection.Copy
    Workbooks.Add
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Dim ThisFile
    Dim DateStr
    ThisFile = Range("J1").Value
    DateStr = Format(Date, "dd-mm-yyyy")
    ActiveWorkbook.SaveAs Filename:= _
        "C:\Users\TOM\Documents\Company\Temp\& ThisFile & DateStr.csv", FileFormat:= _
        xlCSV, CreateBackup:=False
    Range("J10").Select
End Sub

Any help or advice would be greatly appreciated.

Thanks
Luke

 

[offthelip]

you need to change this:

"C:\Users\TOM\Documents\Company\Temp\& ThisFile & DateStr.csv", FileFormat:= _

to

"C:\Users\TOM\Documents\Company\Temp\" & ThisFile & DateStr & ".csv", FileFormat:= _

 

[luke1438]

Hello offthelip,

I had already tried that but tried it again with your suggestions. I am getting a compiling error.

Regards
Luke

 

[offthelip]

try it like this:

    ActiveWorkbook.SaveAs Filename:= _
  "C:\Users\TOM\Documents\Company\Temp\" & ThisFile & DateStr & ".csv", FileFormat:=xlCSV, CreateBackup:=False

 

[luke1438]

That works saving it as a date but the formatting is wrong and it is not pulling in the value from "J7". Why is it not pulling in ThisFile and DateStr? This is weird, it should work. It should be pulling in those defined values into the path.

 

[kevin9999]

You need to be very specific about which object you're working with. For example, following the line:

Workbooks.Add

the new workbook becomes the active workbook, so the line:

ThisFile = Range("J1").Value

gets the value of J1 in the newly created workbook - which is blank. (I note you say not pulling in the value from "J7" in post #5 but you use J1 in your code.

 

[luke1438]

Ah, completely missed that - thanks. I put the code before the:

Workbooks.Add

And it works perfectly - thanks so much for your help.

Regards
Luke

 

[kevin9999]

Ah, completely missed that - thanks. I put the code before the:

Workbooks.Add

And it works perfectly - thanks so much for your help.

Regards
Luke

Happy to help, and thanks for the feedback