Macro not running from another workbook

[GAIJINIJI]

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

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.

 

[Domski]

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:

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

could be re-written as:

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

Dom

 

[GAIJINIJI]

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]

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]

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]

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]

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

Thanks for pointing me in the right direction though Dom

 

[Domski]

It's Sheets(1).select not Sheet(1).select

Dom

 

[GAIJINIJI]

Perfect!

You, Sir, are a gentleman.

Many thanks