Macros - get information from Multiple sheets in a work book?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created the following macro. Where I am calling in the "Details"
Worksheet I would like to call in the same info for all of the Worksheets in
the Workbook. How do I do that?

Thanks in advance for any help!!!!




Sub TMSTabulate()
' For each workbook in source directory
' open workbook
'

'*

TMSVersion = ThisWorkbook.CustomDocumentProperties("TMSVersion")
FiscalYear = 2005
TabCell = 2
Ledger = "BUDGET"
Period = 1


With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Worksheets("Settings").Range("BDIFileLocation")
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
wbName = .FoundFiles(i)
' MsgBox .FoundFiles(i)
Workbooks.Open (wbName)
Application.StatusBar = "Processing " + wbName
OKFlag = False




For k = ThisWorkbook.Worksheets("Settings").Cells(6, 7) To
ThisWorkbook.Worksheets("Settings").Cells(6, 9)
For x = ThisWorkbook.Worksheets("Settings").Cells(8, 7)
To ThisWorkbook.Worksheets("Settings").Cells(8, 9)
If ActiveWorkbook.Worksheets("details").Cells(k, 1)
<> "" Then

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 26) = FiscalYear '

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 3) = Ledger 'Ledger

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 1) = "B" 'Record
Type

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 22) = "ORIGINAL1"
'Scenario

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 8) =
ActiveWorkbook.Worksheets("details").Cells(k, 5) 'Operating Unit

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 5) =
ActiveWorkbook.Worksheets("details").Cells(k, 2) 'Accounts

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 9) =
ActiveWorkbook.Worksheets("details").Cells(k, 4) 'Product

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 7) =
ActiveWorkbook.Worksheets("details").Cells(k, 3) 'Department

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 2) =
ActiveWorkbook.Worksheets("details").Cells(k, 1) 'Business Unit

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 28) =
ActiveWorkbook.Worksheets("details").Cells(k, x) 'Amount$

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 27) = Period 'period
TabCell = TabCell + 1
End If
Period = Period + 1
Next x
Period = 1
Next k



ActiveWorkbook.Close
Next i
Else
MsgBox "There were no TMS files found."
End If

End With

Application.StatusBar = "Done. "

End Sub
 
Each time a matched file is found, it is opened within the CurW
object,
so i replaced the ActiveWorkbook references to CurWB

Sub TMSTabulate()
' For each workbook in source directory
' open workbook
'

'*


FiscalYear = 2005
TabCell = 2
Ledger = "BUDGET"
Period = 1


Dim CurWB As Object

With Application.FileSearch
.NewSearch
.LookIn = ThisWorkbook.Worksheets("Settings").Range("BDIFileLocation")
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
wbName = .FoundFiles(i)
' MsgBox .FoundFiles(i)
Set CurWB = Workbooks.Open(wbName)

Application.StatusBar = "Processing " + wbName
OKFlag = False




For k = ThisWorkbook.Worksheets("Settings").Cells(6, 7) T
ThisWorkbook.Worksheets("Settings").Cells(6, 9)
For x = ThisWorkbook.Worksheets("Settings").Cells(8, 7) T
ThisWorkbook.Worksheets("Settings").Cells(8, 9)
If CurWB.Worksheets("details").Cells(k, 1) <> "" Then

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 26) = FiscalYea
'

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 3) = Ledge
'Ledger

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 1) = "B" 'Recor
Type

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 22) = "ORIGINAL1"
'Scenario

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 8)
CurWB.Worksheets("details").Cells(k, 5) 'Operating Unit

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 5)
CurWB.Worksheets("details").Cells(k, 2) 'Accounts

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 9)
CurWB.Worksheets("details").Cells(k, 4) 'Product

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 7)
CurWB.Worksheets("details").Cells(k, 3) 'Department

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 2)
CurWB.Worksheets("details").Cells(k, 1) 'Business Unit

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 28)
CurWB.Worksheets("details").Cells(k, x) 'Amount$

ThisWorkbook.Worksheets("Tabulation").Cells(TabCell, 27) = Perio
'period
TabCell = TabCell + 1
End If
Period = Period + 1
Next x
Period = 1
Next k



CurWB.Close
Next i
Else
MsgBox "There were no TMS files found."
End If

End With

Application.StatusBar = "Done. "

End Su
 
Thanks but I am looking to replace the Worksheet not the workbook. IN the
macro I had I speicfally called in the "Details" Sheet. I want to call in
ALL sheets in a particular workbook.

thanks!
 
Thanks But i am looking to get info from muliple sheets in Multiple
workbooks. I have the workbooks set. I need the all of the worksheets in
each workbook, not just the "details" Worksheet.
 
Back
Top