Solved

Several worksheets mess up

Posted on 2011-03-17
4
160 Views
Last Modified: 2012-05-11
Hi,
I have a button click event in file "file1". When I click it, a file dialog is open and I select another excel file "file2".
Now my logic is that to compare the sheets between the files "file1" and "file2" than doing something.
Private Sub CommandButton1_Click()
    Dim FullFileName As String
  FullFileName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _
    1, "Custom Dialog Title", , False)
    
    Dim m As Integer
    m = Sheet1.xlCellTypeLastRow
    
    Workbooks.Open FullFileName
    Dim wbOpen As Workbook
    Set wbOpen = Workbooks.Open(FullFileName)
  
    With wbOpen
        Dim X As Long
        X = xlCellTypeLastRow
        Dim i As Integer
        Dim j As Integer
        Dim a As Integer
        Dim b As Integer
        For i = 1 To X
              wbOpen.Sheets(2).Cells(i, 1) = wbOpen.Sheets(1).Cells(i, 1)
              wbOpen.Sheets(2).Cells(i, 2) = wbOpen.Sheets(1).Cells(i, 2)
              wbOpen.Sheets(2).Cells(i, 3) = wbOpen.Sheets(1).Cells(i, 3)
              For a = 1 To m
                    If (Sheet1.Cells(a, 3) = wbOpen.Sheets(1).Cells(i, 1) And Sheet1.Cells(a, 4) = wbOpen.Sheets(1).Cells(i, 2) And Sheet1.Cells(a, 5) = wbOpen.Sheets(1).Cells(i, 3)) Then
                        b = a
                    Exit For
                    End If
              Next a
              wbOpen.Sheets(2).Cells(i, 4) = Sheet1.Cells(b, 1) 'Here Sheet is supposed to be in file1
              wbOpen.Sheets(2).Cells(i, 5) = Sheet1.Cells(b, 2)
        Next i
    End With
    

Open in new window

Please notice Sheet1 in the code is supposed to be in file1 but I found it is in file2. How to modify the code?
Thanks
0
Comment
Question by:zhshqzyc
  • 2
  • 2
4 Comments
 
LVL 24

Accepted Solution

by:
StephenJR earned 250 total points
Comment Utility
Try adding a workbook reference, e.g.

wbOpen.Sheets(2).Cells(i, 4) = ThisWorkbook.Sheet1.Cells(b, 1)

or

wbOpen.Sheets(2).Cells(i, 4) = ThisWorkbook.Sheets(1).Cells(b, 1)
0
 

Author Comment

by:zhshqzyc
Comment Utility
Still wrong. Because I opened two files at the same time. Both of them have the same name ThisWorkbook. I checked the value in the immediate window. It is the value in file2.
0
 

Author Closing Comment

by:zhshqzyc
Comment Utility
My fault. You are rigjht.
0
 
LVL 24

Expert Comment

by:StephenJR
Comment Utility
ThisWorkbook refers specifically to the workbook containing the code.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now