Out of Script Range

[wafs]

Here is the code that I copied over to use in a new macro. It works where it was orignal created, but I get the out of script range. What am I missing? Line located at the --->.

Next
'Save Back up file
'Set active sheet as a workbook
'file location = workbooks ("Project Internet Buys - Nick").Sheets("campaign").Range("E2")
fileLocation = Sheets("campaign").Range("E2")
ChDrive "S" 'Change to the S-Drive
ChDir "S:\"
------> Sheets(Destsh).Select
Fname = InputBox("What do you want to name the file? (The date will be added automatically to the name.", "File Name")
DateHold = Date$
'Timehold = Times$
Fname2 = Fname + "" + DateHold
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=Fname2 & ".xls"
ActiveWorkbook.Close SaveChanges:=False

'Display saved message
strMessage = Fname2 & "savid in" & vbCrLf & vbCrLf & CurDir
MsgBox strMessage, vbInformation, "File Saved! Have a Super Day."""
'Turn on Screen Updating
Application.screeupdating = True

 

[xlbo]

do you have a worksheet called 'Destsh' in the workbook being used ?

 

[SkipVought]

Hi,

What's the VALUE of Destsh in Sheets(Destsh).Select

Is it the name of one of the sheets in this workbook?

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[wafs]

NO, but neither did the other workbook that I got it from.

 

[xlbo]

is it a variable ?? looks like it? If so - what is the value? If not, it must be a sheet codename - you can see these in the Visual Basic Editor next to the sheet tab names in the Project Exploer window

either way, you need to reference a sheet that exists on that line...

 

[wafs]

here is the complete code, Destsh is a string.

Sub Step1_PopulateInternetBuys()
'
' *****************************************************
' PopulateInternetBuys MACRO
'
' Description: This macro allows for the automatic entry
' data from the internet schedule into a list of all
' buys from that campaign.
'
' Macro created on 12/08/2005
'
'
' Written by Nick Britsky/Wendy Smith
' *****************************************************

' Declare Variables
Dim CellStart As Integer 'Row location of first sale
Dim CellEnd As Integer 'size of ad
Dim CellMaster As Integer 'Hold CellStart for Duration of Macro
Dim x As Integer 'For Statement Variable
Dim y As Integer 'For Statement Variable
Dim TestRange As String 'Test String
Dim TestRange2 As String 'Test String
Dim TestRange3 As String 'Test String
Dim TestRange4 As String 'Test String
Dim TestRange5 As String 'Test String
Dim i As String 'Counting Variable
Dim TotalBuys As String 'Count Total buys
Dim ReportLocation As String 'Location of output on report sheet
Dim BuyOutput(1 To 12) As String 'Output for individual buy
Dim n As String 'Counting Variable
Dim sh As String 'Source Sheet
Dim Destsh As String 'Destination Sheet
Dim CSVSh As String 'Sheet for CSV file
Dim m As String 'Counting Variable
Dim v As String 'Counting Variable
Dim z As Integer 'Another For Variable
Dim BuyDate(1 To 4) As String 'Month / Year of Buy / Holding Space
Dim TotalPapers As Integer ' Count Number of ads outputed
Dim AdCodeMaster(1 To 12) As String 'Stores the ad code: color / code, color / code, etc
Dim Dummy As String ' Test
Dim StartMonth As String ' Hold Month Name
Dim Month As Integer 'Count month
Dim Buyer As String 'Buyers Name
Dim Fname As String 'File Name for saved CSV file
Dim Fname2 As String 'Combo of date and file name
Dim wb As Workbook
Dim DateHold As String ' Hold date variable
Dim strMessage As String 'output message for saving CSV file
Dim MsgVarialbe As Integer 'hold msgbox variable
Dim fileLocation As String 'Location of Saved CSV file
Dim cost As Integer 'cost / days per ad

' Initialize Variables
CellStart = 4
'CellMaster = CellStart
TotalBuys = 0
TotalPapers = 0

Month = InputBox("How many months in campaign? (number of months, example: 6)", "Month")
StartMonth = InputBox("Campagin Starts on which month? (three letters, example: Jan)", "StartMonth")

