Editing Between Workbooks 1

[AGlazer]

Hi, everyone.

I'm working on a macro that compares values between two workbooks. I know the code to do the comparison is correct (I've used it on worksheets in the same workbook, but I'm having trouble making it work across workbooks). The code excerpt is below. Any assistance would be greatly appreciated. This is going to be done multiple times, which is why I'm setting it up as two subs (so I can later install loops, etc.)

Sub SelectionTool()
Dim oBook As Workbook
Dim nBook As Workbook
Set oBook = Workbooks("benchmark.xls&quot
Set nBook = Workbooks("hourly2.xls&quot
Call Mixer(12, Sheets("orgbrand-of&quot, Sheets("orgbrand-of&quot, "orgbrand-of", "L&quot
...
End Sub

Function Mixer(nItems As Integer, newSheet As Worksheet, oldSheet As Worksheet, newName As String, QuestionType As String)
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim sig As Single
Dim benchFile As String
Dim oldValue As Integer
Dim newValue As Integer
Dim mult As Integer
Dim oBook As Workbook
Dim nBook As Workbook

sig = 0.105
nItems = 12
Set oBook = Workbooks("benchmark.xls&quot
Set nBook = Workbooks("hourly2.xls&quot

' pick and choose the variables to use

For i = 1 To nItems Step 1
j = i + 4
k = i + 1
nBook.Activate
newValue = newSheet.Range("G" & j & "&quot.Value
newB = newSheet.Range("C" & j & "&quot.Value
oBook.Activate
oldValue = oldSheet.Range("G" & j & "&quot.Value
oldB = oldSheet.Range("C" & j & "&quot.Value
sumB = newB + oldB
nBook.Activate

If newValue < sig Then
If oldValue < sig Then
oBook.Activate
oldSheet.Select
Range(&quot;B&quot; & j & &quot;:G&quot; & j & &quot;&quot.Select
Selection.Copy
nBook.Activate
newSheet.Select
Range(&quot;I&quot; & k & &quot;&quot.Select
ActiveSheet.Paste
Range(&quot;J&quot; & k & &quot;&quot.Select
ActiveCell.FormulaR1C1 = sumB
Range(&quot;H&quot; & k & &quot;&quot.Select
ActiveCell.FormulaR1C1 = &quot;B&quot;
ElseIf oldValue > sig Then
nBook.Activate
newSheet.Select
Range(&quot;B&quot; & j & &quot;:G&quot; & j & &quot;&quot.Select
Selection.Copy
Range(&quot;I&quot; & k & &quot;&quot.Select
ActiveSheet.Paste
Range(&quot;H&quot; & k & &quot;&quot.Select
ActiveCell.FormulaR1C1 = &quot;N&quot;
End If
ElseIf newValue > sig Then
If oldValue < sig Then
oBook.Activate
oldSheet.Select
Range(&quot;B&quot; & j & &quot;:G&quot; & j & &quot;&quot.Select
Selection.Copy
nBook.Activate
newBook.Select
Range(&quot;I&quot; & k & &quot;&quot.Select
ActiveSheet.Paste
Range(&quot;H&quot; & k & &quot;&quot.Select
ActiveCell.FormulaR1C1 = &quot;O&quot;
ElseIf oldValue > sig Then
nBook.Activate
newSheet.Select
Range(&quot;B&quot; & j & &quot;:G&quot; & j & &quot;&quot.Select
Selection.Copy
Range(&quot;I&quot; & k & &quot;&quot.Select
ActiveSheet.Paste
Range(&quot;J&quot; & k & &quot;&quot.Select
ActiveCell.FormulaR1C1 = &quot;0&quot;
Range(&quot;N&quot; & k & &quot;&quot.Select
ActiveCell.FormulaR1C1 = &quot;.99&quot;
Range(&quot;H&quot; & k & &quot;&quot.Select
ActiveCell.FormulaR1C1 = &quot;0&quot;
End If
End If
Next i
End Function


Any help would be greatly appreciated.

Thanks,

Aaron

 

[SkipVought]

Hi,

I would FIRST qualify the Sheets passed as parameters with a workbook ...

Sub SelectionTool()
 Dim oBook As Workbook, oSheet As Worksheet
 Dim nBook As Workbook, nSheet As Worksheet
 Set oBook = Workbooks("benchmark.xls")
 Set nBook = Workbooks("hourly2.xls")
 Set oSheet = oBook.Worksheets("orgbrand-of")
 Set nSheet = nBook.Worksheets("orgbrand-of")
 Call Mixer(12, nSheet, oSheet, "orgbrand-of", "L")
'...
End Sub

Then I would NOT use Activate. I would use the Copy-Destination method

Function Mixer(nItems As Integer, newSheet As Worksheet, oldSheet As Worksheet, newName As String, QuestionType As String)
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim sig As Single
Dim benchFile As String
Dim oldValue As Integer
Dim newValue As Integer
Dim mult As Integer
Dim oBook As Workbook
Dim nBook As Workbook

sig = 0.105
nItems = 12
Set oBook = Workbooks("benchmark.xls")
Set nBook = Workbooks("hourly2.xls")

' pick and choose the variables to use

    For i = 1 To nItems Step 1
        j = i + 4
        k = i + 1
        newValue = newSheet.Range("G" & j & "").Value
        newB = newSheet.Range("C" & j & "").Value
        oldValue = oldSheet.Range("G" & j & "").Value
        oldB = oldSheet.Range("C" & j & "").Value
        sumB = newB + oldB
               
        If newValue < sig Then
            If oldValue < sig Then
              oldSheet.Range(&quot;B&quot; & j & &quot;:G&quot; & j & &quot;&quot;).Copy _
                Destination:=newSheet.Range(&quot;I&quot; & k & &quot;&quot;)
              With newSheet
                .Range(&quot;J&quot; & k & &quot;&quot;).Value = sumB
                .Range(&quot;H&quot; & k & &quot;&quot;).Value = &quot;B&quot;
              End With
            ElseIf oldValue > sig Then
              newSheet.Range(&quot;B&quot; & j & &quot;:G&quot; & j & &quot;&quot;).Copy _
                Destination:=oldSheet.Range(&quot;I&quot; & k & &quot;&quot;)
              oldSheet.Range(&quot;H&quot; & k & &quot;&quot;).Value = &quot;N&quot;
            End If
        ElseIf newValue > sig Then
            If oldValue < sig Then
              oldSheet.Range(&quot;B&quot; & j & &quot;:G&quot; & j & &quot;&quot;).Copy _
                Destination:=newSheet.Range(&quot;I&quot; & k & &quot;&quot;)
              newSheet.Range(&quot;H&quot; & k & &quot;&quot;).Value = &quot;O&quot;
            ElseIf oldValue > sig Then
              nBook.Activate
              With newSheet
                .Range(&quot;B&quot; & j & &quot;:G&quot; & j & &quot;&quot;).Copy _
                    Destination:=newSheet.Range(&quot;I&quot; & k & &quot;&quot;)
                .Range(&quot;J&quot; & k & &quot;&quot;).Value = &quot;0&quot;
                .Range(&quot;N&quot; & k & &quot;&quot;).Value = &quot;.99&quot;
                .Range(&quot;H&quot; & k & &quot;&quot;).Value = &quot;0&quot;
              End With
            End If
        End If
    Next i
End Function

Hope this helps



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[AGlazer]

Skip,

Once again you've solved my problem -- I don't know how I'd survive without your (and the rest of the Tek-Tip Users') help!

Much appreciated.

Aaron