runtime error 1004 range of object global failed excel 2010

kwesmc

Board Regular
Joined
Jan 25, 2008
Messages
72
I got this error on Excel 2010, however the code below works perfectly fine on 2007. I changed all the settings on 2010 on Trust Center, but still no go. I am getting the error on the bolded code below (Range("Week" & WeekNo).Copy. Can someone please tell me why this code works in 2007 but does not in 2010? Thanks. Sub Macro2()
'
' Sub Macro2()

' Keyboard Shortcut: Ctrl+z
'





Dim wbkOtherWorkbook As Workbook
' Set wbkOtherWorkbook = Workbooks("District Foodcost.xls") 'Assuming this workbook is already open


Dim wbMyBook As Workbook
Set wbMyBook = ActiveWorkbook
Set objOutlook = GetObject(, "Outlook.Application")

Dim MySelection As Range
Set MySelection = Application.InputBox(prompt:="Select a Cell ie... G3", Type:=8)
MySelection.Select
' ActiveWorkbook.Sheets("Weekly Food Cost").Activate

Workbooks(1).Activate

Dim WeekNo As Variant

Do
WeekNo = Application.InputBox _
("Enter week number", , , , , , , 1)
If WeekNo = "False" Then
Exit Do
End If
Loop Until WeekNo >= 1 And WeekNo <= 4

Workbooks(2).Activate
If WeekNo <> "False" Then
Rang e("Week" & WeekNo).Copy

Workbooks(1).Activate
' Windows("District Foodcost.XLS").Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Dim Wb As Workbook
For Each Wb In Workbooks
If Wb.Name <> ThisWorkbook.Name Then
Wb.Close savechanges:=False
End If
Next Wb
Range("E3").Select
' objOutlook.Application.Quit

End Sub
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top