MachuPichu92
New Member
- Joined
- Feb 4, 2020
- Messages
- 15
- Office Version
- 2016
- Platform
- Windows
Hello,
I'm facing issues with the my simple code below.
I want to create a loop that would open a specific excel file, copy cell value and paste it to another workbook.
See my code:
When I set Workbooks.Open(FILE & "i") macro looks for FILE1.xlsx file instead of FILE1, but if I insert Workbooks.Open(FILE1) it successfully opens correct file.
Thank you for your answers.
I'm facing issues with the my simple code below.
I want to create a loop that would open a specific excel file, copy cell value and paste it to another workbook.
See my code:
VBA Code:
Dim TWB As Workbook
Dim WB As Workbook
Dim WS As Worksheet
Dim LR As Long
Dim LC As Long
ReDim FILE(6)
Dim i As Long
Dim DIR As String
LR = Range("A" & Rows.Count).End(xlUp).Row
LR = LR + 1
Set TWB = ThisWorkbook
Set WS = TWB.Worksheets("List1")
DIR = "C:\Users\VB\Desktop\V\"
FILE1 = DIR & "test1.xlsx"
FILE2 = DIR & "test2.xlsx"
FILE3 = DIR & "test3.xlsx"
FILE4 = DIR & "test4.xlsx"
FILE5 = DIR & "test5.xlsx"
FILE6 = DIR & "test6.xlsx"
For i = 1 To 6
Set WB = Workbooks.Open(FILE & "i")
ActiveWorkbook.Sheets("Sheet1").Cells(1, 1).Select
If Cells(1, 1).Value <> "" Then
Selection.Copy
TWB.Activate
WS.Cells(LR, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
LR = LR + 1
WB.Close
Else:
WB.Close
End If
Next i
End Sub
When I set Workbooks.Open(FILE & "i") macro looks for FILE1.xlsx file instead of FILE1, but if I insert Workbooks.Open(FILE1) it successfully opens correct file.
Thank you for your answers.