"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!

 

[mwillies]

Haha well, now it's highlighting that same line
Set rVisc...
but it's giving me a different error. This one says:
Run Time Error 424: Object Required

 

[Norie]

Can you post the exact code you have now?

 

[RoryA]

rVisc and rDest are range object variables and do not need qualifying with a workbook, but it looks as though your code is trying to assign the same range to both? The basic syntax would be:

Set rDest = Application.InputBox(Prompt:="Please select the Cell to paste to", Title:="Paste to", Type:=8)
Set rVisc = rDest.Offset(0, 1)
Set rTemp = rDest.Offset(0, 1)

but you probably need to adjust the offset values of at least one of them.

 

[mwillies]

My code right now is this:

Sub ReportData()


Dim wbTarget As Workbook
Dim wbThis As Workbook
Dim rTime As Range
Dim rVisc As Range
Dim 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 rTime = Application.InputBox(Prompt:="Please select the Cell to paste to", Title:="Paste to", Type:=8)


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


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


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


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


'Close Data File
wbTarget.Close False


End Sub

I'm currently getting an "Object Required" error on the line Set rVisc = rDest.Offset(0,1)

 

[Norie]

rDest isn't being set to anything in that code.

Should it actually be rTime?

Set rVisc = rTime.Offset(0, 1)
Set rTemp = rTime.Offset(0, 2)

 

[RoryA]

Change rDest to rTime I suspect.

 

[mwillies]

Ah! That fixed it! Now all of my data is exactly where I want it.

The only other problem that I'm having, I referenced in the original post. I am not sure how to pull the data from the first (and only) sheet in the wbTarget workbook, since the name changes for each file. I have it set as "Gustafson Test 1" right now, because that was the name in the file that I have been using to test this code on, but it will be different for each file that I'm trying to extract data from...does that make sense?

 

[Norie]

This will copy from the first and only sheet in the workbook.

wbTarget.Sheets(1).Range("A85:A2500").Copy

 

[mwillies]

Wow...glad I couldn't figure that one out for myself.

Thank you all so much for helping me out. I really, really appreciate it!