Select Case StartMonth
Case "Jan"
BuyDate(4) = "01/"
Case "Feb"
BuyDate(4) = "02/"
Case "Mar"
BuyDate(4) = "03/"
Case "Apr"
BuyDate(4) = "04/"
Case "May"
BuyDate(4) = "05/"
Case "Jun"
BuyDate(4) = "06/"
Case "Jul"
BuyDate(4) = "07/"
Case "Aug"
BuyDate(4) = "08/"
Case "Sep"
BuyDate(4) = "09/"
Case "Oct"
BuyDate(4) = "10/"
Case "Nov"
BuyDate(4) = "11/"
Case "Dec"
BuyDate(4) = "12/"
End Select

sh = "Campaign" + StartMonth
Destsh = StartMonth + "-IOC"
CSVSh = StartMonth + "-CSV"

' Turn off Screen Updating
'Application.ScreenUpdating = False

' Build ioc Sheet
For z = 1 To 50
'Call Function to Count lines
'CellEnd = CountHeight(CellStart, sh)
'Dummy = storeAdCode(CellStart, sh, AdCodeMaster)

'Get Data from Rows
i = CellStart
TestRange = "J" + i
TestRange5 = "BK" + i
For x = CellStart To CellEnd
If Sheets(sh).Range(TestRange) > 0 Or Sheets(sh).Range(TestRange5) > 0 Then
TestRange2 = "A4" + i
TestRange4 = TestRange2
Sheets(sh).Range(TestRange2).Select
v = 1
For y = 1 To 39
n = CellStart
If Selection <> 0 Then
TotalBuys = TotalBuys + 1

' Prep Client
BuyOutput(10) = Sheets(sh).Range("L1").Value

'Prep Prodcut
BuyOutput(9) = Sheets(sh).Range("L2").Value

'Prep Estimate
BuyOutput(2) = Sheets(sh).Range("L3").Value
'Buyers Name
BuyOutput(12) = Sheets(sh).Range("L6").Value

'Prep Date

i = Month
TestRange = Month + i
Do
i = i + 1
TestRange = Month + i
Loop While Not IsEmpty(Sheets(sh).Range(TestRange))
i = i + 1


'Prep Start Month
BuyDate(3) = "/06"
If v < 4 And StartMonth = "Jan" Then
BuyDate(1) = "12/"
BuyDate(3) = "/06"
Else
BuyDate(1) = BuyDate(4)
End If
v = v - 1
BuyOutput(7) = BuyDate(1) + BuyDate(3)

'Prep Cost
'Cost(i) = Sheets(sh).Range(TestRange).Interior.ColorIndex = RGB(250, 250, 0)
'j = j + 1
'Do
'AdCodeMaster(j) = Sheets(sh).Range(TestRange).Value
'j = j + 1
'i = i + 1
'Loop While Not IsEmpty(Sheets(sh).Range(TestRange))
'BuyOutput(6) = sheets(sh).Range(BuyoutPut(5)).value

'Prep Ad Code

'BuyOutput(8) = FindAdCode(AdCodeMaster, sh, TestRange4)

'Output Info to -IOC sheet
m = TotalBuys + TotalPapers + 3
ReportLocation = "E" + m 'Pub
Sheets(Destsh).Range(ReportLocation) = BuyOutput(4)
ReportLocation = "F" + m 'Date
Sheets(Destsh).Range(ReportLocation) = BuyOutput(7)
ReportLocation = "H" + m 'Cost
Sheets(Destsh).Range(ReportLocation) = BuyOutput(6)
ReportLocation = "I" + m 'Ad Code
Sheets(Destsh).Range(ReportLocation) = BuyOutput(8)
ReportLocation = "B" + m 'Client
Sheets(Destsh).Range(ReportLocation) = BuyOutput(10)
Else
End If
Selection.Offset(o, 1).Select
TestRange4 = Selection.Address
v = v + 1
Next
Else
End If
i = i + 1
TestRange = "J" + i
TestRange5 = "BK" + i
Next
CellStart = CellEnd + 2
TotalPapers = TotalPapers + 1
Next
'Save Back up file
'Set active sheet as a workbook
'file location = workbooks ("Project Internet Buys - Nick").Sheets("campaign").Range("E2")
fileLocation = Sheets("campaign").Range("E2")
ChDrive "S" 'Change to the S-Drive
ChDir "S:\"
Sheets(Destsh).Select
Fname = InputBox("What do you want to name the file? (The date will be added automatically to the name.", "File Name")
DateHold = Date$
'Timehold = Times$
Fname2 = Fname + "" + DateHold
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=Fname2 & ".xls"
ActiveWorkbook.Close SaveChanges:=False

