I have a monitoring database and I want to create a 'source' sheet in sheet 1 whereby when I enter names into a certain column they rename different tabs/sheets in the worksheet. for example, the name 'Brown' inputted in cell 'A2' would rename sheet 2, Black in A3 would rename sheet 3 etc.
View 2 RepliesIs 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.
ATTACHMENT: Order Form.xlsx
I am creating an Order form for my job in Excel. On this form, it displays the Top 100 items for sale. All the basic functionality of the form is working fine, my real issue is with the order summary tab.
On this tab, I would like for any Items with a Quantity on the first Sheet to transfer to a summary on the Second sheet.
So far, I am able to get the data onto the second spreadsheet. My final question is, can make the items on the summary sheet go to the top available row? Basically if I have items in row 3, row 7 and row 9 - I would like for the items to automatically fill in rows 3,4,5 respectively. (on the second worksheet)
I have seen people make this happen (on this board even) but for some reason, when I try using those formulas I do not get the results I need. I have attached the file to this post.
On the Tab entitled "Adv Piano" this the product list. On that tab, customers can put a Quantity in column G for the items they want to order and that item's details, price, and quantity will automatically appear on the 2nd tab, "Order Details" in columns I - L in the corresponding row (this is already done)
I need the information in the columns I - L on the "Order Details" sheet to appear in columns C - G on the same sheet, in the same order but to fill in towards the top with the corresponding quantities, item details and price remaining in the same row.
Order Form.xlsx
I have attached a sample workbook which shows what I am trying to extract from sheet "Service Reminders 2014". In worksheet "Results" cells b11:e11 I am trying to extract the amount of vehicles with within age ranges provided that have a magic number attached which is pretty easy everything >0 is a proper magic number and also the amount of vehicles in each age range that have "Booked" associated within the range of "Service Reminders" Y2:AH5000, The formula works fine until I add the final criteria and then it give me a #VALUE!
View 14 Replies View RelatedIs it possible to name a worksheet tab from a cell reference on a worksheet?
View 6 Replies View RelatedHow can this be done using VB? The format of the date I'm looking for is: 16-Nov-08
View 2 Replies View RelatedI was trying to rename the worksheet. using a cell value,A1. For example , if the cell value is Earning, the worksheet will be named as Earning.
However I would like to include the location before the name, Earning.
For example, in the cell value,A2 TK. I would like to rename the worksheet as TK_Earnings.
So the name of the sheet will be CellvalueA1_CellvalueA2.
I have a macro to loop through the files in a folder. The macro will then open up the files and copy over to a new workbook.
As I want to rename the worksheets created in the new workbook as part of the file name. Any idea how to extract the file name out and stored in a string?
Currently it will be named after the Cell A1 in the opened file. However I would like to rename as below.
Example, the file name : 030309_Mary Sales.xls, Worksheet Name to Create : Mary Sales.
File name : 030309_John King, Worksheet Name : John King
(in the code, 030309 taken from my menu sheet,D4 is the keyword , to search in files containing D4, where D3 is the directory to look in)
Sub RunCodeOnAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
I'm a little scared to post what with all the warnings and instructions but here goes.
Each month I have to create a new workbook for entering data daily, the tabs are named
"April 1" "April 2" "April 3" and so forth.
I am looking for a shortcut to adding each sheet manually and renaming each tab after adding in 30-31 sheets.
I have found code for adding and naming in numeric order, weekday, monthly, but need something to add both the name of the month and the day of the month.
Upon opening after "Enable" is selected the workbook attempts to locate several nonexistent pieces of data, either internet based files or network based files. Requested data appears to be about 11 years old and would not be applicable it located.
Edit Links shows the location of the requested files, i.e., E:filename but does not show the location within the document that causes this request. A search for "E:" does not locate text in any worksheets.
The question is how to delete or turn off this problem which slows opening, saving, and recalculation of a large multiple worksheet workbook.
Trying to get a problem solved to have a % discount & 'flat' $ discount apply to cell(s) referenced. Product A, B & C are available in 3 different materials. Objective is to allow each material's Max Discount column to apply discount(s) referenced in cell J3 & K3, but only in the order of applying the percentage discount BEFORE the 'flat' $ discount.
Formula in cell C3 works IF the cell "Range to apply discount" I3 = "B3". Works just fine if I allow only one cell to be displayed in I3, but ideally, I'd want this to allow the said discount(s) in cell J3 & K3 to apply to any cells mentioned (for instance, for Product A Material 1, Product B Material 2).
I had tried a drop-down menu using Data Validation, which in my actual project allows me to select ONE cell at a time (out of my list of options), which is okay, but it would be extra useful to have a checkbox option to select which ones to apply the same discount to, instead of creating a massive embedded IF function to have each product in each material find whether it's cell is referenced in 20 different places (if I just copy the already functioning single cell reference tool).
The scale in which I'd like to use this in would be to apply specific discounts for one product, but a different discount for another product or material, and allow the input cell I3 to include a checkbox drop-down option (like the filter/sort), but not remove the data in the table (Range A2:G5 in this example), so I can hide any unnecessary columns and print, showing certain products & materials having a 10% discount, while others having a 20% discount, with an additional $5 off, etc.
[URL] ........
I have a workbook with 4 worksheet that store different type of data. It also has a userform that load at start of the application which is to search the data in the workbook. The userform has a combobox where the names of the sheets are stored. when the user selects say Sheet2 in the combobox, it enables the relevant textboxes on the userform and activates the worksheet at the change event. The userform has a search button that searches all the worksheets based on the text entered in a textbox.
The problem: how to search based on 1 textbox. What I want is: say for e.g the end-user selects sheet2 from the combobox, this intern enables 4 textboxes (Name, DOB, Nationality, ID #) on the userform. The end-user should have the liberty to enter data in 1 and/or any of the textboxes. The search should be performed, that if data is only in 1 of any of textboxes then give all rows that fit that criteria and display in a temp worksheet. if say the name and dob is filled by the user than what matches both should be displayed in a temp worksheet. if say dob, name and ID# given so the search button should narrow down to fit all 3 criteria and then display result in temp worksheet. As if mentioned data can be entered in either just 1 or any or all textboxes.
E.g. the worksheet is (Columns are Name, Nationality, DOB, ID#)
row 1 = name: Steven Martin, DOB: 27-may-1993, Nationality: Trinidad & Tobago, ID #: 1234567
row 2 = name: Gary Richards, DOB: 2-FEB-1993, Nationality: British, ID #: 456789
row 3 = name: David Cohen, DOB: 27-May 1993, Nationality: American, ID #: 98765
row 4 = name: Roberto McDonalds, DOB 21-Jul-1962, Nationality: British, ID # 654321
row 5= name: Gary Richards, DOB: 01-Dec-1978, Nationality: Australian, ID # 1234567
Now if the user enters only name as "Gary Richards" and search then row 2 and 5 should be displayed in a temp worksheet. if user enters name Roberto McDonald and ID# 1234567 then it should not display anything. if user enters DOB 27-may-1993 and nationality British and ID # 1234567 then as well shouldn't display anything and should a msgbox "no data found".
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 need to reference the projected and actual expenses from the total on worksheet 'expenses' So i did so, however, if I change the drop down on the expenses worksheet to only display housing data, then the projected and actual expenses on my budget worksheet changes as well to the new data portrayed on the expenses worksheet.
I need to reference the cell, without it changing when I change the category display, but I need the cell to change accordingly if I enter new data in the overall tablet on the expenses worksheet.
Or do I need to create a seperate worksheet that has the data in and reference my cells on my budget worksheet to that new worksheet?
I've been tackling this data capture/paste issue for a week or so. I found the string below which does provide a good foundation for my challenge. But, my basic level of understanding macros limits my modifications to meet my needs.
[URL] ......
I have 20 worksheets in my master file corresponding to Excel files individual associates will update weekly. After the associates have updated their individual files for the week, I want to capture the data entered and paste values into a master file containing a worksheet for each associate (sharing the same name as the individual associate file). All of these files are housed on team SharePoint sites.
I need a macro to perform several steps after clicking a "Run Update" macro button in the master file:
Open individual associate fileIn master file, search for each Initiative listed in column B (starting cell B3) in the individual associate file (in column B starting at cell B11)If Initiative is found in individual associate file, copy adjacent data in columns D:J for the respective rowIn master file, paste values to the corresponding Initiative row for the corresponding week's worth of dataIf Initiative is not found in the individual associate file, move to the next Initiative listed in the master fileRepeat these steps for each individual associate file
Linking would be the easiest way to accomplish this if I wanted to have a multitude of weekly individual files for the associates. However, I'd rather each associate have one file for them to update (basically overwriting their previous week's entries).
I need to ensure the paste values corresponds to the appropriate day of the week. In simpler terms, if the date in the individual associate file in cell D9 reads Oct 1, 2012, the data captured from that row needs to be pasted to the corresponding row/column in the master file that reads the same date.
I need help with a macro for copying and pasting of cells. I believe this should not be a problem for the Excel VBA experts, but for someone who can only record macro, I'm really at a loss.
Attached is a sample file, where sheet 'Source' is an example of the sheet from which data are to be copied. The other sheet, sheet 'Final' is an example of the final format that I need. The reason I'm doing this is I'm planning to upload my data into Access and so I need to convert them into a list format.
List of target columns in sheet 'Final' and source cells in sheet 'Source':
Column A: Biz ID - not sure if I really need this, by right it should be listed automatically once I paste the data
Column B: B2 of 'Source'
Column C: B2 of 'Source'
Column D: B1 of 'Source'
Column E: row 6, relevant column
column F: column K
column G: row 5, relevant column
column H: the specific amount
So basically I'm creating an entry for every amount in the table.
=SUM(INDIRECT("'Sheet2'!$C$1:$C$5"),INDIRECT("'Sheet3'!$C$1:$C$5"))
These do not:
=SUM(INDIRECT({"'Sheet2'!$C$1:$C$5";"'Sheet3'!$C$1:$C$5"}))
=SUM(INDIRECT("'"&Sheets&"'!$C$1:$C$5"))
Where Sheets refers to =Sheet1!$A$1:$A$2
[A1=]Sheet1, [A2=]Sheet2
For the last two ONLY Sheet2 is summed, not sheet3
I have an excel 2007 work book with multiple worksheets that updates daily. I want to bring all the data from each of the work sheets into a new worksheet so that i can create a pivot table.
All the data in the worksheets have the same headings and correspond to the days of the year.
I think i need to use some sort of range look up?
Is there a way to display multiple cells in a separate worksheet that are a part of multiple worksheets? Please see below.
Worksheet 1 has list of email addresses in column A
Worksheet 2 has list of email addresses in column A
How to have Worksheet 3 display email addresses in column A that were on Worksheet 1 and Worksheet 2? Considering all duplicates are removed from each worksheet.
Worksheet 1 (column A)
red
blue
green
yellow
[Code]....
Need to have Worksheet 3 display as: (column A)
red
blue
green
The code below will put "Some text" into column B when data is pasted into column D. This only works when copying data into one cell. If I copy into multiple cells of column D then the code does not run at all.
[Code] ......
Background: I am HR manager for a construction company & keeper of the call-in list of personnel who are looking for work. I have a simple sheet that has columns:
Date Name Craft Experience ...more info...
If each call-in had only one craft, wouldn't have a problem. Those who are multicrafted ar listed e.g. "EL, MW, BM" In the column C. A caller two days later may be listed as "MW, BM, EL" We input the data as they say it since that is usually their order of expertise. (Yes, I know that it should have been set up with each craft having its own column, but I inherited the sheet & it has 4000+ entries)
I wrote a couple of small macros & assigned buttons on the sheet to allow the users to sort the sheet by date, or name, or craft. My customers (project managers) have requested to be able to sort by craft but have all the folks with any specific craft listed together.
Example (Excel 2003): ..
I'm trying to run a macro anytime cells G2, J2, M2, O2, P2, S2, V2, Y2, AB2, AE2, AH2, AK2, AN2, AQ2 change.
Right now I have the below code which is working well, but I only have it set for G2. Do you know what the notation is to make the target range multiple cells?
Also, I use the xlDown command in my sorting code, but later on it reverts back to "A5:AT60"... is there any way I can remove these specific cell references? I want to avoid having to re-write the macro every time my selection shrinks or expands.
Code:
Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("G2")) Is Nothing Then
Rows("5:5").Select
[Code]....
I have created a software on a userform and have copied contents of this userform to worksheet cells. However when I run it for the first time everything works and all contents goes in C4 - C8. However when I run it for the second time the contents in C4 - C8 deletes itself and new data appear i.e. the cells are overwritten.
In cell C3 i have the date and I have managed to put this date in the next empty column everytime the software is run. I do not understand why the dates can go to the next empty column without it being over written but cells C4 - C8 cant!
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 want to do a loop where you can copy say A3 worksheet 1 then add another sheet naming the work sheet "A3" then copying A3 worksheet 1 to A1 "A3". After that looping to A4 to a new work sheet naming the work sheet "A4"copying the value to A1 "A4", etc...
Is there a simply way of doing this loop? I can probably fit my other coding into the structure.
I'm making a Excel list for trading cards (MTG) and I've divided it into different worksheets in order to sort it by the color of the cards (not really important I guess).
The issue I have is that I want it to copy certain columns (in this case C3:C1000, F3:F1000, G3:1000 ) from almost all of the worksheets into a new worksheet where it should paste them into column A, B and C.
In two of the worksheets it's also different (C3:C1000 , G3:G:1000, H3:H1000).
I've seen many where you copy one range from multiple worksheets, and that would've been okay I guess if not those two worksheets were different. I'd prefer not to change them, and I'd also prefer if I could've gotten just the information I needed also. Is this possible to do?
The following code won't let me copy from the first workbook. I get a run time 1004 error stating "That command cannot be used on multiple selections".
I would rather not have to copy this by column for each of the 4 workbooks
[Code].....
I was wondering if it's possible to use a countif so that the condition can be a range of values also? For example, A1 = 14.12.08, A2 = 15.12.08 and A3 = 16.12.08
So I can have a countif that looks like: Countif(B1:B300, A1:A3). It doesn't work when I try it, but was wondering if there's a way to achieve the same result? So if B1:B300 contained any of the values in A1:A3 it would count the amount of times they appear in the B range.
I 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.