Subscript Out Of Range When Selecting Range In Closed Worksheet

Apr 28, 2006

I am receiving a 'subscript out of range' error on the lines of code below.

I would note that all variables are declared and all seem meaningful as regards what you would expect at that point.

Below is a snippet from the immediate window which indicates what the values are:
completecashname C:CashDevelopmentMyFolderoutputCASH042706.xls
cashsheetname Formatted Sheet
cashcurrcolumn A
cashfirstrow 2
cashlastrow 876

Also the workbooks are both closed at this point (but it makes no difference)

Set CashCopy = Workbooks(CompleteCashName).Sheets(CashSheetName). _
Range(CashCurrColumn & Cashfirstrow & ":K" & Cashlastrow).Value
Set PelPaste = Workbooks(completepelname).Sheets(PELSheetName). _
Range((PELCurrColumn & PELlastrow)).Value

View 8 Replies


Macro To Save Worksheet As PDF - Subscript Out Of Range

Mar 20, 2013

I have code to save an excel sheet as a PDF and attach it to an email but when it try to modify it to save as an excel, it causes errors. Below is (1) the excel code I'm trying to fix; and (2) the working code for my PDF function. When I run the macro to save the excel worksheet in a new workbook, it creates the sheet in the a separate workbooks, but then gives as Run-time error '9': Subscript out of range." When I run debug, the line in red is what's highlighted. So it's not liking something with the filename/path??(1)

Code to save worksheet into new workbook (broken)Sub SaveAsExcel2()
Dim FileFormatstr As String
Dim Fname As Variant


View 3 Replies View Related

Vlookup Using A Range From A Closed Worksheet

Dec 13, 2008

way to do use a vlookup using a range from a closed worksheet?

View 9 Replies View Related

Code To Prevent Editing & Selecting Of Range Worksheet

Sep 3, 2008

I have the following code that Ger Plante very kindly helped me with which, depending on whether there is an 'x' by someone's name in a list, creates a new workbook, copies some information to it and saves it before moving on to the next 'x'. Loop Through Rows & Copy Each Row To New Workbook

For lLoop = 2 To 251 'first row of data to last row.
If ws1.Cells(lLoop, 4).Value = "x" Then '4 = Column D
ws1.Range("e" & lLoop & ":g" & lLoop).Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.SaveAs varPath & "Student Data Files" & ActiveSheet.Range("B1") & ".xls"

End If

Next lLoop

how I can modify the code such that any cells in the range "b1:b504" in Sheet1 of the the new workbook can't be selected or edited without a password....I have tried unsuccessfully using Protect but am not sure how to get vba to set it to specific cells and determine exactly what is allowed in those cells.

View 5 Replies View Related

Copy Range From Closed File & Paste To Closed Workbook

Apr 26, 2008

Is there code that will take certain data from one Excel sheet to another named file in a different place on the network? Example Copy cell aa47 from "Recent Faxes.xls" that sits in "correspondence" folder. Then paste into cell B25 "Current Documentation.xls" in the "Sales Contacts" folder

View 2 Replies View Related

Copy/Paste Range(s) Without Activating/selecting Range(s)

Oct 2, 2008

To initialize some cells/ranges, I am copying a given range and pasting it to another given range using the. Copy and .PaseSpecial methods. However, it would seem that both methods actually select the range(s) for the operations, i.e. the given ranges(s) are activated/selected thus changing the focus on the spreadsheet. I would like to perform both operations without actually selecting the given ranges.

View 5 Replies View Related

Subscript Out Of Range ....

Aug 6, 2009

I had the worksheet (VCollinsville) misspelled in the Workbook.

