abdelfattah
Well-known Member
- Joined
- May 3, 2019
- Messages
- 1,429
- Office Version
- 2019
- 2010
- Platform
- Windows
hi,
i have about 3 files and the main file to merge data the code gives me error subscript out of range in this line Set ws = Workbooks("merge file").Worksheets("sheet1")
i'm sure about the name's main file
i have about 3 files and the main file to merge data the code gives me error subscript out of range in this line Set ws = Workbooks("merge file").Worksheets("sheet1")
i'm sure about the name's main file
VBA Code:
Option Explicit
Private Sub CommandButton1_Click()
Dim Path
Dim File
Dim wb As Workbook
Dim x
Dim lr, lr2
Dim ws As Worksheet
Set ws = Workbooks("merge file").Worksheets("sheet1")
Application.ScreenUpdating = False
With ws.Range("a2:r100000"): .ClearContents
.Activate
End With
Path = ThisWorkbook.Path & "\file\"
File = Dir(Path & "*.xlsx*")
Do While File <> ""
Set wb = Workbooks.Open(Path & File)
If Not IsError(x) Then
lr2 = wb.Worksheets(1).Cells(Rows.Count, "a").End(xlUp).Row
wb.Worksheets(1).Range("a2:r" & lr2).Copy
lr = ws.Range("a" & Rows.Count).End(3).Row + 1
ws.Range("a" & lr).PasteSpecial xlPasteValues
Application.CutCopyMode = False
End If
wb.Close
File = Dir
Loop
Application.ScreenUpdating = True
End Sub