'Display saved message
strMessage = Fname2 & "savid in" & vbCrLf & vbCrLf & CurDir
MsgBox strMessage, vbInformation, "File Saved! Have a Super Day."""
'Turn on Screen Updating
Application.screeupdating = True

End Sub

 

[xlbo]

Destsh = StartMonth + "-IOC"

Do you have a sheet that will be like "Jan-IOC" ???

if not - the code will fail - you cannot reference items that aren't there. Also seems like this code is VERY specific to a certain task - doesn't look like it will be easily transferable....

 

[wafs]

Thanks. I do have a sheet marked -IOC.

 

[xlbo]

and before it ???

your variable 'Detstsh' is given a value of whatever you put in the input box, concatenated with '-IOC'

eg, if you entered Jun into the input box referenced by:
StartMonth = InputBox("Campagin Starts on which month? (three letters, example: Jan)", "StartMonth")

your variable Destsh would hold Jun-IOC

What are you entering into the Inputbox ?? and do you have a valid sheet name based on that ?

 

[wafs]

I input July and I have a sheet name -IOC. I know that I have to change the date section because I will need to make a loop out of it so it can do more than one month at a time.

ie: start in July and ends # months later. I ask how many months the ad banner will run, then I enter the month that the banner will start.

 

[SkipVought]

...I input July and I have a sheet name -IOC...

Part of your problem may be that you are not following the coded instrucions...

...
    StartMonth = InputBox("Campagin Starts on which month? ([b][red]three letters, example:  Jan[/red][/b])", "StartMonth")
    
        Select Case StartMonth
            Case "Jan"
               BuyDate(4) = "01/"
            Case "Feb"
               BuyDate(4) = "02/"
            Case "Mar"
               BuyDate(4) = "03/"
            Case "Apr"
               BuyDate(4) = "04/"
            Case "May"
               BuyDate(4) = "05/"
            Case "Jun"
               BuyDate(4) = "06/"
            [b][red]Case "Jul"[/red][/b]
               BuyDate(4) = "07/"
...

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[wafs]

No, I actually do input Jul. I just need to express correctly what I'm trying to say.

 

[SkipVought]

I'd guess that your -IOC sheet is a template that might be intended to COPY and rename as Jul-IOC, for instance.

BTW, what happens from Dec to Jan? New workbook?

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[wafs]

What I'm trying to do is have all the internet buys by month grouped together. So you have:

Jul
Yahoo
Google
AOL

Aug
Yahoo
Google
AOL

Everything on one sheet for the current campaign (time frame of the buys). The information on the sheet will be replaced when a new campaign starts.

 

[SkipVought]

That is not a very good method of storing your data. You will have a difficult time of analyzing and reporting on SUMMARY INFORMATION, like Business in the 1^st Quarter of 2006 compared to the 1^st Quarter of 2005, or annual sales/income/expenses whatever.

Much better to structure your data in a single table from which you could report by week, month, quarter, year, campaign, Hosting Service, etc.

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Skip,

_{[red]Be Advised![/red] The Vinyards of Texas have produced a wine with diuretic dimishment and urethric relief...
Pinot More!}

 

[xlbo]

Plus I'm really not sure what the question is now.....

It started as a "why is my code failing at this line" question - which I believe is now answered - the code fails because the sheet name you were trying to reference did not exist.

If you have new questions, please start a new thread