Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Unhide Sheets/Worksheet With Variable CodeNames


I want to make a hidden sheet visible. Except that i want to use the name of the sheet (the thing that stays the same even when you change the sheet tab name). I also want to select the name based on a variable called Year which the user adds elsewhere (eg as 2006).

wf = "WF_Edin_" + Year 'So for 2006 this would read WF_Edin_2008
wf.Visible = True

The above doesnt work as wf becomes a string which i dont think i want do i?


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Unhide All Sheets
I receive a lot of excel files and would like a macro that I can run to unhide any and all hidden sheets. The sheets will have different tab names. I looked on this forum and could not find the answer to this question, usually I can.

View Replies!   View Related
Password To Unhide Sheets
I wanted to know if there is a way to lock some sheets in a workbook to prevent anyone to view them. I have a workbook with sheets that have a database, it is shown the frontsheet, and when a person selects an option it will appear a sheet with the data selected initialy. I wanted the other sheet to be hidden and locked so the one goes to the unhide menu and selects a sheet to unhide a msgbox appears to ask a pass code.

View Replies!   View Related
Unable To Unhide Sheets
I have a workbook with several sheets that I created some time ago. I have hyperlinks on the main sheet which point to other sheets in the same workbook which can be viewed when using the hyperlink, however the linked sheets are hidden. Somehow in this workbook I removed both the horizontal scroll bar and the lsiting of sheet names. I can not figure out how to get them back so I can unhide and or add new sheets.

View Replies!   View Related
Hide/ Unhide Sheets With Macro
I want to run 2 different macros:

Macro 1- hides Sheet1 and unhides Sheet2
Macro 2- Hides Sheet2 and unhides Sheet1

I used the macro recorder to attempt to make this work but am running into a problem if Macro1 is run two times consecutively. In this situation the macro displays a debugging error b/c Sheet1 is hidden. Is there a way to get around this...possibly using an if then statement?

View Replies!   View Related
Hide Sheets, Then Need Password To Unhide
See I have hidden my sheets and I need them to stay hidden.

But when I need to unhide them, a password should be needed to unhide them.

Maybe add a button on one of the open sheets that when pressed will unhide the sheets but will promt for password.


View Replies!   View Related
Unhide All Sheets That Do Not Contain Certain Words In Sheet Name
I pulled some code off the internet to “force” users to enable macros by hiding all sheets except a “Welcome Page”. It works great except that it also unhides sheets that are meant to be hidden by when the workbook is opened.

How would I adjust the following code so any sheets/ tabs containing the words “New Project” are not automatically unhidden? Below is the code pertaining to unhiding. I did not paste the code in its entirety as it is fairly long.

Option Explicit

Const WelcomePage = "Macros"

Private Sub ShowAllSheets()
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If Not ws.Name = WelcomePage Then ws.Visible = xlSheetVisible
Next ws

Worksheets(WelcomePage).Visible = xlSheetVeryHidden
End Sub

View Replies!   View Related
Macro To Unhide Then Hide Sheets
I have 16 sheets and 4 additional sheets that will kind of 'Group' these 16sheets. For example: I have 'Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16'.
4 additional Sheets are: 'Group1', 'Group2', 'Group3', 'Group4'.

I need a help with macro so that when this workbook is open all 20 sheets ('Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16') will go into hiding and only 4 additional Sheets ('Group1', 'Group2', 'Group3', 'Group4') will be visible. Now, these 4 sheets will have the command button links to the following sheets:

Sheet 'Group1':'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Hide All'
Sheet 'Group2':'Sheet5', 'Sheet6', 'Sheet7', 'Sheet8', 'Hide All'
Sheet 'Group3':'Sheet9', 'Sheet10', 'Sheet11', 'Sheet12', 'Hide All'
Sheet 'Group4':'Sheet13', 'Sheet14', 'Sheet15', 'Sheet16', 'Hide All'

This being said, when you click on each command buttons, the respective sheets will open up and when click on 'Hide All', all of the open sheets for that *additional sheet' for example sheet 'Group1' will go into hiding again.

View Replies!   View Related
Unhide Specified Multiple Sheets In A Workbook
I would like to unhide specified multiple sheets in a workbook at one go. What I'm doing now is something like this:

