I am have some trouble with selecting ranges.
its a bit of a scenario-
I have one Master workbook with multiple sheets
-I am trying to import other workbooks' data into the master sheet in its respective sheet
--I have multiple sub workbooks or sub Master workbooks with identical sheets (identical in the sheets I need to import)
where I am having trouble is in the sheet selection when pasting in the Master workbook
-particularly with the line of code:
--'With Worksheets(ArraySelectCount2)' is where I am getting subscript error
some of this code I have found on this forum which has worked great for a single sheet
its a bit of a scenario-
I have one Master workbook with multiple sheets
-I am trying to import other workbooks' data into the master sheet in its respective sheet
--I have multiple sub workbooks or sub Master workbooks with identical sheets (identical in the sheets I need to import)
where I am having trouble is in the sheet selection when pasting in the Master workbook
-particularly with the line of code:
--'With Worksheets(ArraySelectCount2)' is where I am getting subscript error
some of this code I have found on this forum which has worked great for a single sheet
Code:
Sub SelectOpenCopy()
Dim vaFiles As Variant
Dim i As Long
Dim i2 As Long
Dim wb As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet
Dim File_Path As String
File_Path = Range("File_Path").Value
'IMPORTED SHEETS
Dim ArraySelect As Variant
ArraySelect = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")
Dim ArraySelectCount As Variant
'MASTER SHEETS
Dim ArraySelect2 As Variant
ArraySelect2 = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")
Dim ArraySelectCount2 As Variant
MsgBox "Select the files to import"
vaFiles = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", _
Title:="Select files", MultiSelect:=True)
If IsArray(vaFiles) Then
For i = LBound(vaFiles) To UBound(vaFiles)
Set wb = Workbooks.Open(Filename:=vaFiles(i))
For Each ArraySelectCount In ArraySelect
With wb.Worksheets(ArraySelectCount)
If Range("C3").Value <> "" Then
Range("C2").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Offset(1).Select
Selection.Copy
Windows("" & File_Path & "").Activate
For i2 = LBound(ArraySelect2) To UBound(ArraySelect2)
' ''Set wb2 = Worksheets(ArraySelect2)
For Each ArraySelectCount2 In ArraySelect2
With Worksheets(ArraySelectCount2)
If Range("B2").Value = "" Then
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Selection.PasteSpecial Paste:=xlPasteValues
Else
Range("B1").Select
Range("B1").End(xlDown).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Selection.PasteSpecial Paste:=xlPasteValues
End If
End With
Next ArraySelectCount2
Next i2
wb.Application.CutCopyMode = False
End If
End With
Next ArraySelectCount
wb.Close savechanges:=False
Next i
End If
End Sub
