Help with "Object Required" error

[raeannb]

Hi! So I'm working on combining two spreadsheets, one of which has several more columns than the other. After pasting the new (longer) spreadsheet to the bottom of the old, I have my code compare the heading rows and insert columns into old (shorter) heading row where needed. This part works fine, but the part I'm having trouble with (which seems like it should be so easy!!) is getting the newly inserted columns to have the titles they need. For example, the old table has columns for "Cycle 1" through "Cycle 8", while the new table goes up to "Cycle 10." I've gotten it to add two columns to the old table for cycle 9 and 10, but it won't put in the actual titles. Here's my code - a few notes first: row 5 is considered the "old" heading row, and is the one I want to keep. The last cycle column in each heading row is followed by the term "EOS." The new heading row (m) will ultimately be deleted. Okie doke, here's the program:
Dim m As Integer, n As Integer, o As Integer

'Remove AutoFilters
ActiveSheet.AutoFilterMode = False

'Identify first row of new data (new data has presumably just been pasted)
m = ActiveCell.Row

'Find last cycle column of new data
n = Cells(m, 1).EntireRow.Find(What:="EOS", After:=Cells(m, 1),
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column - 1

'Find last cycle column of old data
o = Rows("5").Find(What:="EOS", After:=Range("A5"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column - 1

'Insert columns to match old + new cycle data, fill in new cycle headings
Range(Cells(5, o + 1), Cells(m - 1, n)).Insert Shift:=xlShiftToRight

Set Range(Cells(5, o + 1), Cells(5, n)).Value = Range(Cells(m - 1, o + 1), Cells(m - 1, n)).Value ******* This is where I get my error


I've tried a bajillion variations on the code, and I can't seem to figure it out. Advice would be wonderful. The error I get is:

Run-time error '424': Object Required


Thank you!!!

 

[RoryA]

Remove the word Set from that line.

 

[raeannb]

Oy I wish it was that easy - I tried that. Any other tips?

 

[RoryA]

You can't be getting an object required error on that line if you remove the Set keyword. Where is the error and what is it?

 

[raeannb]

Now I get an error (Run-time error '91': object variable or With block variable not set) earlier in the code:

Dim m As Integer, n As Integer, o As Integer, x As Integer, y As Integer, i As Integer

'Remove AutoFilters
ActiveSheet.AutoFilterMode = False

'Identify first row of new data
m = ActiveCell.Row

'Find last cycle column of new data
n = Cells(m, 1).EntireRow.Find(What:="EOS", After:=Cells(m, 1), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column - 1 ****Error here

'Find last cycle column of old data
o = Rows("5").Find(What:="EOS", After:=Range("A5"), LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Column - 1

'Insert columns to match old + new cycle data, fill in new cycle headings
Range(Cells(5, o + 1), Cells(m - 1, n)).Insert Shift:=xlShiftToRight
Range(Cells(5, o + 1), Cells(5, n)).Value = Range(Cells(m - 1, o + 1), Cells(m - 1, n)).Value

Thank you!!!

 

[RoryA]

That means that EOS is not being found.

 

[raeannb]

Why would it find EOS before, and then have a problem when I remove the word "Set"? Scout's honor, all I did was take it out, as suggested. Thoughts?