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.

Here's one more example you might find useful to adopt to your code, if you're just trying to add a number of worksheets....

Sub AddSheets()
Dim mySheets() As Worksheet
Dim numSheetsAdded As Integer

numSheetsAdded = 3

For i = 0 To numSheetsAdded
ReDim Preserve mySheets(i)
Set mySheets(i) = Worksheets.Add
Next i

End Sub

0

zhshqzycAuthor Commented:

Hi dlmille:

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)?

Dave

0

zhshqzycAuthor Commented:

No, I don't want to add new sheet. Now I just want to use a blank sheet 2. I might misunderstand the add method.

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

change

xSheet = wbOpen.Worksheet(1)