I want to copy a sheet from files X,Y and Z to a new file Master11Aug2016. If there already exists a file called Master11Aug2016 in the folder it works, but when there is not it returns an error on the copy line stating .
Dim place As String
place = "C:Users..."
Dim fname As String
dt = Format(CStr(Now), "ddmmmyy")
fname = "Master" & dt
Dim path As String
path = place & fname & ".xlsx"
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=(path)
Dim X, Y, Z As String
X = Dir(place & "X.xlsx")
Y = Dir(place & "Y.xlsx")
Z = Dir(place & "Z.xlsx")
Workbooks.Open (X)
Workbooks(X).Sheets("Sheet1").Calculate
Workbooks(X).Sheets("Sheet1").Copy Before:=Workbooks(path).Sheets(1) [error here]
Workbooks(X).Close
...
So the code fails first time around. but then works the second time. Any help would be greatly appreciated. Cheers