VBA Code for selecting worksheet
 

Hi
I've set up a macro which selects a worksheet, the code is...
Sheets("DailyLeadGen").Select
But this comes up with the error message
Subscript out of range, I can't figure out what I've done wrong - can anyone
help?
Thanks

VBA Code for selecting worksheet
 

I'm going to take a stab and guess that you have more than one workbook
open and that the second workbook is currently the activeworkbook and
that it doesn't have a sheet named "DailyLeadGen"

What I suggest that you do is reference your workbook.

Example with two workbooks:

public sub versive()
dim wb1 as workbook, wb2 as workbook
set wb1 = workbooks("Book1")
set wb2 = workbooks("Book2")

' this will select "Sheet1" in "Book1"
wb1.sheets("Sheet1").select

' this will select "Sheet1" in "Book2"
wb2.sheets("Sheet1").select

set wb1 = nothing
set wb2 = nothing
end sub

Cheers,
Jason Lepack

Marie Bayes wrote:
Hi
I've set up a macro which selects a worksheet, the code is...
Sheets("DailyLeadGen").Select
But this comes up with the error message
Subscript out of range, I can't figure out what I've done wrong - can anyone
help?
Thanks

VBA Code for selecting worksheet
 

Sheets("DailyLeadGen").Activate
--
Gary''s Student


"Marie Bayes" wrote:

Hi
I've set up a macro which selects a worksheet, the code is...
Sheets("DailyLeadGen").Select
But this comes up with the error message
Subscript out of range, I can't figure out what I've done wrong - can anyone
help?
Thanks

VBA Code for selecting worksheet
 

Thanks, I'll give it a go.

"Jason Lepack" wrote:

I'm going to take a stab and guess that you have more than one workbook
open and that the second workbook is currently the activeworkbook and
that it doesn't have a sheet named "DailyLeadGen"

What I suggest that you do is reference your workbook.

Example with two workbooks:

public sub versive()
dim wb1 as workbook, wb2 as workbook
set wb1 = workbooks("Book1")
set wb2 = workbooks("Book2")

' this will select "Sheet1" in "Book1"
wb1.sheets("Sheet1").select

' this will select "Sheet1" in "Book2"
wb2.sheets("Sheet1").select

set wb1 = nothing
set wb2 = nothing
end sub

Cheers,
Jason Lepack

Marie Bayes wrote:
Hi
I've set up a macro which selects a worksheet, the code is...
Sheets("DailyLeadGen").Select
But this comes up with the error message
Subscript out of range, I can't figure out what I've done wrong - can anyone
help?
Thanks

VBA Code for selecting worksheet
 

This looks like an easy solution, i'll give it a go, thanks.

"Gary''s Student" wrote:

Sheets("DailyLeadGen").Activate
--
Gary''s Student


"Marie Bayes" wrote:

Hi
I've set up a macro which selects a worksheet, the code is...
Sheets("DailyLeadGen").Select
But this comes up with the error message
Subscript out of range, I can't figure out what I've done wrong - can anyone
help?
Thanks

VBA Code for selecting worksheet
 

Sorry to be a dummy but how do I fit that into this...
Sub RunJob()

'Opens the first workbook
Workbooks.Open Filename:= _
"\\Bedbackup\admin\Marie\BCA\SHARON-Telefocus Forecast 2007.xls",
UpdateLinks _
:=3
'Prevents warnings appearing on the screen, therefore no keyboard
intervention required
Application.DisplayAlerts = False
'Selects the whole worksheet and copies it
Sheets("daily lead gen jan").Activate
Cells.Select
Selection.Copy
'Opens the workbook for client and pastes the values and the formatting
Workbooks.Add
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
' Sheets("Sheet1").Select
Application.CutCopyMode = False
'Renames the worksheets (may not be required)
Sheets("sheet1").Select
Sheets("sheet1").Name = "Daily Lead Gen Jan"
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Cum Lead Gen Jan "
Range("A1").Select
'Saves and closes the workbook
ActiveWorkbook.SaveAs Filename:= _
"\\Bedbackup\admin\Marie\BCA\Test of Save.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
'Goes back to original workbook and selects and copies data from named
worksheet
Sheets("Cum Lead Gen Jan").Activate
Cells.Select
Selection.Copy
'Opens the workbook for client and pastes the values and the formatting
ChDir "\\Bedbackup\admin\Marie\BCA"
Workbooks.Open Filename:="\\Bedbackup\admin\Marie\BCA\Test of
Save.xls"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
'Saves the file and closes
ActiveWorkbook.SaveAs Filename:= _
"\\Bedbackup\admin\Marie\BCA\Test of Save.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close
End Sub



"Jason Lepack" wrote:

I'm going to take a stab and guess that you have more than one workbook
open and that the second workbook is currently the activeworkbook and
that it doesn't have a sheet named "DailyLeadGen"

What I suggest that you do is reference your workbook.

Example with two workbooks:

public sub versive()
dim wb1 as workbook, wb2 as workbook
set wb1 = workbooks("Book1")
set wb2 = workbooks("Book2")

' this will select "Sheet1" in "Book1"
wb1.sheets("Sheet1").select

' this will select "Sheet1" in "Book2"
wb2.sheets("Sheet1").select

set wb1 = nothing
set wb2 = nothing
end sub

Cheers,
Jason Lepack

Marie Bayes wrote:
Hi
I've set up a macro which selects a worksheet, the code is...
Sheets("DailyLeadGen").Select
But this comes up with the error message
Subscript out of range, I can't figure out what I've done wrong - can anyone
help?
Thanks