xtracting a range of non-contiguous cells from either of Two sheets in a folder of worksheets

Question

xtracting a range of non-contiguous cells from either of Two sheets in a folder of worksheets

Nandakishore 1

Extracting a range of non-contiguous cells within number of excel files in a particular folder (data has to be pulled from either of 2 UNIQUE SHEETS)

I have the below code for pulling data (range of cells) that are non-contiguous and pasting them in a new sheet. However, the code needs to look for the data in either of the 2 sheets , namely - "summary1" or "extract1".

[Note- Only one of the two sheets would be available in each file] I can successfully pull for one of them but if i add both of them using "On Error Resume Next" i get an error. Kindly guide me on how to resolve this!

Any suggestions or tips are much appreciate!!

Code:

VBA Code:
Sub PIdataextraction()

Dim myFile As String, path As String
Dim erow As Long, col As Long

path = "C:\Users\New\"
myFile = Dir(path & "*.xl??")

Application.ScreenUpdating = False

Do While myFile <> ""
Workbooks.Open (path & myFile)
Windows(myFile).Activate

Set copyrange = Sheets("summary1").Range("B4,E7,E9,E11,E13,E15,I12,J22,C24,C25,C26,I11,R16")

On Error Resume Next

Set copyrange = Sheets("extract1").Range("B4,E7,E9,E11,E13,E15,I12,J22,C24,C25,C26,I11,R16")

Windows("MasterFile.xlsm").Activate

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

col = 1
For Each cel In copyrange
cel.Copy

Cells(erow, col).PasteSpecial xlPasteValues

col = col + 1

Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox "Data has been Compiled,Please Check!"

End Sub

1 answer

Your answer

Answer 1

Viorel 122.6K

If you prefer 'On Error', then consider this code:

Dim ws As Worksheet
Set ws = Nothing

On Error Resume Next
Set ws = Sheets("summary1")
If ws Is Nothing Then
    Set ws = Sheets("extract1")
End If
On Error GoTo 0

If ws Is Nothing Then
    MsgBox "Sheets not found"
Else
    Dim copyrange As Range
    Set copyrange = ws.Range("B4,E7,E9,E11,E13,E15,I12,J22,C24,C25,C26,I11,R16")
    . . . .
End If

Share via

xtracting a range of non-contiguous cells from either of Two sheets in a folder of worksheets

1 answer

Your answer