I have the following code (it's long but my problem is at the end where I stop): ...

View 4 Replies View Related

Subscript Out Of Range ..

Nov 7, 2009

I have a master spreadsheet in XL2003 (SP3) running Vista. The master sheet, called "DCam IV-11.xls" is a large calculation sheet that is driven by user entered boundary conditions. The boundary conditions are stored in different input files named "******-inp.xls". I use this method to store BCs in small spreadsheet files rather than saving full master spreadsheets in files.

View 6 Replies View Related

Subscript Out Of Range

Jul 26, 2006

keep getting an error message saying subscript out of range.

What am i missing?

'setting up the variable in the projectManagersWorkbook object
Set projectManagersWorkbook.Wkbk = Workbooks(getfileName(projectManagersWorkbook.filePath))
Set projectManagersWorkbook = projectManagersWorkbook.Wkbk.Worksheets("project managers")
Set projectManagersWorkbookLookupCell = projectManagersWorkbook.wks.Range("A2")
' searching for the Last Status Column from the last PO spreadsheet
vlookupcolumn = 1
Do Until tempValue = "Project Manager"
tempValue = projectManagersWorkbookLookupCell.Offset(-1, vlookupcolumn)
vlookupcolumn = vlookupcolumn + 1

View 5 Replies View Related

Subscript Out Of Range Error

Jun 26, 2014

I keep getting an error message when running the following code.

I am not sure why, but I keep getting a "Run-Time error '9': Subscript out of range" error message.

View 3 Replies View Related

Getting Subscript Out Of Range Error

Jul 23, 2014

What I'm trying to do is change the text color of specific keywords in a spreadsheet. For example, I'd like to highlight the text of 'how to' in the following sentence "I want to know how to change text color", so that it looks like this "I want to know how to change text color".

I came across the following, and it works for only two terms (example, "how to" , "how do I"), but I have a string of about 15 terms I'd like to use, but anything I change over two, I get the subscript out of range error.

[Code] .....

View 13 Replies View Related

Subscript Out Of Range Error 9?

Feb 25, 2014

what I'm trying to do is automatically format Excel's track changes to look like Microsoft Word's track changes feature i.e. put in a strike-through when people make changes on a shared spreadsheet.

I found the following script from here: [URL] .....

Dim xLen As Integer
Dim x, y, z, addr As String


Unfortunately, that produces the "Subscript out of range (Error 9)."

Clicking debug will highlight 'Worksheets("History").Activate', but I'm assuming that will just be one problem among a few others.

I've also tried replacing the 'x' in 'Worksheets(x).Activate' with the name of the worksheet (with extension xlsm), but no joy.

View 1 Replies View Related

Subscript Out Of Range :: Both Worksheets

Apr 27, 2009

I have this code below that keeps giving me a subscript out of range error and I don't know why I got it to run once but can't figure out the problem now...

View 4 Replies View Related

Debug: Subscript Out Of Range?

May 29, 2009

trying to copy some records across from once sheet to another?

butt it keeps debugging?

any idea why.

i'd like to keep the user defined type because it's neat and is good practice for myself?

View 9 Replies View Related

Subscript Out Of Range Error ...

Jul 14, 2009

I am having trouble in a dowhile loop that i have created. Its purpose is to copy an unknown amount of data points into an array while it counts how many data points it collects and how many of them are above a reference pressure. I have defined data_A(),data_points,above_reference, and reference_pressure all as integers. The error occurs in the second line of code.

View 14 Replies View Related

Subscript Out Of Range After Completion

Dec 31, 2009

I'm getting a "Subscript out of Range" error on a routine that was running fine a few days ago. It's part of a larger macro, so I've pasted the full code for that macro below. The problem is in the routine labled "Replace Regional Labels". What that's supposed to do (and DOES do) is to replace labels such as 110 or 11000-CSD with 11000. The error message comes up AFTER the routine has completed it's work. The offending line is:

View 2 Replies View Related

Subscript Out Of Range Error

Sep 21, 2011

I have a workbook called "Project" and in it there is a macro that pulls data out of another workbook called "Operations Report."

The macro works fine on my laptop. When I run the macro, I make sure that both workbooks are open, (and they are also saved in the same directory - not sure that matters).

I sent the two workbooks to a colleague and when she opens them both and runs the macro, she gets a "run error 9 subscript out of range" error. She too has saved them to the same directory.

why she might be getting the error when I do not? Here is the line where hers trips up:

With Workbooks("Operations Report").Worksheets("Sheet2")

I assume that the macro is not finding the "Operations Report" workbook.

Are there some checks we could do or test lines of code I could send to her to diagnose the problem?

View 9 Replies View Related

Error 9 Subscript Out Of Range

Dec 23, 2013

Error 9 - Subscript Out Of Range....All sheets are in workbook and spelt correctly....could it be in the Array or Loop part of the code.

It also in the userform have a combobox for Day,Afternoon and Night but it transfers all across.Need the code to just transfer what is selected from the combobox (could be a listbox as well).If day selected then only Day for the selected date,,,same for afternoon and night unless All selected in which all are transferred (DAy,Afternnon & Night)

This part of he code gives error

arrOP_PS(PSCounter, 1) = CDate(Data(CurrentRow, 1)) 'date


Dim Data
Dim arrOP_PS(1 To 6, 1 To 5), PSCounter As Long
Dim arrOP_MSI(1 To 6, 1 To 5), MSICounter As Long
Dim arrOP_MSE(1 To 6, 1 To 5), MSECounter As Long

Private Sub CommandButton1_Click()

[Code] ........

View 6 Replies View Related

Subscript Out Of Range With New Computer

Jul 22, 2014

I created a code and I just changed computer. The code that was working perfectly fine on my previous laptop is no longer working now.

ActiveWorkbook.SaveAs Filename:= _
"C:UsersUserDocumentsBCB MANAGEMENTBankArchiveData_TenantsData_Tenants_" & Left(strDate, 8) _
, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Set wbData = Workbooks("Data_Tenants_" & Left(strDate, 8))

When it reaches Set wbdata it says subscript out of range. I'm not sure how it cannot recognise the spreadsheet I just created and that is open and working.

View 5 Replies View Related

Subscript Out Of Range - VBA Macro

Aug 1, 2014

I am getting he Subscript out of range error when trying to run this macro. I have the macro on a macro workbook and I am referencing another spreadsheet.

Sub Dater()
Static Count As Integer
Dim Wb As Workbook
Dim Ws As Worksheet
Dim FileStr As String
Set Wb = Workbooks("Pending Approval by Responsible User")
Set Ws = Worksheets("Pending Approval by Responsible")

View 4 Replies View Related

VBA Error, Subscript Out Of Range, 9

Nov 16, 2007

Sub Split_By_Store()

x = Cells(Rows.Count, 1).End(xlUp).Row

MsgBox x

a = "F1:F" & x

Range(a).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("J1"), Unique:=True

y = Cells(Rows.Count, 10).End(xlUp).Row

For b = 2 To y
Worksheets.Add.Name = Worksheets("data").Cells(b, 10)
Next b

For c = 2 To x ' copy the data now
d = Worksheets("data").Cells(c, 6)
z = Worksheets(d).Cells(Rows.Count, 1).End(xlUp).Row
Worksheets("data").Range("A" & c & ":I" & c).Copy
Worksheets(d).Cells(z + 1, 1).PasteSpecial
Next c

End Sub

This code breaks on the line z =, I get a Subscript out of range:9 error. Basically this starts with a single sheet called "data". Column F has store number and column J is created as a helper column and each unique store number is transfered there... Then sheets are created for each unique store number from the helper column. Then it's supposed to transfer the area A:I from the "data" sheet for each store that is unique from column F and transfer that to the corresponding Store number for the sheet that was created for it in the earlier step.

View 9 Replies View Related

Subscript Out Of Range Even With Recorder

Feb 21, 2007

I have recorded a macro that will copy and paste information from text files to two separate sheets in a workbook. Previous versions of this have worked fine but now I have a "subscript out of range problem" for this line: Sheets("Raakadata").Select

I'm doing this project to someone else and the weird thing is I don't see this error in my computer but they have it in theirs. The workbook has been recently renamed to oljy_vedesta.xls but it should be correct in the code. The syntax should be fine as macro recorder has been used but still this error comes up with the another computer (all the required files are there).

Sub Tulosten_haku()
ChDir "C:Makro"
Workbooks.OpenText FileName:="C:MakroOLJY_VEDESTA_QC.TXT", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, FieldInfo:= Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1))
Workbooks.OpenText FileName:="C:MakroOLJY_VEDESTA.TXT", Origin:=xlWindows _
, StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 1), _
Array(3, 1), Array(4, 1), Array(5, 1))..........................

