Hi everyone,
I'm trying to get this work and I can't for the life of me figure out why it doesn't
I've got a few workbooks in a folder, and if any of them contain "ValueIWant" in range A4:T4 in any of their sheets, I want to copy the range of A6:T500 into a summary sheet and paste it there. I'm aware that I haven't done the lastrow stuff for the paste destination, feel free to add that too, but i can probably fix that, its erroring out on me when i put the for each ws loop in and I'm stumped as to why. Hopefully it's something simple!
Thanks in advance.
I'm trying to get this work and I can't for the life of me figure out why it doesn't
I've got a few workbooks in a folder, and if any of them contain "ValueIWant" in range A4:T4 in any of their sheets, I want to copy the range of A6:T500 into a summary sheet and paste it there. I'm aware that I haven't done the lastrow stuff for the paste destination, feel free to add that too, but i can probably fix that, its erroring out on me when i put the for each ws loop in and I'm stumped as to why. Hopefully it's something simple!
Thanks in advance.
Code:
Sub CopySelectedRanges()
Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim ws As Worksheet
Application.DisplayAlerts = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "C:\Users\Me\Downloads\Folder" 'Change as needed
FileName = Dir(Path & "\*.xl*", vbNormal)
Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "\" & FileName)
For Each ws In Wkb.Worksheets
Set Rng = ws.Range("A4:T4")
For Each Cell In Rng
If Cell.Value = "ValueIWant" Then
'Sheets("Summary").Range("A1").Value = "Yes"
Sheets("Sheet1").Range("A5:T500").Copy Destination:=Sheets("Summary").Range("A10")
End If
Next Cell
Next ws
'Next
'Next ws
'Wkb.Close False
FileName = Dir()
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub