Why is my code showing error??

[bmba007]

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:

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

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

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

Can anybody help...!!

[Logit]

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.

[bmba007]

Hi Logit,

There you go.

[Logit]

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

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.

[Logit]

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

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

[bmba007]

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.