View 2 Replies View Related

Subscript Out Of Range Error

Jul 25, 2007

I have in the attached workbook, a form which I run, the macro StartForm. This brings up the form, where in A, I select the sheet label Oval_An, and in B, I select the sheet Oval_DMA. The other drop down boxes are not in use yet. Once A and B are selected, the user hits the big subtract button, and normally gives the the new sheet, Final Results.

Yet, when I change the name of the forms, or allow the kTest compare variance box into the macro to be user definable via the form, the whole thing just stops working.

The error is found in the kTest macro, line 12 as a type mis match.

Redim q(1 To UBound(a, 1), 1 To 1)

I have had a whole lot of help from people with this, and I am new to VBA.

View 9 Replies View Related

Array Subscript Out Of Range

Aug 9, 2007

I have a form that finds an loads a record in textfields and combo boxes fine. When there are more than 1 record found, the user is presented with the FindAll option. Upon clicking find all, the list box gest populated with the records found. But instead, I am getting Run-Time error:'9' Subscript out of range in the following code block. It is occurring in the block starting with myArray: - MyArray(i, 0) = fndA:

Private Sub cmbFindAll_Click()
Dim MyArray(6, 12)
Dim FirstAddress As String
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search
Dim fndA, fndB, fndC, fndD, fndE, fndF, fndG, fndH, fndI, fndJ, fndK, fndL As String
Dim head1, head2, head3, head4, head5, head6, head7, head8, head9, head10, head11, head12 As String ' heading s for list
Dim i As Integer
i = 1
Set rSearch = Sheet1.Range("A6", Range("A65536").End(xlUp))
strFind = Me.txtWorkOrderNo.Value
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)
If Not c Is Nothing Then 'found it
'load the headings

