I care about is the line starting wks4.Cells(Di, 2) = I am trying to sum data from a variable length column in worksheet 3 and place the result in column 4 However, when I hit enter after entering the formula into =SUM() I get
Compile Error:
Expected: list separator or )
[code]
Sub Macro1()
Dim wks3 As Worksheet Dim wks4 As Worksheet
Set wks3 = Worksheets("Sheet3")
[Code].....
All code is called from another workbook
This fails on the second line with a type 9 error, ie worksheet not found
Workbooks("RENT_EXPLANATION.xls").Activate
Worksheets("Rent change details").Activate
This works:
Workbooks("RENT_EXPLANATION.xls").Activate
Workbooks("RENT_EXPLANATION.xls").Worksheets("Rent change details").Activate
Surely both should work since the default qualifier for the worksheets object is the activeworkbook? This only fails in Excel 2003, in the same app. in Excel 2000 it works.
I have a macro that copies selected data from various cells in WS1 to WS2's next open row, using offset to step to each new open cell in that row.
View 14 Replies View RelatedI'm trying to add a message box that will appear when you activate a worksheet in a workbook. This is what I have, but it's not working.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh. Name = "P2 Forecast" Then
MsgBox "Ensure you have locked your forecast on the Sales Forecast Tab prior to working your P2s"
Else
End If
End Sub
I have a worksheet which is used to display analysis data to the user. The sheet will be further protected with only some cells available to the user. I've used the attached code to retrieve some data from the sheet, clear a bunch of cells to reset the sheet and reset a graph (the graph itself is plotted as a log-log with the 1 to 100 on the Y-axis, hence the resetting of data to 0.5 to push it below the axis and make it invisible ready for later data dumping).
The code worked fine until I protected the worksheet. I have set the charts to unlocked using the format option in the chart area prior to locking. When I run the code though, it stops on the line indicated with the error "Application-defined or object-defined error". I've double checked and Chart 7 is the correct chart, and it is unlocked according to its format properties.
VB:
'get date of survey and equipment number for retrieving the data from the archive
dateSurvey = Worksheets("Calculation Page").Range("B2").Value
equipNum = Worksheets("Calculation Page").Range("F2").Value
'stop screen updating
Application.ScreenUpdating = False
[Code]....
EDIT: Oops, I've just noticed that even if I unprotect the sheet, I get an error on the .select within the seriescollection stating "Method 'Select' of Object 'Series' Failed", and the code worked perfectly before. I'm completely lost now...
For some reason I'm consistently getting an error in the following line:
Code:
Public wkbk1 As Workbook
Public shtInput As Worksheet
Public i As Integer, iPass as Integer
[Code]....
What command should I use if I want to activate sheet1 by clicking a coomandbutton1 on sheet2?
View 5 Replies View RelatedNot sure why this Code is not copying data across to the second worksheet.
The attached file 140404 Need Range.xlsm is an extract showing only the relevant elements of a much larger structure.
Drivers upload a stock record each day onto the Data Input sheet, listing what products and volumes they have loaded.
Because the range of products varies every day, this has to be set as a "dynamic" range on the worksheet.
To create a printable form, I need to copy each Item ID and Quantity from the Data Input sheet into the "Van Load" spreadsheet.
So Data Input D10 copies to Van Load E6 and the quantities from C10 to G6, down one row, repeat, etc. until the last row of variable Data Input range.
Option Explicit
Sub VANRECORD()
Dim lRow As Range, oCell As Range, nRD As Range, ws As Worksheet
Set lRow = ActiveSheet.Range("B" & Range("B65536").End(xlUp).Row)
[Code] .....
I am trying to write code to select a range in a worksheet where the last cell in the range is variable.
Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range
[Code].....
I am trying to run a script from a command button on another sheet in the same workbook. all the variables have been defined. I get a "select method of range class failed" message when I run the code. I believe that i am not getting "closed trades" to be the active worksheet.
excerpt of code
'Clear out existing data
iRow = 2
Set wks = ThisWorkbook.Worksheets("Closed Trades")
wks.Activate
lrow = Cells(65536, 1).End(xlUp).Row
Set Rng = wks.Range(Cells(iRow, 1), Cells(lrow, 17)) ' this row errors out
Rng.Clear
Set Rng = Nothing
I want to select the variable range somewhere in the middle of the sheet from where the 2nd instance of cell named "real cost" is, down to the next blank row (select the area without the blank row), so that I could copy it to another sheet.....
View 6 Replies View RelatedCode:
=SUMPRODUCT(--(_NamedRng1=NamedRng2),$B$49:$F$49)
I am using the above formula in my code with two Named Ranges
Code:
Set Rng3 = Range("_NamedRng1").Offset(1, 0)
=SUMPRODUCT(--(_NamedRng1=NamedRng2),rng3)
[/CODE]
I want to set the range $B$49:$F$49 in my code and I have tried the above, but it does not work.
I want to allow for the fact my end user may insert rows so do not want to use $B$49:$F$49
Subcript Out Of Range Error Coming Now For The Code Which Works For Me Before
Sub WHideRows()
Dim rRange As Range, rCell As Range
Dim strVal As String
Set rRange = Worksheets("WIED PROBLEM WELLS").Range("A11:A110")
For Each rCell In rRange
strVal = rCell(1, 3) & rCell(1, 4) & rCell(1, 5) & rCell(1, 6) & rCell(1, 7) & rCell(1, 9)
rCell.EntireRow.Hidden = strVal = vbNullString
Next rCell
End Sub
i am using the code above to hide the rows which doesn't have any values in all the following Cells 3,4,5,6,7 & 9 or Unhide the rows if there is value in any 1 of the following cells 3,4,5,6,7 & 9 from row number A11 to A110.
The same code works for me before. But now the code is not working. It says below the error message
Run-time error '9':..................
I have been using this code and just noticed that it resets all of my options buttons to false when I exit and then re-enter (activate) the sheet with the option buttons.
I see where this is going on, but don't know how to correct it. I only want the option buttons changed to false if
The Sol named range is something other than "Primary Vendor". It seems to call the macro ClearOB whenener the sheet is activated.
Private Sub Worksheet_Activate()
If Range("Sol").Value = "Primary Vendor" Then
For Each OB In ActiveSheet.OptionButtons
OB.Enabled = True
Next OB
ClearOB
ActiveSheet.ScrollArea = "A1:K58"
[Code] .......
I have several workbooks (5) with the same variables (columns- A:Q) but with a changing amount of rows (2:n, not including the headers). Each row corresponds to a date range (usually a week) for a particular person (up to 40 people) plus a few other values.
I would like to have a way of "merging" or "compiling" the 5 "seed" workbooks into 1 "master" worksheet. Where rows 2:n of each of the 5 "seed" workbooks are added to the master without any duplication of the same name-date range combination. Also, the master worksheet should not include the rows which only contain a name and date range but for which all the other variables are zero or missing.
Each "seed" workbook would have a button that sends the data over to the "master" worksheet.
Is this a really difficult project? Feasible for someone with near to zero VBA experience?
I attached 3 files to show you what I mean. The 2 "seed" files are merged into the "master" file. Please note that in the files only 2 names are used, but the "seed" files could contain any combination of 40 some names. Also note that the length of rows which contains data in the "seed" files is variable, although it should not be longer than 16 rows + the header row.
I have a macro that compares two sheets (in a workbook that might contain 10 sheets). Currently, the user enters the names of the two sheets in two input boxes. Is there a way that the user need only to click on a sheet tab and the sheet names are used in the macro? (this would eliminate any misspellings)
View 2 Replies View RelatedI want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count <> 1 Then Exit Sub
If Target(1, 1).Address = "dataentry!H5:IV72" Then
If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then
On Error Resume Next
Application.EnableEvents = False
In cell A1, I have the month number (eg, 1, 2, 3,). The month number reflects current month and will automatically change with every month. For example, right now it’s 6, next month it will automatically change to 7. Each two columns in Range A10:X20 represents the data from January to December. I want to use a worksheet event to change the background of the current month two columns in the range to yellow color and the two columns in the range will be visible when I activate this sheet.
View 3 Replies View RelatedI am attempting to use the code below to activate a sheet whose name is contained in a variable. The line in Bold gives me a "Subscript out of range" error even though the variables seam correct in the debugger.
Code:
Private Sub GetFromArchive_Click()
Dim wkb As Workbook
Dim strPath As String: strPath = "M:EfpDocsArchivesESI Processing Log Master Archive.xls"
Dim lngListItem As Long, lngSelected As Long
Dim varSheets() As String
[Code] .......
I have a workbook that contains 50 worksheets named 1-50. I need to add more worksheets. all the formulas in the worksheets always refers to the previous worksheet.
How can i make a copy of the worksheet named 50, name it 51 and have all the formulas in worksheet 51 refer back to worksheet 50?
AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes".
Sitenameshort = "_Bmth"
ShtName1 = "Weekly" & Sitenameshort
shtName = ShtName1. Name 'This line doesnt work
Set Nws = Sheets(shtName)
It Errors on the marked line.
I'm having difficulty using a worksheet variable with control objects on worksheets.
The following code is successful at getting the value of the text in textbox txtDCN.
Public Sub test()
Dim text As String
text = Worksheets("Master Table").txtDCN.Value
End Sub
The following code gives compile error "Method or data member not found" on the last line of code. I've searched around a bit all over the web but cannot find any examples of this same error. It's obvious that the compiler cannot figure out the reference to the text box but I cannot figure out why for the life of me.
Public Sub test()
Dim ws As Worksheet
Dim text As String
Set ws = Worksheets("Master Table")
text = ws.txtDCN.Value
End Sub
I have a macro on a button click that copies and pastes the template worksheet into the same workbook on a dialy basis, i would like to rename the "template" copied file to a unique worksheet name based on a date ie 31_3 (31st march) and so on, the cell that provides the date changes on a daily basis to the next one, however when the script runs it uses the original macro recording date rather than the new one!, the changable cell is AS2.
View 3 Replies View RelatedI am trying to run some code when a worksheet is selected.
I'm doing this using a macro currently (code below), but this requires that i use a quick menu button and i would like to make it a form button and assign a macro to it (so i can distribute the file without everyone having to create a menu button).
I would like to replace (or modify) the code below to select any sheet other than the ones named "a" and "b".
Code:
Sub ImportAlarms()
Dim thisSheet As Worksheet
Dim targetSheet As Worksheet
On Error GoTo failed
Set thisSheet = Application.ActiveSheet
Set targetSheet = Sheets(TARGET_SHEET)
[Code] .......
I'm replacing a specific sheet on a workbook with another one, but I want the users to be able to browse to a file, replace the sheet with the new one and re-establish the links on the replaced sheet with the target workbook. This macro is running out of the workbook that supplies the replacement sheet. The idea is that the user opens this workbook, runs the macro, browses to the file they need to fix and hey presto! I'm doing something stupid with my variable name, I'm sure it's syntax but I don't know what I'm doing wrong. The code is posted below.
Sub Macro1()
Dim ToBeFixed
ToBeFixed = Application.GetOpenFilename
Workbooks.Open (ToBeFixed)
Sheets("!").Visible = True
Sheets("!").Select
ActiveWindow.SelectedSheets.Delete.....................
In conjuction with a previous request I've tried to create my own vba code (actually amend existing code i have) but its failing........ whats wrong??
Sub Retail_1_Xfer()
Dim NextRow As Long, Isht As Worksheet, Lsht As Worksheet
Application. ScreenUpdating = False
Set Isht = Worksheets("Retail Team 1")
Set Lsht = Worksheets("Raw Data")
NextRow = Lsht. Cells(Rows.Count, 2).End(xlUp).Row + 1 'next empty row in col A
Lsht.Range("A" & NextRow).Value = Isht.Range("A9").Value
Lsht.Range("B" & NextRow).Value = Isht.Range("A4").Value
Lsht.Range("C" & NextRow).Value = Isht.Range("B9").Value
Lsht.Range("D" & NextRow).Value = Isht.Range("N3").Value
Lsht.Range("E" & NextRow).Value = Isht.Range("N2").Value
Lsht.Range("F" & NextRow).Value = Isht.Range("BJ9").Value
End Sub
I think its got something to do with the fact that the worksheet " Retail Team 1 " is in another workbook
I am trying to pass two variables RARD and CARD from Sheet1 'change selection' subroutine to the 'Set ARD Command button click' subroutine. The variable value remain empty and it gives me a 1004 error when I try to execute this line of code:
[Code] ....
What I am trying to do is put the text value 'ARD' in the cell on sheet1 which was the original cell I clicked on to open the userform2. To do this I click on the 'Set ARD' button on the userform2 which is then suppose to hide the userform2 and put the 'ARD' text in the selected cell on Sheet1.
I have attached the workbook for review : Therapy Tracker - Tester V2.10 - deleted logo.xlsm‎
I am working on a workbook which uses a large number of variables. I am trying to keep them as "local" as possible to keep it simple. Some of my variables are local to the subs they're used in. Some are global as they're used by subs in several sheets. A third type of variable is used by several subs all belonging to the same sheet. Is there a way of declaring them so they're known by all subs in that sheet, but not by every sub in the workbook?
View 4 Replies View RelatedI'm using MS Excel 2003 Pro. I need to link data from one source worksheet to a target worksheet in the same workbook when only one variable changes in the source worksheet e.g.:
A. From source worksheet:
When the data under the "Expected Date" column is set (it normally is blank)
B. Populate target worksheet with:
1. Name
2.aaaa
3.bbbb
4.cccc
5.dddd
6. expected date (the actual date that is set from the source worksheet)
This is a simplified version of an earlier, long winded post that clouded my actual question.
Can I use a variable to refer to a worksheet by its CodeName? For example, let's assume I have several worksheets, with CodeNames of mySheet1, mySheet2, mySheet3, and so on. I understand that I can refer to them directly,
mySheet1.Range(myRangeName).value = someValue
mySheet2.Range(myRangeName).value = someValue
mySheet3.Range(myRangeName).value = someValue
.
but what if I want to do this using a loop? Is it possible to preload an array of CodeNames and do it that way? I'm thinking along the lines of something like...
Dim CodeNames
CodeNames = Array( mySheet1, mySheet2, mySheet3 ...)
For x = 1 to HoweverMany
CodeNames(x).Range(myRangeName).value = someValue
Next x