I need to take the contents of a cell and use it as a variable in a mcaro. For example: If the Value of Cell B1 is "Outback", the Macro will select the Outback sheet, but if B1 had a value of "Damon's", than I would want the macro to select the Damon's sheet.
View 9 RepliesI am fairly new to macros and have trouble with VBA. I have a file with multiple worksheets. Each worksheet contains the name of a specific location in cell A8. I want this name in cell A8 to be the name on the worksheet tab for each worksheet in my file but do not know how to accomplish this. Is that even possible?
View 3 Replies View Relatedcoding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.
As a simplified example:
Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!
So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.
I was thinking of using something like this:
Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Address="'Worksheet1'!A1" Then
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Else
Rows("20:30").EntireRow.Hidden = False
End If
End If
I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.
I 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
I'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.
This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.
I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.
I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.
I have a 'Summary' worksheet which is shown as:
August
September
October
Example 1
1
4
5
The table, which works correctly, looks in another worksheet 'Report' for 'Example 1' within the date range of August. This uses COUNTIFS.
Ideally, what I'm wanting to do is have an auto-filter on each of the cells - 'Example 1', 'Example 2', so if a user wants to see what rows on the 'Report' sheet, falls into this criteria, it auto filters the other worksheet, and takes them too it.
The action would be - Click on the cell of 'Example 1', the macro auto-filters based on the cells value in column F of the 'Report' sheet, and takes the user there. The macro would have to take into account the date filter as well, which uses cells A1,B1 for August, and C1,D1, for September.
I have three work sheets.
Worksheet A contains the Names that I want to find in Worksheet B and C. Trouble is, the format of the names are different in each Worksheet.
For example:
Worksheet A, Column 'Name':
Kyle Rowlie
Worksheet B, Column 'Name':
Kyle Rowlie, Head of Excel
Worksheet A, Column 'Name':
Rowlie, Kyle
What I need to do is create two columns in Worksheet A that identifies Kyle Rowlie is duplicated in Worksheet B and C.
Is it possible to name a worksheet by entering the name in a cell from another worksheet in the same workbook? I am building a macro and am missing this crucial step.
I would like it so that someone else could come along and change the name in the cell and the name of the workbook would change too.
I have renamed worksheets according to a cell value in the same worksheet, but now I need to rename the worksheet according to a cell value in another worksheet.
Here is the code I tried:
Code:
Private Sub Worksheet_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sheets("Set-Up Page").Range("B2").Value "" Then
'rename the worksheet to the contents of cell B2
Sh.Name = Sheets("Set-up Page").Range("B2").Value
End If
End Sub
I have pasted that code into the worksheet that needs to be renamed, but it doesn't work. I have also tried:
Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = Sheets("Set-up Page").Range("B2") Then Sh.Name = Target
End Sub
I like the first one because it checks for a blank value. There will be blank values depending on how many worksheets each teacher needs, so it will just rename the ones that have values filled in. Each worksheet will have the code referencing to a different cell on the Set-Up Page worksheet.
I have an Excel 2010 spreadsheet consisting of many worksheets (20 or so). Each of these worksheets contain detail level data regarding different projects. One of the columns in these worksheets is the 'Status' column (column F). There is conditional formatting on this column where if the text is 'G' then change background to a green color, 'Y'=yellow, 'R'=Red and 'U'=Grey.
The first worksheet is a summary sheet that I would like to pull information from each of the detail worksheet's columns B, D, E, G and H if the status column (Column F) is 'R' or 'Y'.
The number of rows in the detail worksheet can change each week (as few as 0 and as many as 100)
I have the following code in another workbook that is used to populate a cell on the same sheet based on input to cells in column 'A'.
Is it possible to modify this for the attached workbook to select a cell with data (numbers) on the Input Data sheet in column 'E', add text to the beginning, ('CG' in this case), and paste the result to the Import Template in the corresponding cell of column 'A'? I currently have a formula copied to dozens of cells in 'A' but since the number of rows for the Input Template is variable, there are usually cells in 'A' that contain CG but no corresponding data in the rest of the row.
I am wanting to have certain cells of data from one worksheet appear on another worksheet once the cells on the first sheet have data entered in them.
I find that I can do a simple formula to copy the cell but I want the cell to remain blank until data is added in the first sheet (it will be a mix of text and numerical entries).
I have a worksheet size roughly 100 * 400 cell. there is roughly 100 cells filled with RED color and with different value.
When i used FIND ALL format - RED, I can locate the cell location and the cell value. And I wish to copy these 2 info onto a new sheet so that on the new sheet, I can create a hyperlink and easily access to the location of the RED cell.
However, in FIND and REPLACE, i have a list of results on what I want. But i cannot copied the information out. So is there anyway to copy it out or to achieve what i want?
For eg.
RED value
cell
50
A3
87
D71
251
AQ5
and so on.
I've done the following:
Sub BrownBH()
If Range("Brown!B4:B31") = X Then
Range("C4").Value = [#A]
Else
Range("C4").Value = NT
End If
End Sub
However, this doesn't even work.
When somebody enters an X in a specific cell on one worksheet, it's supposed to change the value to A of a specific cell in a different worksheet. Sounds simple enough...but...
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?
I need to copy a cell content to a Comment into another worksheet in
the same cell ref.
sheet1
A1 = apple
A2 = orange
Sheet2 - target
A1 = comment (apple)
A2 = comment (orange)
I have 2 workbooks, A (source) and B (destination).
Workbook A has 1 sheet named 'master' and has 10 columns a:j
I would like to copy a range of data in worksheet 'master' for entire 10 columns while the cell value (moving down worksheet) of the next cell in column A is greeater than the current cell. eg. if cell values of range a1:a6 = 1 then cell value a7 =2, then copy range a1:j6 to workbook B in sheet 'r1'.
Then I would like to go back to Workbook A sheet 'master' and begin process again starting from where it left off at a7.
Display Alert On Closing If Cell Not Filled In
How do I alter this code so that it acts like an INDIRECT function so will pick up a cell reference in another cell?
I can't just use A1 as I want this to change dynamically.
I have a spreadsheet called "temp.xls" that will change file name often.
This "temp.xls" has text that needs to be copyed to a new spreadsheet named "punchlist.xls" that does not change names. I know how to acheive this using record macro but I need the text to find the next row in the punchlist.xls file. Do all the macros run on the 1st file or do both files need macros?
I have a macro in the Worksheet module which begins as follows, however I would like the cursor to be in cell "E1" when the sheet is activated.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Address "$E$1" Or .Cells.Count > 1 Then Exit Sub
I'm sure that there is a way of achieving that but just can't find it?
Is it possible to link a cell e.g. A10 that has a time value in it (there is a formula in this cell that puts in the time value) to another cell in a range of cells e.g. B1:B1000 that has the same time value?
I know someone out there knows how to do this.
I'm trying to link data that is located on one spreadsheet and equal that to other information located in a different worksheet within the same file. See HPRP A Vital stats in E,F,G 12. I want to have the inforamtion from the spreadsheet Staff - Client Pre Screening in cell D,E,F,G 12. I tried the = function, but it is not working.
View 3 Replies View RelatedWhat I have is a sheet that checks for the current date when it opens, if the day is a Thursday (payday) then it does stuff. What I am having an issue with is setting a cell equal to another cell by formula, but rather than using the cell reference itself, sheets.cell, I have that cell in a variable Var6. When I run the code I am getting an application or object defined error.
The variable set is as follows:
Var6 = "Budget!AI26"
Formula ....
How to do VBA even for a basic one. macro that allows me to go to an specific cell from another sheet/tab when I click a given cell in the current sheet.
View 7 Replies View RelatedI would like a cell at the top of my worksheet that reflects the name of the worksheet in the workbook.
View 5 Replies View RelatedI can not find a way to have a cell in a worksheet to be the name of the worksheet. I am sure there must be a simple way to do this.
View 4 Replies View RelatedI have created a Button on my main worksheet tab = Sheet1 once clicked it will create a new sheet within the workbook using my default worksheet tab named "00" (Sheet2) and cell O6 within the sheet is named "00". Hence, once i click the button i will create another tab 001 and click it again create 002 and so on so forth. The only thing I cant figure out is how can I link cell "O6" within each of the newly created sheets to the sheet name. Example I create Sheet1= 001 I want cell O6=001 within the sheet. Create Sheet2= 002 I want cell O6=002 within the sheet and so on. Below is the Macro I have so far
Sub Create_New_NCR()
Sheets("00").Copy After:=Sheets(1)
Sheets("00 (2)").Name = "00" & Sheets.Count - 2
ActiveSheet.Range("O6").Select
End Sub
What do I need to add to the Macro so that once the tab is created cell O6 get the tab name.
I have a worksheet with additional worksheet name listed in Column G, starting in G3.
I want to run this formula on each sheet, using the value in Column G as the WORKSHEETNAME.
=COUNTIFS(A7:A1000,">12/31/2010",A7:A1000,"<1/1/2012",F7:F1000,"Excluded")
Faulty example of what I'm trying to do:
=COUNTIFS('WORKSHEETNAME'!A7:A1000,">12/31/2010",''WORKSHEETNAME'!A7:A1000,"<1/1/2012",''WORKSHEETNAME'!F7:F1000,"Excluded")
I've tried INDIRECT, but I cant seem to make it work.
there is a named range on the worksheet in cell A1- "GiGS"
I want to type "GIGS" in 1st cell and then in 2nd one to write formula "=A1*5". So excel need to understand that in A1 there is a name and not just a string
I have a workbook containing 25 worksheets. i would like for each worksheet to be named based on what is in cell S6. I might add that S6 has a vlookup in it that pulls in its value..
View 14 Replies View Related