Object variable not set (Error 91) [SOLVED]

[CalvinH]

Hi,

I have some simple code below, please help me. It states I could be missing a SET statement....still learning. I am sure it would take you guys 5min to fix. Error comes up in the (Testcase.Close True) line....

[pre]

Sub Cop_Rows()

Dim Testcase As Workbook
Dim b As Long

Application.ScreenUpdating = False

b = 7

Do While Cells(x, 5) <> ""

If Cells(b, 5) > "0" Then
Worksheets("Sheet1").Rows(x).Copy
Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlValues
Application.CutCopyMode = False
End If

x = x + 1

Testcase.Close True 'Error comes up here.....

Loop

Application.ScreenUpdating = True

End Sub

[/pre]

 

[Smallman]

Hi Calvin


You have delcared Testcase as a workbook but you have not Set testcase to equal a specific workbook.


Typically it would be something like this.

set testcase = thisworkbook

Hope that helps.


Take care


Smallman

 

[Hui]

Calvin


You should also initialise the variable x just after b=7 as at that point x=0


I am wondering why you would want to close the testcase workbook the first time cells(x, 5) <>""


Should the testcase.close line be outside the loop?

 

[jeffreyweir]

I might as well throw my 2 cents in as well.


If you only want to copy values, then don't use the copy/Paste method at all. Instead, simply set the values in your destination range equal to the values in the source range.


Range("Destination").value = Range("Source").value

Worksheets("Sheet2").Rows(2).Value = Worksheets("Sheet1").Rows(1).Value

 

[CalvinH]

Thanks guys for the reply.....Hui, x and b was my typo's and yes corrected position of .close outside loop, I have another issue with another file of mine. I might not be using SET correctly as well in this scenario....Can it be rewritten better....getting errors(Runtime Error 1004 on and Error 91) and not copying all the rows with value > than "3", Jeffrey the reason I am using copy and paste is because my file has lots of hidden columns with values that I require in destination file.


Sub myCopy()


Dim r As Long

Dim Testcase As Workbook

Dim owb As Workbook


Application.ScreenUpdating = False


r = 3


Do While Cells(r, 3) <> ""

If Cells(r, 3) > "0" Then

Worksheets("Sheet1").Rows(r).Copy

Set owb = Workbooks.Open("C:tempBook1.xlsx")

Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlValues ' Runtime error 1004

Application.CutCopyMode = False


End If


r = r + 1


Loop


owb.Close True 'Error 91


Application.ScreenUpdating = True


End Sub

 

[Smallman]

Calvin


Can you upload the file? I want to see the structure of the file. Looping through a range of cells is so inefficient. There is a better way.


Take care


Smallman

 

[CalvinH]

Hi smallman is there site I could use...not sure if out security policy allows access but I can try.tx

 

[Smallman]

Hi Calvin


Yeah that is common problem. Work usually bans file sharing sites. I use RapidShare but there are heaps - dropbox, filesnack etc.


If you have no luck I can post it in this forum for you. Just email it to me and will upload it.


Marcusinlondon1 AT Yahoo dot com


Take care


Smallman

 

[SirJB7]

Hi, CalvinH!

Give a look at the green sticky posts at this forums main page for uploading guidelines.

http://chandoo.org/forums/topic/posting-a-sample-workbook

Regards!

 

[CalvinH]

Thanks Guys,


Not sure if I was able to do everything I should on dropboc, but please check if you can see the file. The other file it copies to is just a blank file with a header row.


https://www.dropbox.com/s/d809u1lac3jekdr/Testdata.xlsm

 

[SirJB7]

Hi, CalvinH!

Done all fine at DropBox, I'm giving a look to your Testada.xlsm file with 1st worksheet Time_data. Coming back soon, I hope.

Regards!

 

[SirJB7]

Hi, CalvinH!


Try with this code:

-----

[pre]

Option Explicit

Sub myCopy()
'
' constants
Const ksTargetWB = "C:TimeSheetTableUpdate.xlsx"
Const kiWitnessColumn = 75 '77 is column BY which is empty, that's why it did nothing
Const klStartingRow = 20 '1st data row, 18 is title row and empy at column BW (75)
'
' declarations
Dim x As Long, y As Long
'
' start
Application.ScreenUpdating = False
x = klStartingRow
Workbooks.Open ksTargetWB
With ActiveSheet
y = .Cells(.Rows.Count, 1).End(xlUp).Row
End With
'
' process
With ThisWorkbook.Worksheets("Time_data")
Do While .Cells(x, kiWitnessColumn).Value <> ""
If .Cells(x, kiWitnessColumn).Value > 0 Then
y = y + 1
.Rows(x).Copy
ActiveSheet.Rows(y).PasteSpecial xlValues
End If
x = x + 1
Loop
End With
'
' end
ActiveWorkbook.Close True
With Application
.CutCopyMode = False
.ScreenUpdating = True
End With
'
End Sub

[/pre]
-----


I've arranged it a little, creating constants for hard coded constants (I also changed 75 by 75 and 18 by 19 values, please check them), eliminating unnecessary object definitions, moving once actions out from the loop, and so. Just advise if any issue.


Regards!

 

[CalvinH]

Thank you Sir, will check and confirm.

 

[CalvinH]

Nothing short of brilliant SIRJB...I am slowly getting the hang of VBA....thank you so very much..another one..SOLVED...

 

[SirJB7]

Hi, CalvinH!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!