Why is my code showing error??

I have a excel workbook with 2 text boxes where I basically put the file to copy data from and another to copy data to.
Below is my code:

Code:

---

Private Sub copydata_Click()    Workbooks.Open Filename:=TextBox1.Text
    Workbooks.Open Filename:=TextBox2.Text
   
    Windows("f_database.xlsx").Activate


    'Range("A1").Select
    Cells.Find(What:="ID", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("data_output.xlsx").Activate
    Range("I17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                  :=False, Transpose:=False






    Windows("f_database.xlsx").Activate
    Range("A1").Select
    Cells.Find(What:="Address", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 0).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Windows("data_output.xlsx").Activate
    Range("A17").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                  :=False, Transpose:=False






    MsgBox "Done!"
End Sub

---

When I run the code using a button, I'm getting the error

Quote:

---

Run-time error '1004'
Application-defined or object-defined error

---

in the line that I commented in my above code.

But now after commenting the previous error line I'm getting

Quote:

---

Run-time error '91'
Object variable or With block variable not set

---

Can anybody help...!!

Two things to make this easier for you ...

What line of code in your macros creates the error message ? It will be highlighted in yellow when the error message appears.

Please post a copy of your workbook for review.

Hi Logit,

There you go.

.
The following could most likely be condensed some more / cleaned up some more .... but this works :

Code:

---



Private Sub CommandButton3_Click()
Dim WB As Workbook
Dim FileName As String
Dim FileName2 As String




'Getting file path and file name from the textbox
FileName = Sheet1.TextBox1.Value
FileName2 = Sheet1.TextBox2.Value


'Open the Excel workbook
Set WB = Workbooks.Open(FileName)


'Open the Excel workbook
Set WB = Workbooks.Open(FileName2)


Application.ScreenUpdating = False


    'Workbooks.Open FileName:=TextBox1.Text
    'Workbooks.Open FileName:=TextBox2.Text
   
    Windows("fdatabase.xlsx").Activate


    Sheets("Database").Range("C2").Select
   
    'Cells.Find(What:="ID", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    'ActiveCell.Offset(1, 0).Select
   
    Sheets("Database").Range(ActiveCell, ActiveCell.End(xlDown)).Copy
   
    'Selection.Copy
   
    Windows("dataoutput.xlsx").Activate
   
    'Sheets("Sheet1").Range("I17").Select
   
    'Range("I17").Select
   
    Sheets("Sheet1").Range("I17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                  :=False, Transpose:=False






    Windows("fdatabase.xlsx").Activate
    Sheets("Database").Range("A2").Select
   
    'Cells.Find(What:="Address", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    '                                                                :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    '          False, SearchFormat:=False).Activate
    'ActiveCell.Offset(1, 0).Select
    'Range(Selection, Selection.End(xlDown)).Select
   
    Sheets("Database").Range(ActiveCell, ActiveCell.End(xlDown)).Copy
   
    'Selection.Copy
   
    Windows("dataoutput.xlsx").Activate
   
    'Sheets("Sheet1").Range("A17").Select
   
    'Range("A17").Select
   
    Sheets("Sheet1").Range("A17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                                                                  :=False, Transpose:=False


Application.ScreenUpdating = True


    MsgBox "Done!"
   
End Sub

---

The code shows previous lines that were commented out to help you understand where the changes were made and hopefully to better follow the code logic.

.
If you delete all the extraneous lines of code you have this :

Code:

---

Private Sub CommandButton3_Click()
Dim WB As Workbook
Dim FileName As String
Dim FileName2 As String

FileName = Sheet1.TextBox1.Value
FileName2 = Sheet1.TextBox2.Value

'Open the Excel workbook
Set WB = Workbooks.Open(FileName)

'Open the Excel workbook
Set WB = Workbooks.Open(FileName2)

Application.ScreenUpdating = False

    Windows("fdatabase.xlsx").Activate

    Sheets("Database").Range("C2").Select
     
    Sheets("Database").Range(ActiveCell, ActiveCell.End(xlDown)).Copy
     
    Windows("dataoutput.xlsx").Activate
   
    Sheets("Sheet1").Range("I17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   
    Windows("fdatabase.xlsx").Activate
    Sheets("Database").Range("A2").Select
   
    Sheets("Database").Range(ActiveCell, ActiveCell.End(xlDown)).Copy
   
    Windows("dataoutput.xlsx").Activate
   
    Sheets("Sheet1").Range("A17").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                                                                 
Application.ScreenUpdating = True

    MsgBox "Done!"
   
End Sub

---

Hi Logit,

I need to find the proper data columns (i.e. ID, Address etc) and the range to copy dynamically from f_database file as the position of the columns/cells may change in a different f_database file and that's why I used the Cells.Find method in my code.