If I guessed right in my previous post about your needs, then you should
be
able to adapt this code to your needs. To use it, open a brand new
workbook
in Excel, use:
[Alt]+[F11] to get into the VB Editor, then from its menu bar, use Insert
|
Module to create a new code module. Copy the code below and paste it into
the module.
Save that workbook into the same folder with all of the 200 workbooks to
be
modified and then run the macro.
This code assumes that the workbook with the code and all of the other
workbooks are in the same folder (shouldn't be any other .xls files in
that
folder - they could get corrupted or code could fail). It also assumes
that
the names of the 3 worksheets in those other workbooks are all the same:
2006, 2007 and some other name you didn't specify ... you can alter the
code
to provide that 3rd sheet's name.
Sub RecordDifferences()
'to use this macro
'Create a folder that only contains this workbook
'and the 200 other workbooks that you have to work with
'Open this workbook and run this macro
'ASSUMPTIONS:
' the 3 worksheets involved have the same name
' in all workbooks to be used
' that you want to copy text from col C to
' the 3rd tab sheet along with putting
' values into cells in columns D-O there.
'
'change these constants as required
'
'names of sheets involved
'change names here as required
Const tab1Name = "2006"
Const tab2Name = "2007"
Const tab3Name = "Differences"
'column where phrase 'Total non-salary' occurs
Const phraseCol = "C"
'first column with numeric data to be used
Const firstDataCol = "D"
'column with last numeric entry
Const lastCol = "O"
'row with 1st set of numbers to work with
Const firstDataRow = 1 ' assumes no labels in row 1
'end of user change area
Dim basicPath As String
Dim anyFile As String
Dim lastDataRow As Long
Dim WS1 As Range ' will be tab1 C1
Dim WS2 As Range ' will be tab2 C1
Dim WS3 As Range ' will be tab3 C1
Dim offsetToLastColumn As Integer
Dim cOffset As Integer
Dim rOffset As Long
offsetToLastColumn = Range(lastCol & "1").Column - _
Range(phraseCol & "1").Column
'find the path to the folder that this workbook
'and all the others is/are stored in.
basicPath = Left(ThisWorkbook.FullName, _
InStrRev(ThisWorkbook.FullName, "\"))
' get first filename
anyFile = Dir$(basicPath & "*.xls")
' speed things up
Application.ScreenUpdating = False
'begin the real work
Do While anyFile <> ""
If anyFile <> ThisWorkbook.Name And _
UCase(Right(anyFile, 4)) = ".XLS" Then
'open the other workbook, it becomes active
Workbooks.Open basicPath & anyFile
Set WS1 = Workbooks(anyFile).Worksheets(tab1Name). _
Range(phraseCol & firstDataRow)
Set WS2 = Workbooks(anyFile).Worksheets(tab2Name). _
Range(phraseCol & firstDataRow)
Set WS3 = Workbooks(anyFile).Worksheets(tab3Name). _
Range(phraseCol & firstDataRow)
'find row with "Total non-salary" in it
lastDataRow = _
Workbooks(anyFile).Worksheets(tab1Name). _
Range(phraseCol & Rows.Count).End(xlUp).Row - 1
cOffset = 0 ' initialize/clear
rOffset = 0 ' initialize/clear
For cOffset = 0 To offsetToLastColumn
For rOffset = 0 To lastDataRow
If cOffset > 0 Then
'handles numeric values
'if you want hard values use this
'(remove ' in front of WS3.... and
'either comment or remove the formula
'creating statement below).
'WS3.Offset(rOffset, cOffset).Value = _
WS1.Offset(rOffset, cOffset).Value - _
WS2.Offset(rOffset, cOffset).Value
'if you want formulas, use this
'(which will override hard values above unless
'you comment/delete this next statement
'I believe this will be better for you, since
'you're working with 2007 values, and 2007 is
'not over with just yet!
WS3.Offset(rOffset, cOffset).Formula = _
"='" & tab1Name & "'!" & _
WS1.Offset(rOffset, cOffset).Address & _
" - '" & tab2Name & "'!" & _
WS2.Offset(rOffset, cOffset).Address
Else
'just copies from column C in WS1 to C in WS3
WS3.Offset(rOffset, cOffset).Value = _
WS1.Offset(rOffset, cOffset).Value
End If
Next ' rOffset loop end
Next ' cOffset loop end
ActiveWorkbook.Close True ' save with changes
End If ' filename test
anyFile = Dir$ ' get next file name
Loop ' file retrieval loop end
Set WS1 = Nothing
Set WS2 = Nothing
Set WS3 = Nothing
Application.ScreenUpdating = True
End Sub
JLatham said:
I agree with Don Guillett - not clear. You mention only 3 sheets, how do
we
get from those to the other 197?
Or are you talking about workBOOKs -
Workbook: Excel file with 1 or more worksheets in it
Worksheet: a single tab within a workbook.
Plus you say "...fill in the rows depending on what is in column C on tab
1
or 2."
fill in what rows on what tab? What type of data is in column C on tabs
1
and 2 (from earlier statement, sounds like it should be dollar values).
What I *think* I hear you asking for is a macro that will open each of
the
200 workbooks, calculate or place formulas in Tab 3 that will have the
differences between tabs 1 and 2 in that sheet, close the workbook and
move
on to the next?
:
Tab one is 2006, tab two is 2007.. Tab 3 is difference.
Tab 1 (2006) will have text then $$ in columns C to O (12 columns and
total ), Tab 2 will have text then $$ in columns C to O ( 12 columns
and
total).
Tab 3 will have Tab 1 less Tab 2 in columns C to O.
Pretty much a standard worksheet BUT there will be abut 200 worksheets
and
it seems to me to be easier with a macro that will fill in the rows
depending on what is in column C on tab 1 or 2.
The worksheets will be different length, but the last line will read
"Total non-salary" ..
Any help would be appreciated as to getting this done without 5 minutes
a
worksheet or about 16 hours..
