Code works only once

MRsuave

New Member
Joined
Jun 4, 2018
Messages
3
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:





Code:
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
 
Last edited by a moderator:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Hi,
Test if workbook is already open before Workbook.Open & see if this solves your issue.

Also, a comment on your variable declarations

Code:
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

Code:
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
 
Last edited:
Upvote 0
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

Code:
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!
 
Upvote 0
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:

Code:
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! :)
 
Upvote 0

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top