Sheets("Source 1").Visible = True
Sheets("Source 2").Visible = True
Sheets("Source 3").Visible = True
Sheets("Source 4").Visible = True

I managed to get the array function when I want to hide the sheets:

Sheets(Array("Source 1", "Source 2", "Source 3", "Source 4")).Select
ActiveWindow.SelectedSheets.Visible = False

but I can't do this when I want to unhide them. I do not want to unhide all sheets in the workbook, only those I specify in the code. How can I use the something like the array code to unhide the sheets.

View Replies!   View Related
Different Password To Unhide The Hidden Sheets.
I have 5 sheets, 3 of them are hidden, how can i put a 3 different password to unhide the hidden sheets.

View Replies!   View Related
VBA To Hide/Unhide Sheets Based On Cell Value
I have a workbook which has roughly 50 sheets. What I'm trying to do is automatically hide/unhide sheets based on the cell values in the first sheet. So in sheet1 cell A1 i would a value of FALSE which would trigger sheets1, 2, & 3 to hide, when that value changes to TRUE then those same sheets would unhide. I need to replicate that for the 10 corresponding sets of sheets, but for each grouping of sheets a different cell in sheet1 would be the trigger, cell A2 = sheets 4 - 10, cell A3 = sheets 11 - 20, etc.

View Replies!   View Related
Automatically Name Sheets Based On Range & Unhide Them
I found this very nice code on this site that changes the names of my sheets based on the first sheet, works great. What I want though is to have the sheets be unhidden when their name changes from the generic name "sheet1" to whatever else we call it. Can this code be manipulated to do that? I want the sheets whose names do not change to stay hidden, the others to unhide.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim lCount As Long

If Target.Cells.Count > 1 Then Exit Sub

On Error Resume Next
If Not Intersect(Target, Range("A2:A11")) Is Nothing Then
For lCount = 2 To 11
If Sheets(lCount).Name <> Me.Name Then
Sheets(lCount).Name = Me.Cells(lCount, "A")
End If
Next lCount
End If

View Replies!   View Related
Hide/unhide Selected Range In All Sheets
I have several workbooks, and the workbooks can include several sheets. Is there's an easy way to create a macro so the user selection to be hidden or unhidden is hidden or unhidden in all sheets in the workbook. E.g. if the user selection is to hide rows 54-189, the macro hides rows 54-189 in all sheets in the workbook.

View Replies!   View Related
Macro To Unhide / Hide Sheets With Combobox Selection
I have a workbook that contains approx 50 sheets and will grow to somewhere in the region of 200.

The majority of sheets, which contain the raw data referenced by the renaining sheets, are hidden. I will occasionally need to update the data in some of those hidden sheets and would like an easy / quick way of unhiding them.

The front page has several comboboxes which select the page needed for the calculation being performed, eg I select Chapter2 in the first combobox, section 4 in the second and page 12 in the 3rd. The output is combined / abbreviated into into a cell eg Ch2-Sec4-P12. That being the name of a sheet I then use INDIRECTs to retrieve the data I want and place it in a spare sheet, that works well.

I'd like to do the same to select the sheet to unhide. I can setup the comboboxes to give the name of the sheet I want to unhide / hide in a cell but then I'm stuck;

How can I use the contents of a cell in place of the sheet name in a macro command such as Sheets("data").Visible = Not (Sheets("data").Visible) ?


View Replies!   View Related
Capture Cells From One Sheet Into Another. Hide/Unhide Sheets
There is 2 parts to my question and I will explain them as best as i can.

1. I have a front sheet and 12 other sheets ( 1 for each month of the year) they all have the same layout. On my front sheet I want to create a button which will capture the sheet onto my front sheet for the current month, whether this means to make 12 buttons to choose which month is displayed on the front sheet or one that detects the date by system time i dont mind.

2. These 12 sheets will ideally be hidden and what I am wanting is again, on the front sheet 12 buttons for each sheet to bring up the corresponding hidden sheet so they can be viewed, and then on each of the 12 sheets another button which will hide them and return the user to the front sheet.

