"Object doesn't support this property or method" -Help with VBA

[mwillies]

Good Afternoon!

I'm currently working on piecing together a macro that will allow me to pull data from 3 specific ranges of cells in a user-selected file, and copy-paste it into the open excel file. The issue I'm currently battling is a Run Time Error 438: Object doesn't support this property or method from the third line in the third chunk of code, below:

Sub ReportData()


Dim wbTarget As Workbook
Dim wbThis As Workbook
Dim rTime, rVisc, rTemp As Range


'Choose & Open Data File
Dim FileName As String
FileName = Application.GetOpenFilename()
Set wbTarget = Application.Workbooks.Open(FileName)
Set wbThis = ThisWorkbook


wbThis.Activate
Set rDest = Application.InputBox(Prompt:="Please select the Cell to paste to", Title:="Paste to", Type:=8)
Set rVisc = wbThis.Sheets("5550 Data").rDest.Range(rDest).Offset(0, 1).Value
Set rTemp = wbThis.Sheets("5550 Data").rDest.Range(rDest).Ofset(0, 1).Value


'Copy & Paste Time Data
On Error GoTo 0
wbTarget.Activate
Application.CutCopyMode = False
wbTarget.Sheets("Gustafson Test 1").Range("A85:A2500").Select
wbTarget.Sheets("Gustafson Test 1").Range("A85:A2500").Copy
wbThis.Activate
rDest.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False


'Copy & Paste Visc Data
wbTarget.Activate
wbTarget.Sheets("Gustafson Test 1").Range("H85:H2500").Select
wbTarget.Sheets("Gustafson Test 1").Range("H85:H2500").Copy
wbThis.Activate
rVisc.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False


'Copy & Paste Temp Data
wbTarget.Activate
wbTarget.Sheets("Gustafson Test 1").Range("B85:B2500").Select
wbTarget.Sheets("Gustafson Test 1").Range("B85:B2500").Copy
wbThis.Activate
rTemp.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False


'Close Data File
wbTarget.Close False


End Sub

Previously, it was telling me that I was getting an error with global variables, but I seem to have moved past that, at least for now.

Also, while you're looking at this...I also need to figure out how to get the code to find the sheet name in the wbTarget workbook that I'm pulling from. It is different for each of the files, and I can't figure out how to select and copy the data I want without knowing the sheet name beforehand.

I apologize if this is not very straightforward. I haven't done much with VBA, and am piecing this all together based off of lots of googling!

 

[HalfAce]

Hi and welcome to the board.
Exactly which line of code is highlighted by the error? (I can't decide which line you mean by your description.)

If it's this line:
Set rTemp = wbThis.Sheets("5550 Data").rDest.Range(rDest).Ofset(0, 1).Value
it's likely because the word Offset is misspelled.

If it's this line:
wbTarget.Sheets("Gustafson Test 1").Range("A85:A2500").Select
it's likely because (1) the sheet "Gustafson Test 1" is not the active sheet and (2) we're not allowed to select a range on an inactive sheet. (Meaning you would need to select the sheet in one line and then select the range in that sheet in a separate line.)

Also, you don't need to actually select the sheet or range in order to copy. You can simply refer to them instead. The same goes for using paste.
It looks to me like you're using a newer version of excel than I am, but try replacing these lines:
wbTarget.Sheets("Gustafson Test 1").Range("A85:A2500").Select
wbTarget.Sheets("Gustafson Test 1").Range("A85:A2500").Copy
wbThis.Activate
rDest.PasteSpecial (xlPasteValues)
with just this:
wbTarget.Sheets("Gustafson Test 1").Range("A85:A2500").Copy rDest.PasteSpecial(xlPasteValues)
(Note, the PasteSpecial may not allow this, but if you were just using Paste, it should work all in one line like that.)

Does any of this help, or am I missing what you're trying to get across?

EDIT:
Also, I just noticed the lines like:
Set rVisc = wbThis.Sheets("5550 Data").rDest.Range(rDest).Offset(0, 1).Value
I think you'll have problems with these lines unless the value of the cell to the right of rDest is in fact a cell address.

 

[HalfAce]

One other thing... the way you've declared your variables "rTime, rVisc & rTemp", rTemp is the only one declared as a range object, the first two are (by default) being declared as a Variant data type. Each one needs to be specified as its own data type instead of trying to declare them all in one shot like that. For example...
Dim rTime As Range, rVisc As Range, rTemp As Range

 

[mwillies]

Thanks for picking up on all that! I have made the changes that you have listed, but am still getting the same error in the same place. The error highlights the last line you mentioned, in your edit
Set rVisc = wbThis.Sheets("5550 Data").rDest.Range(rDest).Offset(0, 1).Value

But I'm not sure what I need to do to fix that.

I really appreciate the help, though. Thank you so much!

 

[HalfAce]

What exactly is in the cell being referred to? (The cell to the right of rDest.)

What the code is being told now is to set that cell's value as a range.
It appears as if it's wanted to be used as a range to paste to, so try removing the .Value at the end of that line of code and see where that gets you.

 

[mwillies]

The cell to the right of rDest is where I would like the first value of the column of rVisc data to be pasted. And the cell to the right of that is where I want the rTemp data to start.

I did remove the .Value, but the problem is still happening.

 

[HalfAce]

The only thing I can think of at this point is that the sheet named "5550 Data" is not the active sheet in wbThis.
We either have to have the specified sheet as the active sheet before we can set a specified range, or we can do it within a With Statement.

Just for testing purposes, try changing these lines:
Set rVisc = wbThis.Sheets("5550 Data").rDest.Range(rDest).Offset(0, 1).Value
Set rTemp = wbThis.Sheets("5550 Data").rDest.Range(rDest).Ofset(0, 1).Value
To this instead:
With wbThis.Sheets("5550 Data")
Set rVisc = .rDest.Offset(0, 1)
Set rTemp = .rDest.Offset(0, 2)
End With

Note, I changed the rTemp to offset 2 columns over from rDest, as originally you had rVisc and rTemp both set to be one column over, thereby assigning both variables to the same cell.

Does any of this help?

 

[mwillies]

I made the change you requested. It is now giving me the same run time error as before, on the line:
Set rVisc = .rDest.Offset(0, 1)

 

[HalfAce]

Which workbook and sheet are the active workbook and sheet when this is being run?

 

[HalfAce]

Oops, my bad.
Above, I posted to try using this:
With wbThis.Sheets("5550 Data")
Set rVisc = .rDest.Offset(0, 1)
Set rTemp = .rDest.Offset(0, 2)
End With

Try this instead...
With wbThis.Sheets("5550 Data")
Set rVisc = rDest.Offset(0, 1)
Set rTemp = rDest.Offset(0, 2)
End With

(Removed the periods in front of rDest in both lines.)
Does that help any?