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

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

Work with 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.

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.

Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