Code works only once

[MRsuave]

Hi,

I'm fairly new to VBA and I made up some code here that makes me transfer data from one workbook to another.

I have two workbook variables names wbk1 and wbk2.

The problem is, the code works only once. Afterwards, I have to close the second workbook file (wbk2) in order for it to work again.
And if I don't, the variable wbk2 becomes the same file as wbk1 (I have verified this using Msgbox).

What's really frustrating is that I just copy-pasted this sub from the sub directly above it and changed the file path for wbk2 and it works perfectly fine there.

Could you guys help me please?


Here is my code for your reference:

Sub PasteComp()

Dim wbk1, wbk2 As Workbook
Dim sht1, sht2 As Worksheet
Dim i, j As Long
Dim strfilename, strfilepath, panel, complog, area As String

    Application.ScreenUpdating = False

    strfilename = InputBox("Enter file name.", "File Name")
    area = InputBox("Enter area number", "Area Number")
    panel = "P" & InputBox("Enter panel number.", "Panel Number")
    
    strfilepath = "filepath for workbook 1" & strfilename & ".xls"
    complog = "filepath for workbook 2.xlsx"

    Set wbk1 = Workbooks.Open(strfilepath)
    Set wbk2 = Workbooks.Open(complog)

    MsgBox wbk1.Name
    MsgBox wbk2.Name
       
    i = 1
    j = 1
   
    Set sht1 = wbk1.Sheets("Lot Layout")
    Set sht2 = wbk2.Sheets("Comparison " & panel & " at A" & UCase(area))
    
    j = sht1.Range("13:13").Cells.SpecialCells(xlCellTypeConstants).Count         '''''Number of Columns
    i = sht1.Range("A14").End(xlDown).Row - 13                                             '''''Number of Rows
    MsgBox j & "Columns and " & i & " Rows."

    Dim vrow, vcol As Long
    vrow = 1
    vcol = 1

    For vrow = 1 To i
        For vcol = 1 To j

     If IsEmpty(sht1.Cells(13 + vrow, 1 + vcol)) = False Then

          sht2.Cells(91 + vrow, 1 + vcol) = sht1.Cells(13 + vrow, 1 + vcol)
          sht2.Cells(91 + vrow, 1 + vcol).Font.Name = "Calibri"
          sht2.Cells(91 + vrow, 1 + vcol).Font.Size = 11
          sht2.Cells(91 + vrow, 1 + vcol).Font.Color = vbBlack

          If Err Then
               Debug.Print Err.Description
               Exit Sub
          End If

     End If

        Next vcol
    Next vrow

wbk1.Close savechanges:=False
Application.ScreenUpdating = True

End Sub

 

[dmt32]

Hi,
Test if workbook is already open before Workbook.Open & see if this solves your issue.

Also, a comment on your variable declarations

Dim wbk1, wbk2 As Workbook
Dim sht1, sht2 As Worksheet
Dim i, j As Long
Dim strfilename, strfilepath, panel, complog, area As String

Only the last variable in each line is declared with specified data type the rest will be variants. You must explicitly declare each variable with required data type even though they are on the same line

Dim wbk1 As Workbook, wbk2 As Workbook
Dim sht1 As Worksheet, sht2 As Worksheet
Dim i As Long, j As Long
Dim strfilename As String, strfilepath As String, panel As String, complog As String, area As String

Dave

 

[MRsuave]

Test if workbook is already open before Workbook.Open & see if this solves your issue.

Okay I will try that. But this the exact same code that I copy-pasted from a previous sub. And with the previous code it works perfectly fine without having to check if the workbook is open. Is there any way that this is related to the number of characters in the workbook name?
Cause this is the file name for that workbook: "PVD termination depth Comparison (Design Vs Actual) based on PCPT"

Also, a comment on your variable declarations

Dim wbk1, wbk2 As Workbook
Dim sht1, sht2 As Worksheet
Dim i, j As Long
Dim strfilename, strfilepath, panel, complog, area As String

Only the last variable in each line is declared with specified data type the rest will be variants. You must explicitly declare each variable with required data type even though they are on the same line
Dave

Is this really the case? Cause I have been declaring like this ever since and I haven't had any real issues related to variable type so far.
Anyway I'll just separate them to be sure.

Thanks for your reply!

 

[RoryA]

Is this really the case?

Yes, it is.

 

[MRsuave]

Yes, it is.

Oooohhh. I didn't know that! I got used to declaring variables in C++. Thanks!

Anyway I seem to have found the problem. In the previous sub (the one i copied from), I first opened the workbooks before setting the variables for them.

Like this:

Workbooks.Open("filepath\filename.xlsx")
set wbk1 = workbooks("filename.xlsx")

It seems to be working properly now. Though I dunno what's the difference.

Maybe anyone can give a little explanation?

Anyway, thanks for the help guys!