Option ExplicitPrivate Sub CommandButton1_Click() Dim FullFileName As String FullFileName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _ 1, "Custom Dialog Title", , False) Workbooks.Open FullFileName Dim wbOpen As Workbook Set wbOpen = Workbooks.Open(FullFileName) Dim xSheet As Worksheet xSheet = wbOpen.Worksheet 'wrong here Dim m As Integer Dim n As Integer m = xlCellTypeLastRow n = xlCellTypeLastCol With wbOpen Dim X As Long Dim Y As Long X = xlCellTypeLastRow Y = xlCellTypeLastCol Dim i As Integer Dim j As Integer Dim a As Integer Dim b As Integer For i = 1 To X Sheet2.Cells(i, 1) = xSheet(1).Cells(i, 1) Sheet2.Cells(i, 2) = xSheet(1).Cells(i, 2) Sheet2.Cells(i, 3) = xSheet(1).Cells(i, 3) For a = 1 To m If (Sheet1.Cells(a, 3) = xSheet.Cells(i, 1) And Sheet1.Cells(a, 4) = xSheet.Cells(i, 2) And Sheet1.Cells(a, 5) = xSheet.Cells(i, 3)) Then b = a Exit For End If Next a Sheet2.Cells(i, 4) = Sheet1.Cells(a, 1) Sheet2.Cells(i, 5) = Sheet1.Cells(a, 2) Next i End WithEnd SubFunction xlCellTypeLastRow() Dim LastRow As Long With ActiveSheet LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row End With xlCellTypeLastRow = LastRowEnd FunctionFunction xlCellTypeLastCol() Dim LastCol As Long With ActiveSheet LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With xlCellTypeLastCol = LastColEnd Function

Alternatively, you could have had the:
dim mySheet1 as worksheet, mySheet2 as worksheet
With wbOpen
set mySheet1 = .sheets(1)
set mysheet2 = .sheets(2)
end with
and then used mySheet1

Take a couple minutes and articulate what you're trying to do. The current state of your original question is "what is wrong with my code" so we focused on your syntax error. Now, you want more help, and I'm glad to do that if you would highlight a few steps that indicate what you're trying to do with the code at a level which I can quickly respond.

I just steped into the code,I could not go further. I have several sheets. I want to extract data to one sheet and write to the other sheet by some condition.

Option ExplicitPrivate Sub CommandButton1_Click() Dim FullFileName As String FullFileName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", _ 1, "Custom Dialog Title", , False) Workbooks.Open FullFileName Dim wbOpen As Workbook Set wbOpen = Workbooks.Open(FullFileName) Dim xSheet1 As Worksheet Dim xSheet2 As Worksheet xSheet1 = wbOpen.Worksheets.Add Dim m As Integer Dim n As Integer m = xlCellTypeLastRow n = xlCellTypeLastCol ' With wbOpen Dim X As Long Dim Y As Long X = xlCellTypeLastRow Y = xlCellTypeLastCol Dim i As Integer Dim j As Integer Dim a As Integer Dim b As Integer For i = 1 To X xSheet1(2).Cells(i, 1) = xSheet1(1).Cells(i, 1) xSheet1(2).Cells(i, 2) = xSheet1(1).Cells(i, 2) xSheet1(2).Cells(i, 3) = xSheet1(1).Cells(i, 3) For a = 1 To m If (Sheet1.Cells(a, 3) = xSheet1(1).Cells(i, 1) And Sheet1.Cells(a, 4) = xSheet1(1).Cells(i, 2) And Sheet1.Cells(a, 5) = xSheet1(1).Cells(i, 3)) Then b = a Exit For End If Next a xSheet(1).Cells(i, 4) = Sheet1.Cells(a, 1) xSheet(1).Cells(i, 5) = Sheet1.Cells(a, 2) Next i ' End WithEnd SubFunction xlCellTypeLastRow() Dim LastRow As Long With ActiveSheet LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row End With xlCellTypeLastRow = LastRowEnd FunctionFunction xlCellTypeLastCol() Dim LastCol As Long With ActiveSheet LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With xlCellTypeLastCol = LastColEnd Function

The Excel file has 3 sheets, let's say their names are Sheet1, Sheet2, and Sheet3 - and all three exist. Is there data in all three sheets, or just in Sheet1. Do you really need to add a sheet (e.g., create Sheet4), or just use the blank Sheet2 (is Sheet2 blank)?

Ok - I've not tried to follow the logic as much as try to identify the things you want to identify.

To get a handle to a sheet, there are several ways:

dim mySheet as worksheet
set mysheet = worksheets.add (this creates a new sheet and gives the handle to that sheet as mySheet)

another way...

activeworkbook.sheets(1) <- this is a reference to the Sheet1 sheet using the index of 1
activeworkbook.sheets(2) <- same thing with index of 2, for Sheet2

finally another way...

activeworkbook.sheets("sheetname") <- referencing the sheet using a sheet name

you could assign mySheet to any of these using the

set mySheet = activeworkbook.sheets(1)

or in your example...

set mySheet = myOpen.Sheets(1)

I've attempted to replace your code and you can correct as you understand your logic. Sheet(1) is the first sheet (default "Sheet1"), and Sheet(2) is the second (default "Sheet2")

See code below:

Option ExplicitPrivate Sub CommandButton1_Click() Dim FullFileName As String Dim wbOpen As Workbook Dim xSheet1 As Worksheet Dim xSheet2 As Worksheet Dim i As Integer Dim j As Integer Dim a As Integer Dim b As Integer Dim X As Long Dim Y As Long Dim m As Integer Dim n As Integer 'Open a worksheet file based on user input FullFileName = Application.GetOpenFilename("Excel files (*.xl*),*.xl*", 1, "Custom Dialog Title", , False) Workbooks.Open FullFileName Set wbOpen = Workbooks.Open(FullFileName) 'workbook has 3 sheets m = xlCellTypeLastRow n = xlCellTypeLastCol X = xlCellTypeLastRow Y = xlCellTypeLastCol 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 (wbOpen.Sheets(1).Cells(a, 3) = wbOpen.Sheets(2).Cells(i, 1) And wbOpen.Sheets(1).Cells(a, 4) = wbOpen.Sheets(2).Cells(i, 2) And wbOpen.Sheets(1).Cells(a, 5) = wbOpen.Sheets(2).Cells(i, 3)) Then b = a Exit For End If Next a wbOpen.Sheets(1).Cells(i, 4) = wbOpen.Sheets(2).Cells(a, 1) wbOpen.Sheets(1).Cells(i, 5) = wbOpen.Sheets(2).Cells(a, 2) Next i ' End WithEnd SubFunction xlCellTypeLastRow() Dim LastRow As Long With ActiveSheet LastRow = .Range("A1").SpecialCells(xlCellTypeLastCell).Row End With xlCellTypeLastRow = LastRowEnd FunctionFunction xlCellTypeLastCol() Dim LastCol As Long With ActiveSheet LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column End With xlCellTypeLastCol = LastColEnd Function

The code is running perfectly. Let me just check the logic. Thank you very much.

0

Featured Post

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Introduction
This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results.
Paraphrasing Richard Shockley, author of my favourite finance reference texâ€¦

Workbook link problems after copying tabs to a new workbook?
David Miller (dlmille)
Intro
Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original souâ€¦

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â€¦