Solved

Several worksheets mess up

Posted on 2011-03-17
4
164 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
ID: 35160545
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
ID: 35160576
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
ID: 35160618
My fault. You are rigjht.
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 35160637
ThisWorkbook refers specifically to the workbook containing the code.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
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.

816 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

11 Experts available now in Live!

Get 1:1 Help Now