source sheet name as a wild card
Barb,
Thanks for looking into this for me, I placed your portion of the code where
I thought it should go but now it is not pulling anything out. Here is what
the code looks like now:
Sub RDB_Copy_Sheet()
Dim myFiles As Variant
Dim myCountOfFiles As Long
Dim WS As Excel.Worksheet
Dim WB As Excel.Workbook
Set WB = ThisWorkbook
For Each WS In WB.Worksheets
If WS.Name Like "Report*" Then
myCountOfFiles = Get_File_Names( _
MyPath:="H:\myprojdir\GWIS\Humble\Test\part2", _
Subfolders:=True, _
ExtStr:="*.xl*", _
myReturnedFiles:=myFiles)
If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If
Get_Sheet _
PasteAsValues:=True, _
SourceShName:="Report", _
SourceShIndex:=1, _
myReturnedFiles:=myFiles
End If
Next WS
End Sub
Any suggestions??
Thanks
"Barb Reinhardt" wrote:
You can do something like this
Dim WS as Excel.worksheet
Dim WB as Excel.Workbook
Set WB = ThisWorkbook
For each WS in WB.Worksheets
If WS.Name Like "Report*" then
'Do what you want to do
End iF
next WS
Hope that helps.
Barb Reinhardt
"James" wrote:
I was working on this late last night, than I remembered Ron de Bruin page
and he had some written for what I needed. I need a small modification on
this code. In the SourceShName line I have it pulling all sheets with the
name "Report", but some are my sheet names are called "Report 1, Report 2..."
How can I make it look for anything called Report* or something like that.
Sub RDB_Copy_Sheet()
Dim myFiles As Variant
Dim myCountOfFiles As Long
myCountOfFiles = Get_File_Names( _
MyPath:="H:\myprojdir\GWIS\Humble\Test\part2", _
Subfolders:=True, _
ExtStr:="*.xl*", _
myReturnedFiles:=myFiles)
If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If
Get_Sheet _
PasteAsValues:=True, _
SourceShName:="Repor*", _
SourceShIndex:=1, _
myReturnedFiles:=myFiles
End Sub
Thanks
|