(I didn't notice your notE in your code. Sorry.)

Dave Peterson wrote:
>
> Glad you found the problem.
>
> (I didn't notice your not in your code. Sorry.)
>
> davegb wrote:
> >
> > Dave Peterson wrote:
> > > Are you getting the error on one of these lines?
> > >
> > > Set wbNew = Workbooks(ComboBox1.Text)
> > > Set wbOrig = Workbooks(ComboBox2.Text)
> > >
> > > If yes, then the current value in that combobox (that's causing the error)
> > > doesn't refer back to a workbook that's open.
> > >
> > > Could it be that the user hasn't made a choice and that .text value is still
> > > ""???
> > >
> >
> > Thanks for your reply, Dave.
> >
> > I found the problem. The user supplying the spreadsheets to be compared
> > has been renaming the first sheet instead of leaving it "Sheet1"! Doh!
> >
> > Thanks again to all.
> >
> > > davegb wrote:
> > > >
> > > > Tom Ogilvy wrote:
> > > > > Option Explicit
> > > > > Public wsNew As Worksheet
> > > > > Public wsOrig As Worksheet
> > > > > Public wbNew As Workbook
> > > > > Public wbOrig As Workbook
> > > > >
> > > > > needs to be placed at the top of a general module.
> > > > >
> > > > > Public variables in a general module have project wide scope. Public
> > > > > variables in a userform have module level scope by default although they can
> > > > > also be treated as properties of the class.
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > >
> > > > Tom, thanks for your help.
> > > >
> > > > I had declared the variables publicly in both the userform module and
> > > > the general module. When I read your reply, I remarked them out in the
> > > > userform module. Now I'm getting a Subscript out of range error on that
> > > > same code. Any suggestions?
> > > >
> > > > >
> > > > > "davegb" wrote:
> > > > >
> > > > > > With a lot of help here, I've written some code to compare 2
> > > > > > spreadsheets. But when I couple the code to run a userform to get the
> > > > > > filenames to compare with the code to align the data in corresponding
> > > > > > rows, the second macro doesn't recognize the workbook names that have
> > > > > > been saved in the first macro.
> > > > > >
> > > > > > Here's the code with the userform:
> > > > > >
> > > > > > Option Explicit
> > > > > > Public wsNew As Worksheet
> > > > > > Public wsOrig As Worksheet
> > > > > > Public wbNew As Workbook
> > > > > > Public wbOrig As Workbook
> > > > > >
> > > > > >
> > > > > > Private Sub CancelButton_Click()
> > > > > > Unload UserForm2
> > > > > > End Sub
> > > > > >
> > > > > > Private Sub OkButton_Click()
> > > > > > 'Workbooks(ComboBox1.Text).Select
> > > > > > Set wbNew = Workbooks(ComboBox1.Text)
> > > > > > Set wbOrig = Workbooks(ComboBox2.Text)
> > > > > > 'wbNew.Activate
> > > > > > UserForm2.Hide
> > > > > > Call EvenOutRows
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > Private Sub UserForm_Initialize()
> > > > > > Dim wbk As Workbook
> > > > > > For Each wbk In Workbooks
> > > > > > ComboBox1.AddItem wbk.Name
> > > > > > ComboBox2.AddItem wbk.Name
> > > > > > Next wbk
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > > And the other code:
> > > > > >
> > > > > > Sub EvenOutRows()
> > > > > >
> > > > > > Dim wsNew As Worksheet
> > > > > > Dim wsOrig As Worksheet
> > > > > > Dim lCurRow As Long
> > > > > > Dim lrowNew As Long
> > > > > > Dim lrowOrig As Long
> > > > > > UserForm2.Show
> > > > > >
> > > > > > Set wsOrig = wbOrig.Worksheets("Sheet1") <-------OBJECT VARIABLE OR
> > > > > > WITHBLOCK VARIABLE NOT SET
> > > > > >
> > > > > > Set wsNew = wbNew.Worksheets("sheet1")
> > > > > >
> > > > > > lCurRow = 2
> > > > > > Do While Not IsEmpty(wsNew.Cells(lCurRow, 1)) And _
> > > > > > Not IsEmpty(wsOrig.Cells(lCurRow, 1))
> > > > > > If wsNew.Cells(lCurRow, 1) > wsOrig.Cells(lCurRow, 1) Then
> > > > > > wsNew.Rows(lCurRow).Insert: lCurRow = lCurRow + 1
> > > > > > ElseIf wsOrig.Cells(lCurRow, 1) > wsNew.Cells(lCurRow, 1) Then
> > > > > > wsOrig.Rows(lCurRow).Insert: lCurRow = lCurRow + 1
> > > > > > ElseIf wsNew.Cells(lCurRow, 1) = wsOrig.Cells(lCurRow, 1) Then
> > > > > > lCurRow = lCurRow + 1
> > > > > > End If
> > > > > > Loop
> > > > > > Application.Run "Compare.xla!Compare"
> > > > > > End Sub
> > > > > >
> > > > > > The variables have been declared publicly in both modules. Any
> > > > > > suggestions?
> > > > > >
> > > > > >
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

--

Dave Peterson