Click here to Skip to main content
14,920,559 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a excel workbook with 2 text boxes where I basically put the file to copy data from(dynamically get the range to copy) and another to copy data to.
Below is my code:

What I have tried:

VB
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(on the line Range("A1").Select ):
Run-time error '1004'
Application-defined or object-defined error


If I comment the line then I get a different error :
Run-time error '91'
Object variable or With block variable not set


Why is it happening and how do I fix this?

form.xlsm
f_database.xlsx
data_output.xlsx
Posted
Updated 12-Dec-19 5:06am
v3
Comments
Richard MacCutchan 9-Dec-19 11:58am
   
Are you missing an Activate call to select the active worksheet?
Member 12692000 9-Dec-19 13:50pm
   
Even if I add activeworkbook.worksheets("database").activate , errors still show up. I'm confused...
Dave Kreskowiak 9-Dec-19 12:06pm
   
The error message means that a variable you are trying to use is null, or Nothing in VB.

Your code is assuming that something worked and returned what was expected into a variable, but didn't. Check the content of your object variables before you try to use them.
Member 12692000 9-Dec-19 13:55pm
   
I cannot figure out what and why is null or nothing in my code. Everything seems okay but it's obviously not...

I'd avoid of using Activate and Select methods, due to several reason. See:
excel-vba - Avoid using SELECT or ACTIVATE | excel-vba Tutorial[^]
How to Avoid the Select Method in VBA & Why - Excel Campus[^]
Power Excel vba secret, avoid using select[^]

The main idea to avoid using above methods is to work in context. What this means? For example, if you want to insert data into the cell in specific sheet, you need to "say" to MS Excel application to do that:

VB
'define variables
Dim sFileName1 As String, sFileName2 As String 
Dim wbk1 As Workbook, wbk2 As Workbook
Dim wsh1 As Worksheet, wsh2 As Worksheet

'get the name of workbooks
sFileName1 = TextBox1.Text
sFileName2 = TextBox2.Text
'open workbooks
Set wbk1 = Application.Workbooks.Open(sFileName1)
Set wbk2 = Application.Workbooks.Open(sFileName2)
'define sheets you want to works with
Set wsh1 = wbk1.Worksheets("Sheet1")
Set wsh2 = wbk2.Worksheets("Sheet1")
'copy data!
wsh1.Range("A1") = wsh2.Range("A1") 
'further code
'...
'finally - clean up
Set wbk1 = Nothing
Set wbk2 = Nothing
Set wsh1 = Nothing
Set wsh2 = Nothing


Note: above code does not contain an error handler. It's recommended to use it!

BTW: There's few other ways to copy data. Please, read this: Copy Data Between Excel Sheets using VBA[^]
   
Comments
CHill60 10-Dec-19 3:54am
   
Beat me to it! 5'd
Maciej Los 10-Dec-19 3:57am
   
Thank you, Caroline.
Member 12692000 10-Dec-19 7:53am
   
Hi Maciej, thanks for your reply. Can you show me how to avoid of using Activate and Select methods in my above code where I need to find the appropriate range to copy data from? The range that I'm trying to copy are not fixed in different database files, so I cannot hard code the range that I'm trying to copy, it has to be dynamic(hence, I used cells.find method).

Thanks in advance
Maciej Los 10-Dec-19 11:17am
   
I've already showed you how to avoid of using Activate and Select method. All you need to do is to change to code to your needs. Nothing complicated...
Member 12692000 11-Dec-19 0:16am
   
What I meant was how can I use the cells.find method to copy the data without using select and/or activate...
Have you checked my code in the question.. I can't figure out another way of copying the data without using the find method
Member 12692000 11-Dec-19 8:59am
   
I've also posted a solution following your approach however still using cells.find method. Can you breeze through my code and suggest any changes in that code to make it more faster as I'll have to do copy using the cells.find technique more than just twice...
Thanks
From the comments … this is a section of your current code ...
VB
'copy data!
Dim rgFound As Range
Set rgFound = wsh1.Cells.Find(What:="ID", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                                                               :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                              False, SearchFormat:=False)
Dim x As Range, y As Range, z As Range
Set x = rgFound.Offset(1, 0)
Set y = x.End(xlDown)
Set z = Range(x.Address, y.Address)

wsh1.Range(z.Address).Copy
wsh2.Range("I17").PasteSpecial xlPasteValues
Try replacing it with
VB
'Find the start of the data - title is "ID"
Dim rgFound As Range
Set rgFound = wsh1.Cells.Find(What:="ID", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                                                               :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                              False, SearchFormat:=False)
Dim x As Range, y As Range, rngSource As Range, rngTarget As Range
Set x = rgFound.Offset(1, 0)
'Find end of the data
Set y = x.End(xlDown)
'rngSource now contains the source range of data
Set rngSource = wsh1.Range(x.Address, y.Address)
'Work out the target range
Set rngTarget = wsh2.Range("$I$17:$I$" & CStr(17 + rngSource.Rows.Count))
'Set the target values
rngTarget.Value = rngSource.Value
   
v2
Comments
Maciej Los 12-Dec-19 12:53pm
   
5ed!
CHill60 13-Dec-19 4:25am
   
Thank you - you did all the initial leg work though!
If you do the following, you will see it begins to work:
VB
'replace Windows("f_database.xlsx").Activate with the following line:
 Sheet1.Activate
