I have a list of equipment and bits that go with each kit and depending on other kits which they are installed with.
I'd like to automate the list.
Attached an example: I'd like excel to look for the first piece of equipment over their respective sheets and list the parts needed for the chosen equipment.
I have 2 worksheet A for data capture & is looking for a VBA to create a report in worksheet B using worksheet A. How the VBA should look like?
Worksheet A
Container Type
Type of Seafood
Weight (kg)
Ave price $/kg
Price ($)
Container A
Fish
1
5
5
[Code] ........
Worksheet B
-contains X number of table, one for each container in worksheet A.
-there should be a sub total at the end of each table.
- one final grand total at the end of report.
Container A
Type of seafood
Weight(kg)
Ave price ($/kg)
Price ($)
Fish
10
50/10 = 5
10*5 = 50
Prawn
6
30/10 = 3
6*3 = 18
SubTotal
$68
Container B...
Container X....
I'm trying to make an excel spreadsheet for a Kareoke business - what I want to do is have a database of CDs that they have in 1 sheet - this will include Song Name, Artist, CD Title, Track Number and CD Number - then I want the user to be able to go into the next sheet and type in the CD Number into a specified cell and thus will import all the information for that CD number into the required fields. I have attached a copy of what I sort of want it to look like. I don't really know the functions of Excel, but I'm quite computer savvy and should be able to work it out with instruction - please note, I've never used a "macro" and don't know what they are - so if I need to use a "macro" would you be able to give me a link to somewhere that explains what they are...and how to use them?
View 3 Replies View RelatedI have a report of users and thier roles
User# User_Christian User_Surname Role
1 bob smith operator
1 bob smith supervisor
1 bob smith warehouseman
2 john stone operator
2 john stone gunman
5 george brown Foreman
What Im after is:-
a matrix with unique Role list across the top
Unique ID & Name down the side
(I can do all the above using recorded macro's)
but the clever bit I cant even start to work out is how do I get matrix populated from the list?
want to generate automatic report from excel work sheet
View 1 Replies View RelatedI've been working on this for a while unsuccessfully. Any way I can create a simple report that is autopopulated with the data from a schedule. The report worksheet would need to be organized by division and AM/PM. see attached for further explanation. I've tried things like "Index(Match)", but it's too cumbersome and not automated.
View 5 Replies View RelatedIs it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.
View 2 Replies View RelatedI'm trying to use a List Box to select multiple items on my Pivot Table but I can't seem to get it to work. Its the Report Filter part of the pivot table.
When the list Box is set to fmMultiSelectSingle it work and selects the indivdual items, but I need it set as fmMultiSelectMulti for if i'm also needed to select multiple items. (which doesn't seem to work!)
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies View RelatedIs it posible and how to create a list of barcodes from a list of numbers on the worksheet?
Strokescribe seems to have some ind of solution but the data can't come from the worksheet.
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies View RelatedI 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.
Currently I am using Excel 2000. I did a search and couldn't seem to find the answer I was looking for. Hopefully someone can help. It would be greatly appreciated since I have been trying to figure out how to do this for days.
I am trying to create a worksheet that will pull info from a row on one sheet to another sheet based on the name; then continue to list the info on each line afterward until there is no more of the that particular criteria. See below (this is just an example to simplify the data but would need the same process):
This would be the data on sheet 1:
Name Bonus Commision
Tom $45 $50
Mary $25 $75
Mary $30 $80
Tom $60 $50
Tom $90 $25
What I would like to do is have 2 more sheets. One would be Tom's sheet and the other would be Mary's sheet and it would look like this:
Tom's Sheet:
Tom $45 $50
Tom $60 $50
Tom $90 $25
Mary's Sheet:
Mary $25 $75
Mary $30 $80
I am not completely familiar with all of the functions in Excel and compared to you guys I am a complete novice.
Is this possible to do in Excel without using a macro?
I am trying to create a sub-list from a master list based on a column criteria.
Basically, we have a large group of people that are now being split into 3 different sub groups, but still part of the whole. I would like to only upkeep the master list by assigning one of 3 managers to each employee and then having Excel place the employee's info (5 columns) into a seperate worksheet based on the manager assigned (3 seperate worksheets, one for each manager). I have searched through books and Excel help, but still cannot find any formula that can return multiple lines based on one criteria. Am I out of luck and forced to maintain this database with an Auto Filter and manually moving/copying the info?
Need Help in resolving the following issue:
Update a list using a Combo Box works fine when the list is on the same worksheet but when the list is on a different Worksheet it does not work.
Is this achievable? I guess so! but was wondering how to do it.
e.g: if my range defined name called let us say "SP" with a range AD1:AD60 in Sheet1 when I use a combo box the Listfillrange will contain: SP
When inserting a new entry using the combobox it works fine but when the same defined name range points to a different Worksheet example: Sheet2 then the update does not work although the ListFillRange contains the same Range name: SP.
Im still pretty new toi VBA and im struggling with pulling data from another worksheet and placing it into a list. Below is the code currently I think I have identified were its not working but not sure what to do. the code should essentially do the following
on opening the workbook check CS PS data worksheet find the names of people within the sheet, These start at row six and the step between each is 82 it then should input these names one after another in the following range Range("AO3:AO43") one name per cell until the end of the dat,a it seems to run but only inputs the first name in the first cell even thought it picks up each name in the myformula piece
Within the myformula there is a concatenate piece to flip the names to opposite way round I think this is where is not working possibly something to do with the separators? each name is in the format surname, firstname it should flip it so it shows first name [space] last name
provide ive commented out the piece I think is the issue
Sub CSupdate_()
With Application
.Calculation = xlManual
.ScreenUpdating = False
[Code]...
My first sheet of the workbook is an employee roster which shows a weekly rotation between two locations. I have additional sheets that are used as daily sign-ins encompassing a two week pay period for AM, MID and PM shifts. I want to link the sign-in to the roster so that I may add or delete an employee on the roster and have it auto generate to the sign-in.
View 3 Replies View RelatedI need to compile a list of all worksheets except Data, Attempted Calls, Completed Calls, Completed MOU, Incomplete Calls, Percent Complete, Busy Calls, and Percent Busy.
I need to put this list in B4
Then I need cells in C, D, etc to reference the value in B as the target worksheet.
All worksheets are formatted exactly the same, except the ones above.
Let me know if you need the data file or not?
I am struggling to populate a listbox with data from a different worksheet.
If it is the current worksheet works fine.
The worksheet is called "Drawing Data" ..
I need to create a routine that searches through all the worksheets in the active workbook. The worksheets tab is a specific color based on the level of urgency that is needed to attend to the information in the sheet. I am usin a userform with Option buttons to select the color I need to find and a 2 column listbox to which I want to store te contents of cell c4 of each sheet and the name of the sheet.
I've written the attached code but get a Runtime erroe "91"
Object variable or with block variable not difined
Private Sub CommandButton4_Click()
Dim listB As Boolean
Dim lCount As Long, lCounted As Long
Dim lShtLast As Long
listB = True
If OptionButton1.Value = True Then tabcolor = 3 'Urgency color is RED
If OptionButton3.Value = True Then tabcolor = 6 'Urgency color is YELLOW
If OptionButton2.Value = True Then tabcolor = 4 'Urgency color is GREEN
If OptionButton6.Value = True Then tabcolor = 41 'Urgency color is BLUE
If OptionButton4.Value = True Then tabcolor = -4142 'Urgency color is NULL
If OptionButton5.Value = True Then tabcolor = 53 'Urgency color is BROWN
If OptionButton8.Value = True Then tabcolor = 19 'Urgency color is WHITE
If OptionButton7.Value = True Then tabcolor = 48 'Urgency color is GRAY
Is there a way in VBA to get a list of all worksheet names in a workbook?
View 2 Replies View RelatedI have a list of data for a frame in a building. I used these forums to come up with a sort that will take the data and put in on seperate sheets based on the floor of the building. I need to now go through each "floor sheet" find the maximum values and paste them onto a Report sheet. This has to be done within a macro stored in my Personal.xls folder as the file with data is coming from another program and is always changing. Also, the sheet names wont always be the same as the format might be different like third, or 3rd, or "third". Here is the code for the sort macro, I am sure some of you will recognize it:
Public Sub Unique_Record_Extract()
'extracts unique records from Column A and copies those records to a new worksheet
Dim My_Range As Range
Dim My_Cell As Variant
Dim sh_Original As Worksheet
Dim Cell_Sum As Long
Dim i As Long
'turn off interactive stuff to speed it up
Application.DisplayAlerts = False
Application.Calculation = xlManual
Application.ScreenUpdating = False
'Delete All Blank rows if cell in column A is blank
On Error Resume Next
Range("A:A").SpecialCells(xlBlanks).EntireRow.Delete
ActiveSheet.Name = "MainSheet"............................
I am trying to figure out how to create a drop down list on one page of a workbook with the list of items for the drop down list located on a separate worksheet within the same workbook.
I have a workbook with one spreadsheet with a list of items on it (sheet1/ingredients) and the rest of the worksheets would have the drop down list of items from sheet1/ingredients.
I have been trying to use the validation method with no luck - I just can't seem to get the source path right.
If possible I would also like the drop down list of ingredients to be self updating - the ability to continue to add items to the list and show up in the drop down lists.
When the value in the drop down window is selected I would really like / need to have the data populated in the rows to the right of the drop down window.
While asking is there any sort of auto complete so that one can start typing in the box and the ingredients that start with those letters show up to select from? (Such as if type EVA or EVAP --- then all words starting with EVA or EVAP appear instead of the entire list?)
I have a Workbook with a dropdownlist on it. The dropdown list has some names on it each name will be a refference to another sheet, ie when name Collins Jim is clicked on the drop down list the Jim Collins sheet will appear. I have attached a sample sheet to show you waht i mean.
View 4 Replies View RelatedI am trying to build my first worksheet macro. I have a dropdown list in a worksheet "Active Email" with "Yes" and 'No". When Yes is selected I want to cut that entire row and paste it in worksheet "Archived Emails". I feel I am close with the code but when I select the word from the dropdown, nothing happens.
Here is what I have so far.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If UCase(Target.Value) = "Yes" Then
Target.EntireRow.Copy Destination:=Sheets("Archived Emails"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End If
End Sub
Looking for a way to use VBA to get a list of the tab names from a closed workbook and put them in a column of the worksheet that has the macro. Is there a good way to do that?
View 1 Replies View RelatedI currently have a workbook that copies the first sheet and emails it to an individual on the first day of the week.
New requirements are for it to be sent to a list of people.
I am at an impasse on how to proceed due to lack of knowledge. The current code obtains the recipient from Sheet3 D4 and i would like to be able to list down this column for additional emails without restricting this to a set number of cells.
I have searched the forum and have been unable to find what i am after.
I have included a test file to show what i have so far.
We use Outlook 2k3
We use Excel 2k3
I have the attached workbook that contains a list of produce in the Produce sheet and I am wanting to have the Availability sheet show what produce is available each day sorted into Good, Limited and None as per the data on the Produce sheet. I should end up with a list of each type of produce listed under the relevant availability each day.
Sorry but I have limited knowledge of VB, I assume that the best way to do this is some code behind the Availability worksheet but I am not able to determine what this code should look like.
I have a workbook that has 20 or so sheets that all roll into a summary statement via vlookups/sumifs/and other formuals.
Currently have around 300 vendors that need to have statements PDF'd which I currently do one-by-one.
Input range: VendorCounts$B$2:$B$1500
Cell Link: $C$1
Print area: A3:P437
Would like to PDF from print area each of the vendors in the drop-down list and save file to a designated location with the file name being the vendor name in the drop down window.