specifing a file path in vba

[stevebutler]

i want to save a copy of a sheet from Workbook A, into Workbook b.

I want Workbook B (with the new copied sheet) to be saved in the same folder as Workbook A

I thought i could use ThisWorkbook.path but i can't seem to get it to work.

also, i want to add the date to the end of the file name.

thanks in advance.
steve

 

[Sprucy]

Hey Steve,


Does Workbook B already exsist or will the macro create it?
See link below, hope it helps.


http://www.mrexcel.com/board2/viewtopic.php?t=181533

Cheers,
Sprucy

 

[Von Pookie]

How about:

Sub CopySheet()
Dim wbPath As String
Dim wbB As Variant
Dim NewName As String

'file path of current workbook
wbPath = ThisWorkbook.Path & "\"

'full path to Workbook B
wbB = wbPath & "WorkbookB.xls"

'full path and name that workbook with copied sheet will be saved as
NewName = wbPath & "WorkbookB " & Format(Date, "mm-dd-yy") & ".xls"

'check if workbook B is open
If WorkbookIsOpen("WorkbookB.xls") = False Then
    'if not open, open workbook
    Set wbB = Workbooks.Open(wbB)
Else
    Set wbB = Workbooks("WorkbookB.xls")
End If

Application.ScreenUpdating = False

'copy first sheet of workbook to WorkbookB
ThisWorkbook.Sheets(1).Copy _
After:=wbB.Sheets(Sheets.Count)

Application.ScreenUpdating = True

'save WorkbookB in same folder with new name
With wbB
    .SaveAs Filename:=NewName
    .Close 'close workbookB
End With

End Sub

Private Function WorkbookIsOpen(wbname) As Boolean
'   Returns TRUE if the workbook is open
Dim x As Workbook
On Error Resume Next
Set x = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True Else WorkbookIsOpen = False
End Function

 

[Norie]

Steve

Can we see what you've tried?

How isn't it working?

 

[stevebutler]

I don't know what i'm doing wrong...everytime i try this i get an error 400.

Workbook B does not exist...it should be created by the macro...but i tried to adjust and it didn't work

any ideas??

 

[stevebutler]

fname = ThisWorkbook.Path & "\" & Date & ".xls"
Set wbk = Workbooks.Add
ThisWorkbook.Worksheets("Sheet1").Copy _
After:=wbk.Worksheets("Sheet1")
wbk.SaveAs fname


this is probably easy for you guys...i'm just missing something

 

[Von Pookie]

Oh! I see what you're doing.

You can use the date in the file name, but you have to format it--you can't use the mm/dd/yy format because it will interpret the slashes as directories. You can use *dashes* in filenames, though.

In place of the word Date, use

Format(Date,"mm-dd-yy")

 

[stevebutler]

that was it!!

i also had to change the name of one of the worksheets...(so they didn't have the same name)

thank you so much, i learned a lot

steve