View 9 Replies View Related

Selecting A Range Based On Date Within That Range

Sep 26, 2007

What i'm trying to do is select a range based on the date which happens to be in the first column of the range.

For example, I have a column (A) that contains dates and column (B) that contains names.

Its easy enough to select the entire range using


I need someting that will analyse column A and conditionally selct the range based on the date in column A (ie. it will only select the rows where column A has todays date).

View 9 Replies View Related

Run Time Error - Subscript Out Of Range 9

Jul 16, 2014

I have three sheets in My workbook and one user form. based on selection of checkbox in userform, I want to activate the respective sheets. While clicking on add button, I am getting error message " Run Time Error : Subscript out of range 9". I am using following code

Option ExplicitPrivate Sub Add_Click()
Dim ctrl As Control
For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "CheckBox" Then
TransferValues ctrl

[Code] .....

I am getting error on "Set ws = Sheets(Left(cb.Name, 1))" line.

View 9 Replies View Related

Runtime Error 9 - Subscript Out Of Range

Feb 12, 2014

I am trying to consolidate data in Sheet7 of all the files in a folder to a single file. In case a file doesn't contain Sheet7, i want to get the name of the workbook. The code is working fine, but after it loops it is returning Runtime Error 9 - Subscript out of range.

[Code] .....

View 5 Replies View Related

Error Message 'Subscript Out Of Range'

Aug 24, 2009

See if you can see what is wrong with this code - I have no idea. All the spreadsheet names are correct and I have used this syntax before, I don't know what I am doing wrong. The macro has a problem with the 3rd line

View 2 Replies View Related

Run Time Error 9 : Subscript Out Of Range

Aug 27, 2009

This program is supposed to take the value in two combo boxes and use them to populate pivot charts that are in other spreadsheets. So a user would select PS and AMI on sheet one (s) and it would change the pivot table on s1-s9 to look up those chosen fields. I keep getting a subscript out of range error, and I'm not sure if my pivottable.pivotlayout method is correct, but when I recorded a macro (in the very bottom) using activesheet, it worked. How can I fix this?

The error occurs at the first large text, the second large text is the recorded macro.

View 14 Replies View Related

Subscript Out Of Range :: Control Sheet

Sep 4, 2009

I have a workbook with 20 sheets, including a control sheet 'Validation Table'. By setting the required Background/Font in a cell named hdrDefault on sheet VT, I want to click a button on VT to invoke a macro to visit each sheet and set the named Header range, hdrxxx, to the same Background/Font. Originally had the code in a Private Sub linked to a button event, but this can't break out of the worksheet so have moved the Sub into a Module, and calling from the click event:


View 3 Replies View Related

Copyrights 2005-15, All rights reserved