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