View Replies!   View Related
Cannot Unhide Columns In A Worksheet
The workbook contains 5 worksheets, 4 of which will unhidden just fine; the remaining worksheet ("AnnualBudget" refuses to unhide columns AD to IV. Of course, this is the sheet that contains data in those hidden cells and I cannot get to the data. The sheet is unprotected and all freeze panes removed.

I have copied this sheet to a new sheet in the same workbook:

If I copy only formulas and number formats, the new sheet has all columns unhidden. If I copy formats, I have the same issue with the new worksheet. It’s like these columns are forever locked from view.

FYI I am using Excel 2007 with a 2003 compatability worksheet

A copy of the workbook is attached. Any idea what’s happening?

View Replies!   View Related
Unhide Worksheet With Same Label Name
I have a workbook with quite a few worksheets, along with a summary page. The summary page has a list of names of all the worksheets in the workbook. I am still learning to use Macro. So far, I have created buttons to hide the individual worksheets (each component is on a worksheet by itself). But I can't figure out how to unhide a specific worksheet from a group of worksheets without creating just as many macros calling specific worksheet names. I don't really want to create 100s of little macros. I am sure there is a better way out there.

Here is a concept of the workbook:

On the summary page, the user supposed to click on a label ("T1000" for example), and this will unhide a worksheet with the same name (T1000). There are hundreds of these worksheets, how does it find

This is my idea of how the function supposed to work.

First determine the label text as a string.
Then locate the worksheet with the name equals to the string
then set visibility = true.

View Replies!   View Related
Auto Unhide Worksheet
What I am trying to do is keep a few worksheets hidden until something is entered onto that sheet using a vlookup formula.

My Setup is that we have a daily schedule that is on "sheet 1" when we enter certain items on that sheet, vlookup allows them to appear on "sheet 4". now when an Items appears on "Sheet 4" that is when we would like the sheet to become "unhidden".

Using MVB I found - Sheets("Sheet4").Visible = True

I tried attaching an IF;then statement to it referencing cell (A5) but either it doens't work like that or the code was wrong.

View Replies!   View Related
Unhide Worksheet Menu Bar
i was trying to hide my worksheet menu bar, i was searching the web & i got this macro

View Replies!   View Related
Worksheet CheckBox (Hide Unhide)
I am having difficulties with this one. I have a button on my worksheet (sheet1) that runs a macro.

View Replies!   View Related
PASSWORD/Code To Unhide Rows And Worksheet Tab
I have a protected sheet so specific people can only see certain things. I would like code to Unhide a tab called "Estimating" and unhide Rows 2 through 9 and rows 24, 26 and 27.


View Replies!   View Related
Variable Sheets, Variable Input
I run a machine that scans parts and is able to output the scans into Excel. Each part scanned creates a new sheet, and the number of sheets is variable as the number of parts scanned depends on the size of the lot. Also, since each part scanned is going to have a different number of features, the information being output on each sheet will be variable as well.

I am trying to figure out how to write a macro that will find the first feature, find the values for that feature I am looking for, output a max and min into a final sheet, and repeat for each feature, and for each sheet. I hope this is clear.

View Replies!   View Related
VBA Script To Hide And Unhide Specific Columns Within A Worksheet
Need the VBA script to hide/unhide specific columns within a worksheet.

View Replies!   View Related
Vba And Autonaming Variable Sheets
I am trying to automate the process of updating a worksheet that draws values from another sheet, updates all formulas based on this value, then copies the sheet to another workbook, valuelocks it and names it according to the value I used to update the sheet in the original workbook.

I've tried several things, including recording a macro, but this seems to run into issues after I create the new sheet on the second iteration.

Basically, I have a list of numbers, which drive the formulas in the first workbook, that I want to use to create a number of sheets in a new workbook named after the numbers.

The first macro I recorded went through all the steps of updating the data, cutting and pasting to the new workbook, valuelocking the data in the sheet and entering the worksheets VBA module and using a bit of code I found in one of the threads that will automatically name the sheet according to a specific cell on the sheet.

The major problem seems to be that when it gets to this point it tries to reference the first sheet I named, not the one I want to name.

View Replies!   View Related
Opening Variable Sheets With Vba
I am designing a userform with a set of option buttons and a few corresponding combo boxes, which allow to view a ready-made report in excel. My problem is, how do I get the code to open the report I want? So far, the code looks like this:

Private Sub cmdshowreport_click()
frmmeipdashboard.Hide
If optcomm = True Then
If opttrd = True Then
ActiveWorkbook. Sheets(cbotrd).Activate
End Sub

cmd is the command button that I press when I want to view the report, opt is the radio butons, cbo the combo boxes. What is going on is you enable one button, then another, then choose a value from the combo box, and what I want is for the value in the combo box to be the value the code uses when choosing which sheet to activate.

View Replies!   View Related
Sum Across Sheets With Variable Cell Reference
i'm trying to use the sum across sheet function - i.e. - =SUM(Start:End!B1)
Whereby "Start" and "End" are the two relevant sheet names and "B1" is the cell I want added up.

However, can I make B1 variable, whereby there is a cell, say in A1, that contains the string "B1", so that I can make it sum C1 or B20 or anything on the other sheets?

View Replies!   View Related
Count Sheets For Variable Array
I have a workbook with several sheets, some are permanent and then there are certain sheets whose quantity varies depending on how many sub-contractors we use on a given job. The workbook starts as a template and there are 10 sub-contractor sheets to start with, if we only have 5 subs then the last five sub sheets are deleted. The sheets start out with the tabs named "Sub 1" "Sub 2" etc. but after the book is set up the sheet tab names are changed to the sub name. I've included some partial code below, with the goal being to run code on the sub-contractor sheets no matter the tab name or how many there are.

So the workbook has 5 "non-sub" related sheets, I was thinking that if I could do a sheet count -5, and get that number into the array it would accomplish what I want to do. And if someone added a new sheet it would still be processed with the others.

Public Sub SubConEstNum()
On Error Resume Next
Dim S As Worksheet, names As Variant, i As Integer
names = Array(6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
For i = LBound(names) To UBound(names)
Set S = Sheets(names(i))
S.Range("T3").Value = S.Range("S3").Value
S.Range("T1").Value = S.Range("S1").Value
S.Range("T2").Value = Sheets("Bill").Range("K1").Value

Next i
End Sub

View Replies!   View Related
Cannot Edit A Macro In A Hidden Workbook. Unhide The Workbook Using The Unhide Command?
using Excel 2007/Vista

when I go a new edit a macro, I get the message

"Cannot edit a macro in a hidden workbook. Unhide the workbook using the unhide command"

Where do I find the relevant "unhide command"?

View Replies!   View Related
Sumif Several Sheets And Using A Variable Criteria Range
Currently I’m using several formulas to acquire my result; I know it can be abbreviated to a more compact formula.

I have four Sheets. One summary sheet and SheetA, SheetB and SheetC containing Data.
All sheets are documented in an equal format, e.g.: in column A: Date, column B: Code (A, B, C or D) and Column C: values.

Criteria are located in the summary sheet e.g.: A2: 1-1-2008 A3: 2-1-2008 and B2: A, B3:B
The data sheets are listed in H1:H3.

The results should cover a sum of col C over the sheets by using the critiria listed in summary sheet.

The criteria range is variable; sometimes I only want to use one criteria and sometimes more.

View Replies!   View Related
Reference Sheets With Variable Sheet Names
I have a workbook with multiple sheets. There is an overview sheet and then 14 sheets allowing for 14 days worth of schedule information. There are then 3 sheets following the 14 days to total some information. The workbook users frequently change the sheet names of the 14 sheets to reflect days of the week.

I'm creating a copy for distribution that takes the active workbook and copies the values to a new workbook. There are columns that I would like to delete from the 14 day sheets and that would be easy enough if the sheet names were never changed.

The code I'm using currently follows (my thanks to Turtle 44 for helping on that section)

Sub Copy_Visible_Sheets()
Dim arr() As String
Dim i As Integer
Dim WB As Workbook
Dim WS As Worksheet
Set WB = ActiveWorkbook
Application. ScreenUpdating = False

'Make sure template is saved as .xls
If Not ThisWorkbook.Saved Then
MsgBox "Please save this workbook before generating a Client Copy."
Else

View Replies!   View Related
Macro: Copy And Pasting Variable Ranges On Seperate Sheets
I have attached the xls. I have an input sheet with 3 columns to enter data. Each column is linked to a separate worksheet with a formula (Carrys 1000 rows long). I need to be able to pull the populated data from those 3 worksheets and paste into 1 column continuously on another worksheet so all data is on top of another without any spaces.

I made an if statement so that if there isn't data pulling from the input sheet the a blank cell is left to indicate the last row to copy data from and paste on the final sheet.

View Replies!   View Related
Worksheet Name To Variable
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.


View Replies!   View Related
Use Of Worksheet Variable
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

View Replies!   View Related
Using Variable Cell Value As Worksheet Name
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 Replies!   View Related
Replacing Worksheet;with Variable Name
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.....................

View Replies!   View Related
Set Variable To Worksheet In Another Workbook
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

View Replies!   View Related
Adding A Variable Rows Worksheet
Due DateInvDocument numberDebitCreditBalance
17/09/08L01607610714211201825.44
13/10/08L01637010284345841020
13/10/08L01637131713874501020
13/10/08L016373852202473461020
13/10/08L0163748.5222E+111020
15/10/08L01641811103746832316

The above is a part of a w/sheet. Rather than determining the last row in the debit column and credit column (to know which is greater) and use a loop to put a formula in each of the cells in the balance column (adding the debit and credit), I know I can use the with ... statement to input the formula at a go , but cannot figure it out.

Sub sumtotal()
With Sheets(1)
With .Range("G14", Range("G" & Rows.Count).End(xlUp))
.Clear
.Formula = "=sum(E14+F14)"
On Error Resume Next

End With
End With
End Sub

View Replies!   View Related
Variable To Refer To A Worksheet.CodeName
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

View Replies!   View Related
Linking Data When One Variable Changes In Src Worksheet
I'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)

View Replies!   View Related
A Variable Which Is Used By Several Subs In A Worksheet But Is Not Global?
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 Replies!   View Related
Converting A String To A Worksheet Variable
I have a userform that reads in a list of names into a combobox. When you choose a name from the combobox it then shows the that persons details from a sheet containing all the names.

I have some text boxes that allows the data to be modified and returns it to the sheet which works fine. What i also want it to do it put the data from the form into a sheet for that individual. For example the name of Ford would go into the Ford worksheet.

How do i read in the value from the combobox on the worksheet and use it as a variable to use in my code. For example

Sheets(Value from the combobox).Cells(NoOfRows, 3).Value = Me.TextBoxChangePosistion.Value

*NoOfROws is the variable to find a blank row

View Replies!   View Related
Assign Variable To Worksheet Formulas
Split from Copy, Cut, Fill Relative Or Absolute Formulas

Originally Posted by AAE
Welcome to Ozgrid.

Assume the following:
Cell A1: is the input cell
Cell B1: contains a number
Cell C1: formula = A1*B1 or $A$1*$B$1

Your formulas must contains absolute cell references, other wise Excel will attempt to update them to the new cell references when copying/pasting.
If you "cut", rather than copy, Excel will maintain the original cell references in the formula.

Both of these questions are well within the experience level of a user with above average skill level as your profile shows. Please lower it (before one of the Admins does it for you) to a more realistic level.

See this training page on how to toggle between relative/absolute cell references: [url]

Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.

Lets use my calculator for instance. Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.

View Replies!   View Related
Run-Time Error Setting Worksheet Variable
The user is asked for one piece of information "Enter the ID Number.

What the macro should do then is go to Wks1 find the ID Number and change some cells as a result. This bit works.

Set Wks2 = Worksheets(strWks)

7 rows from the bottom.

My intention was to capture the the name of another worksheet which is held on the same row as the ID Number on Wks1 and call it strWks.

Then further down the macro set the value of Wks2 to that of strWks so that the macro will then go to that sheet and remove data from the row with the same ID Number.

I get a Time Run Error 9.

It just seems to be the bit at the bottom where I am trying to identify Wks2 using strWks.

Sub Macro01C_Auto_Resign()
Dim Wks1 As Worksheet, Wks2 As Worksheet
Dim strFind As String, rngFound As Range
Dim lngRow As Long, rngUnion As Range, strWks As String ............................

View Replies!   View Related
Check Worksheet Exist, If Not Add Variable Sheet Name
I need to allow users to add up to seven new worksheets as required and the new sheets must be named Image1, Image2, etc. I do not want to add seven worksheets at once, only one at a time as the need arises and no new ws can be allowed after Image7 ws is added.

I have a macro to test for the existence of ws Image1, but need help on further development to add and test for the extence of the remaining sheets.

Dim ws As Worksheet
Dim bTest As Boolean
For Each ws In Worksheets
If ws.Name = "Image1" Then
bTest = True
Exit For
End If
Next ws

If bTest = True Then
'Need some code here, not sure what I need

View Replies!   View Related
Worksheet Change Event :: Trying To Set The IntRMACol Variable
This is the code on the Worsheet Change event.


Private Sub Worksheet_Change(ByVal Target As Range)

'Define the RMAs range and count the RMA ships. Do the rest of the math based on this calculation.
Dim intRMACol As Integer, EndRow As Integer, intRMAShips As Integer, LastCol As Integer
Dim rngRMAs As Range, rngHome As Range, sht As Worksheet
Set sht = ActiveWorkbook.ActiveSheet
Set rngHome = Cells(1, 1)
rngHome.Activate
EndRow = Range("A" & Rows.Count).End(xlUp).Row
intRMACol = Cells.Find(What:="RMA#", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
Set rngRMAs = rngHome.Offset(1, intRMACol - 1).Resize(EndRow, 1)
rngRMAs.Name = "RMAs"
rngHome.Offset(1, LastCol + 1).Formula = "=COUNTA(RMAs)"
intRMAShips = rngHome.Offset(1, LastCol + 1).Value
rngHome.Offset(1, LastCol).Formula = "=SUM((" & EndRow & " - 1) - " & intRMAShips & ")"
rngHome.Offset(1, LastCol + 2).FormulaR1C1 = "=SUM(RC[-2] : RC[-1])"
End Sub

I keep getting stuck when trying to set the intRMACol variable. I have used the code all over the place, but only in Modules, never on a worksheet event. EndRow, the variable right above, sets correctly so I don't get it.

Also, do I have to define the Target variable? Am I going about this the right way? I basically want three formulas to calculate when any change is made on the worksheet.

View Replies!   View Related
Error Settting Variable To Range On Another Worksheet
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

View Replies!   View Related
Select Variable Range In The Middle Of Worksheet Down To The Next Blank Row
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 Replies!   View Related
Subcript Out Of Range Error Setting Worksheet Variable
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':..................


View Replies!   View Related
Combine Sheets To One Worksheet
I have a workbook with a Sheet named Main Data. The sheet named Main Data, I want to leave alone. The remaining 8 or 10 worksheets (the number of sheets can vary), I would like to take the range A2 to the last row in AH that has contents in it and paste those ranges from each worksheet into one new worksheet so that they do not overlap. I don’t know how to do that so the range I am using in the code below is A2:AH60000 consequently it will not copy the entire range from each worksheet because there are not enough rows in the destination sheet.

View Replies!   View Related
Move/Navigate Through Many Sheets/Worksheet
I have a workbook that contains 30-40 worksheets. Is there a keyboard shortcut or an easier way to move through the worksheets besides using the mouse and clicking the tabs at the bottom?

View Replies!   View Related
Copy Matching Data Between 2 Sheets To 3rd Worksheet
I have found the following code on this forum that has really worked well. This code matches the first two columns of two worksheets and inputs them into a third worksheet. But I need to be able to copy up to five more columns, a total of seven columns and have it input into the third worksheet. I am not sure what it means "For Each c In Sht1Rng". look at the code and advise me of what I need to do.

Sub FindMatches()
Dim Sht1Rng As Range
Dim Sht2Rng As Range
Set Sht1Rng = Worksheets("Data1").Range("A1", Worksheets("Data1").Range("A65536").End(xlUp))
Set Sht2Rng = Worksheets("Data2").Range("A1", Worksheets("Data2").Range("A65536").End(xlUp))
For Each c In Sht1Rng
Set d = Sht2Rng. Find(c.Value, LookIn:=xlValues)
If Not d Is Nothing Then
Worksheets("Results").Range("A65536").End(xlUp).Offset(1, 0).Value = c.Value
Worksheets("Results").Range("A65536").End(xlUp).Offset(0, 1).Value = c.Offset(0, 1).Value
Set d = Nothing
End If
Next c


End Sub

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved