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.