VBA Copy filename to another excel file that serves as database

[miguel_z]

Hello dear colleagues. Perhaps this topic was already discussed but I was unable to find similar thread so here's the problem.

I have a certain excel file. I am trying to write a code that will perform following steps.

By clicking on a button I would like to save a copy of this file with a speficific name that I am extracting from cell "E5" to a specific location and then write the name this file into a "database" file into a certain folder.
I am new to VBA and I tried to prepare the code myself but there is something wrong with it.

Here is the whole code:

Sub save_filename()

Dim Path As String
Dim filename As String
Dim wbCopy As Workbook
Dim wbDest As Workbook
Dim Fname As String
Dim lDestLastRow As Long

'Message box for file save confirmation
If MsgBox("Do you want to write a copy of this file into m:\files ?", vbYesNo + vbQuestion) = vbYes Then

Path = "m:\files"

filename = Range("E5").Value & ".xlsm"

ActiveWorkbook.SaveAs Path & filename

End If

'Defining source and destination workbooks
Set wbCopy = ActiveWorkbook
Set wbDest = Workbooks.Open("m:\files\DATABASE.xlsx")

'Copying filename that is located in "E5" cell
wbCopy.Sheets("Raport A3").Range("E5").Copy
lDestLastRow = wbDest.Cells(wbDest.Rows.Count, "A").End(xlUp).Offset(1).Row
wbDest.Sheets("Sheet1").Range("A" & lDestLastRow).Paste
 
Workbooks("DATABASE.xlsx").Close SaveChanges:=True
ActiveWorkbook.Close

Application.DisplayAlerts = False
Application.DisplayAlerts = True


End Sub

I am getting a "Run-time error 438 - object doesnt support this property or method" in this part of the code:

lDestLastRow = wbDest.Cells(wbDest.Rows.Count, "A").End(xlUp).Offset(1).Row

Could someone please help me to understand why this error occurs?

Many thanks...

 

[Joe4]

A few issues I see right off the bat (without diving too far into it):

1. Do NOT use reserved names (names of existing Excel/VBA functions, properties, methods, etc) like "Path" as the name of your variables, procedure names, or function names.
Doing so can cause errors and unexpected results.

2. You do not have a slash after "m:\files" in your path (you should have "m:\files\").

3. You have a workbook, but no worksheet reference here:

lDestLastRow = wbDest.Cells(wbDest.Rows.Count, "A").End(xlUp).Offset(1).Row

I don't think Excel likes that - using a Workbook reference but no Worksheet reference.

 

[miguel_z]

Thanks for reply @Joe4
I will correct points number 1 & 2.

Could you be more specific about your third remark? How should I declare the worksheet there?

Many thanks...

 

[Joe4]

Look at here how you have both the workbook and worksheet references:

wbCopy.Sheets("Raport A3").Range("E5").Copy

Here, you only have a workbook reference, but no worksheet reference:

lDestLastRow = wbDest.Cells(wbDest.Rows.Count, "A").End(xlUp).Offset(1).Row

I am pretty sure Excel does not like that.
I am not sure that even with a worksheet reference, if Excel will like that you include the workbook reference in there (it didn't in my testing).

Since the destination workbook seems to be the open one at the time that line of code is hit, I think you just need the sheet reference in there, i.e.

lDestLastRow = Sheets("sheetname").Cells(Sheets("sheetname").Rows.Count, "A").End(xlUp).Offset(1).Row

Just replace "sheetname" with the actual name of the sheet.