' as a test replace Range("A1").Select with the following:
Range("A1..B5").Select
' you will see the code run and the cells will be selected.
' Hopefully from there you'll see that the Activate of the Windows("f_database.xslx") is failing and work backward from there.


After OP commented, I found that OP needs to add a new Module in the VBA Project and then add code there.

See this image to see how to add a new module in VBA (Excel) : https://i.stack.imgur.com/OTELN.png[^]

Once the code is added to the module it will run without errors.

Here's a sample you can add to the module to insure it works:
VB
Sub Test()
    Windows("f_database.xlsx").Activate

    Range("A1..A5").Select
End Sub
   
v2
Comments
Member 12692000 9-Dec-19 14:10pm
   
If I do what you say, then the form.xlsm's sheet1 is activated not f_database.xlsx's. Also what is the reason for Windows("f_database.xlsx").Activate failing?
raddevus 9-Dec-19 14:20pm
   
This is interesting, because I tried it with a local extra spreadsheet file and it failed too. It is a security thing. You need to add a Module and then add your code to that module and you'll be able to run it.
Here's how you add a new module ---> https://i.stack.imgur.com/OTELN.png
I've actually solved this another time and you can see my answer (which displays that image) at StackOverflow: https://stackoverflow.com/questions/33741488/cannot-run-the-macro/42773999#42773999
My answer is NOT the official answer but has been upvoted more than the official. :)
This will get you to the solution. In the end, the problem is really because of newer security features of Excel -- it doesn't want other files to be opened unless the code is in the protected module area. Please let me know if that works.
Member 12692000 10-Dec-19 7:48am
   
Hi raddevus, your separate module solution still produces the same error..
Maciej Los 10-Dec-19 3:59am
   
Well...
I'd avoid of using Activate and Select methods. Please, see my answer.
Well I've modified my code using Maciej Los's suggested method and so far it seems to work but I'm not sure whether it is any better than using
Quote:
Activate and Select methods
....

VB
Private Sub CommandButton1_Click()
'define variables
    Dim sFileName1 As String, sFileName2 As String
    Dim wbk1 As Workbook, wbk2 As Workbook
    Dim wsh1 As Worksheet, wsh2 As Worksheet

    'get the name of workbooks
    sFileName1 = TextBox1.Text
    sFileName2 = TextBox2.Text
    'open workbooks
    Set wbk1 = Application.Workbooks.Open(sFileName1)
    Set wbk2 = Application.Workbooks.Open(sFileName2)
    'define sheets you want to works with
    Set wsh1 = wbk1.Worksheets("database")
    Set wsh2 = wbk2.Worksheets("Sheet1")

    'copy data!
    Dim rgFound As Range
    Set rgFound = wsh1.Cells.Find(What:="ID", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                                                                   :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                                  False, SearchFormat:=False)
    Dim x As Range, y As Range, z As Range
    Set x = rgFound.Offset(1, 0)
    Set y = x.End(xlDown)
    Set z = Range(x.Address, y.Address)

    wsh1.Range(z.Address).Copy
    wsh2.Range("I17").PasteSpecial xlPasteValues



    Set rgFound = wsh1.Cells.Find(What:="Address", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
                                                                                        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
                                  False, SearchFormat:=False)

    Set x = rgFound.Offset(1, 0)
    Set y = x.End(xlDown)
    Set z = Range(x.Address, y.Address)

    wsh1.Range(z.Address).Copy
    wsh2.Range("A17").PasteSpecial xlPasteValues


    'finally - clean up
    Set wbk1 = Nothing
    Set wbk2 = Nothing
    Set wsh1 = Nothing
    Set wsh2 = Nothing
End Sub


Can this be made more efficient ?
   
Comments
CHill60 11-Dec-19 11:08am
   
Yes - stop using Copy and Paste! See the solution by @Maciej-Los and use
wsh2.Range("A17") = wsh2.Range(z.Address)
Member 12692000 12-Dec-19 8:35am
   
It did not work in my code, that's why I used the Copy and Paste method instead of `wsh2.Range("A17") = wsh1.Range(z.Address)` and so on...
CHill60 12-Dec-19 9:18am
   
Try
wsh2.Range("A17").Value = wsh2.Range(z.Address).Value

The only other issue could be that your source and target are different "shapes".
The problem with Copy and Paste is that the pasted data could end up anywhere - try starting copying and pasting a large range of cells one-by-one and then while it is running click into a text document …. all of your "excel" data will end up in the document instead.
CHill60 12-Dec-19 9:19am
   
I should also point out that I recently replaced some(one else's) copy & paste code with targetrange.value = sourcerange.value and reduced the time it took to run from approximately 30 minutes to around 10 seconds!
Member 12692000 12-Dec-19 9:51am
   
Well if I use `wsh2.Range("A17").Value = wsh1.Range(z.Address).Value`, then only pastes one value and not the entire range in different cells. So, what is the solution for that?
CHill60 12-Dec-19 10:34am
   
Define the entire range instead of A17 and z.Address e.g.
wsh2.Range("$A$2:$BD$3001").Value = …
Both ranges must be the same size and shape
Member 12692000 12-Dec-19 10:52am
   
I cannot hard code the paste value range, it has to be dynamic, so I did try it like ` wsh2.Range("B17:B" & z.Count&"""").Value = wsh1.Range(z.Address).Value ` but its showing syntax error...what am I doing wrong here?
CHill60 12-Dec-19 10:59am
   
What syntax error?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900