Macro not running from another workbook

GAIJINIJI

New Member
Joined
Nov 20, 2009
Messages
8
I am working on a piece of VBA code (Visual Basic 6.5) that will simply take a date (different every time) from a specified cell, convert it to a day and paste the result into the tab. This is repeated across six tabs.

I have finished the coding and it works perfectly on the original workbook but when I open another workbook and try to run the macro from the first I get an error that states

Run-time error '1004':
Method 'select' of object '_worksheet' failed

When I try and debug it the first line of coding is highlighted which simply says

Sheet1.Select

At this point I am tearing my hair out :( and can't figure out what is causing this. Could anyone point me in the right direction?

Its worth noting that I know that my code is far from being the most efficient VBA coding that I could have used... but its my first one so take it easy :biggrin:

Please see coding below

Sub Macro1()
'
' borc Macro
' multisheetdatetoday
'
'
Sheet1.Select
Range("A3").Select
ActiveCell.FormulaR1C1 = "=TEXT(R[-1]C,""dddd"")"
Range("A3").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set target = Range("A1")
Sheet1.Name = target
Sheet2.Select
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TEXT(R[-1]C,""dddd"")"
Range("A3").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set target = Range("A1")
Sheet2.Name = target
Sheet3.Select
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TEXT(R[-1]C,""dddd"")"
Range("A3").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set target = Range("A1")
Sheet3.Name = target
Sheet4.Select
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TEXT(R[-1]C,""dddd"")"
Range("A3").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set target = Range("A1")
Sheet4.Name = target
Sheet5.Select
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TEXT(R[-1]C,""dddd"")"
Range("A3").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set target = Range("A1")
Sheet5.Name = target
Sheet6.Select
Range("A3").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=TEXT(R[-1]C,""dddd"")"
Range("A3").Select
Selection.Copy
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Set target = Range("A1")
Sheet6.Name = target
MsgBox "Completely unnecessary message boxes are made of win!"
End Sub


Any help on this would be very much appreciated.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Using code like Sheet1.Select refers to the underlying sheet name to that you see in the VBA project explorer. You can also refer using the sheet's tab name using Sheets("Sheet1").Select or it index using Sheets(1).Select.

One important thing to get your head round when you start to write VBA is that it is almost always not necessary to use Select.

For example this:

Code:
Sheets("Sheet1").Select
Range("A3").Select
ActiveCell.FormulaR1C1 = "=TEXT(R[-1]C,""dddd"")"

could be re-written as:

Code:
Sheets("Sheet1").Range("A3").FormulaR1C1 = "=TEXT(R[-1]C,""dddd"")"

Dom
 

GAIJINIJI

New Member
Joined
Nov 20, 2009
Messages
8
Thanks Dom.

The workbooks that I want to use this on will always have different names on the tabs so I figured using the underlying name was the way to go.

I'm a firm believer that "Google is your friend" and is usually there to help solve any problems but on this occasion it fails me.

I suspect its having problems selecting either the right sheet or the workbook I want it to run on?
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
If they have different names what is the logic as to which sheet to perform the action on? If it's the order of the sheets in the workbook you could use Sheets(1).Select etc

Dom
 

GAIJINIJI

New Member
Joined
Nov 20, 2009
Messages
8

ADVERTISEMENT

I see what you are saying but it shouldn't be an issue. Since the workbook(s) I want to run this macro on are automatically generated they will always be numbered sheet1, sheet2 etc (but with different tab names).

The thing I don't understand is why this macro works in the workbook it resides in but not on another workbook (unless I copy and paste the whole thing into a module of the new one).

Does the method of selecting sheets effect this (ie sheet name, underlying sheet name, or sheets from left to right)?

Many thanks
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
I've never really used the Sheet1.Select method but I'm guessing that as you are referring to an object it assumes it is the object within the workbook that contains the code and not the active workbook. Using the other methods I described will always work on the active workbook unless you specify otherwise.

Dom
 

GAIJINIJI

New Member
Joined
Nov 20, 2009
Messages
8

ADVERTISEMENT

Using the Sheet(1).select method I am presented with an error that states compile error: sub or funtion not defined.

Using Sheet1.Range("A3").FormulaR1C1 = "=TEXT(R[-1]C,""dddd"")" works but now I have an error in another part that did work! I will google this and see if I can find a resolution before troubling you with more questions.

My experience of VBA so far has been like this;

YAY!!!
ah ****
woo hooo!
oh ******* ****
and so on :p

Thanks for pointing me in the right direction though Dom
 

Watch MrExcel Video

Forum statistics

Threads
1,122,669
Messages
5,597,470
Members
414,145
Latest member
lonnie451

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