Vba/macro: Generate Individual Requests From A Summary

Feb 7, 2007

The problem I have is I need to generate individual requests from a summary (which I can do as long as the max values do not fluctuate)

e.g.
Number required each week
Tool wk1 wk2 wk3 wk4

flogger 1 4 2 5
wrench 2 3 1 5
socket 6 10 2 8

so for the flogger I would need to write 8 tool requests in total: 1 for wk's 1-4, 2 for wk's 2-4, 2 for wk2 only, and 3 for week 4 only.
There must be a quick way of doing this using VBA.

View 2 Replies


ADVERTISEMENT

Generate A Master Worksheet From Individual Worksheets

Dec 8, 2009

I'm having trouble making a master worksheet that imports data from a set of worksheets that are in the same file directory.

Basically,
I have about 200 or so rate sheets and I need to get them all into one master worksheet so that way I can run different data and graphs without manually typing each one into the master worksheet.

I've not really new to excel; however I am new to VBA and I'm assuming that's what I need to use??

I've now uploaded all the correct files that I'm using.

Attached:
RC1272.xls
Master RCF Report.xls

I need to find a way to import the info RC1272 and others alike into the master file. In reality, I'll have about 150-200 "RC" to get moved over.

What stuff that I need to move from the individual RC files are highlighted in YELLOW in the RATESHEET tab

Here's a list of the items that I need moved over from RateSheet (from top to bottom and left to right) to the master file

Provider Name: Column B
Provider Number: Column D
OSS/IPC Resident days: Column F
Total Resident Days: Column G
Total Provider Beds: Column E
Total Allowance Days: Column H...........................

View 14 Replies View Related

Generate Individual Supplier Report From Excel File?

Aug 27, 2013

I have a supplier list which i access all the time. To view individual supplier details i use find function using supplier code and then scroll through the sheet to view all the details and it is quite hectic. Is there any way i can modify the sheet so that every time i search for a supplier , only that supplier details can be shown in a different sheet or notification which i can also print if i want.

View 2 Replies View Related

Take Individual Dates And Total Into Summary Page Automatically

Apr 23, 2008

I have multiple tabs for each month (Jan, Feb, etc) for the 2008 year and I have a Summary tab.

For the individual months, I have each calendar day in a column and whether the day was for sick, vacation, etc in the rows. So if someone was sick in April, I would mark the day they were out of the office. And so on...

Well now I need to figure out how I can take those individual dates and total them up in the summary page automatically.

View 9 Replies View Related

Generate Summary Report From Range

Aug 16, 2008

I have a spreadsheet of website stats showing the number of visitors to all the domains and aliases we use for company websites. Each domain or alias has its own unique row of data. The data is in the order of most visitors. I have attached a simplified and anonymised example of the data in worksheet "stats". In real life this sheet runs to several hundred rows.

As you can see if look at the worksheet "domain key", each of our websites has more than one domain or alias pointing at it - these are reported separately by our stats package.

What I want to do is find an easily sustainable way of generating a summary report each month, such as you can see on the worksheet summary, which will give a total number of vistors for each site calculated from the visitors to the various adresses each site uses.

What I have done so far is use a very long SUMIF function, e.g. to find all visitors to the FR site the function reads:

=SUMIF(stats!A2:A16, "www.companyname2.fr", B2:B16)+SUMIF(stats!A2:A16, "www.othername.fr", B2:B16)

This looks OK in the example above but in the real data we have in some cases over a dozen domains pointing to one site and its very messy and hard to maintain.

What I would prefer to do is something that would use a range of data for the criteria rather than a specified string e.g.:

=SUMIF(stats!A2:A16, domain_key!C2:C16, B2:B16)

Obviously the straight SUMIF function won't do this. The advantage to this approach is that it would make the ongoing management of which domains are counted for each country a lot simpler as I could just edit the data in the domain_key sheet rather than having update the functions.

Some issues to be aware of are:
The order of data will change each month so youcan't guarantee that each address will be in the same row every monthThere isn't a pattern to the addresses that would allow you to use any kind of wildcard, e.g. you can't say all addresses containing "companyname" are the UK site and all addresses using othername are the French site. Similarly, you can't say all the french site addresses end in .fr - some countries use .com

View 9 Replies View Related

Delivery Report Summary - Monthly Total Sheet On 4 Individual Weekly Sheets?

Mar 3, 2014

We have a weekly "On-Time" report that shows early, late, on time, or no data. Each week has a total percentage of early, late, on time, or no data deliveries. I now need a monthly total on a seperate worksheet, but for some reason I can not get the percentages to total correctly. I am attaching the spreadsheet.

View 3 Replies View Related

Convoluted Sum Formula (formulas To Generate Commission Reporting Information On The Summary Tab )

Feb 27, 2009

I need 2 different formulas to generate commission reporting information on the Summary tab of the attached sample Excel file. The first is highlighted in green. For these cells, I need a sum formula that reports the total commissions (column H of the "Data" worksheet) for items Ordered in the month listed in column B of the "Summary" worksheet, but not invoiced until the month listed in the column D, E & F headers of the same worksheet. Date of item order can be found in column A of the "Data" worksheet. Date of invoice can be found in column E of the "Data" worksheet.

Now, the problem that I think I am going into is the way Excel handles dates and times. All columns and data highlighted in orange on the data sheet need to be maintained without being changed, as eventually I am going to have a report setup by our operating program drop in there so that it automates the information without any additional labor by our employees who have varying levels of Excel proficiency. Unfortunately, the report from our operating program cannot simply list a date without a time. Feel free to create any column or field to the right of the orange columns in order to complete formulas based on those orange columns. I will just lock those cells when finished so that coworkers don't accidentally blow the shizel up.

The second sum formula that I need is highlighted in yellow on the "Summary" worksheet. Basically, I need a formula that sums all commissions in column H of the "Data" worksheet for those items that are cancelled AFTER invoicing. Column D of the "Data" worksheet lists the cancellation date. There are explanations for each of these on the worksheets for quick referral.

View 2 Replies View Related

Number Of Requests Per Month

Jan 11, 2014

I have a Work Log that i would like to be able to analyse, in particular the number of requests submitted per month, The data in the work log looks this this: -

ClientPosition Para-plannerAdvice RequiredDate Required ByMy CommentsReceived by Paraplanning

BurgessCompletedPetePP transfer to GPP 21-Jan-13

Its the received by paraplanning heading date, i want Excel to be able to tell me how many reqeusts were recieved in Jan, Feb, Mar etc. I've tried =countif but that does not do what i need it to, just get an error.

View 4 Replies View Related

Ignore Remote Requests

Dec 24, 2008

I've just discovered that if you include the line: Application.IgnoreRemoteRequests = False. in a Workbook_BeforeClose event, and then close the workbook using ActiveWorkbook.Close

the

Application.IgnoreRemoteRequests = False

line is itself ignored! Code attached - the only workaround I can come up with is also included (to re-enable IgnoreRemoteRequests BEFORE the workbook is closed programmatically) - can anyone confirm this, or break it, or come up with a better explanation or workaround?

View 7 Replies View Related

Multiple SUMIFS (find And Add Up All The Total Requests For Only The Summer Period (Column Q))

Apr 28, 2009

I have a list of about 2,500 entries containing information of all my works leave requests for 2009. This list contains more than one entry per employee, as it shows all their individual requests for different weeks. The list is compiled pulling through data from other worksheets. It looks like this...

Sheet Name: MASTER

Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column E = Week ending
Column F to L = Days of the week
Column M to O = Request Type
Column P = Number of days requested for the week

I then have another sheet (Called REQUESTS) with all the employee names (1 entry for each employee), ID numbers and total requests for the year. This looks like this...

Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column P = Number of days requested for the year
Column Q = Number of days requested for the summer

I know how to locate and add up all the requests for each individual for the whole year (Column P) from the MASTER sheet to the REQUESTS sheet by using the flowing formula for each employee.

=SUMIF(MASTER!$D:$D,D2,MASTER!$P:$P)

But I don't know how to ask Excel to find and add up all the total requests for only the summer period (Column Q). What I want Excel to do is this................

View 2 Replies View Related

VBA Code To Get Individual Values In A List Placed Into Individual Cells

Apr 7, 2014

My current project involves sorting a spreadsheet, selecting specific qualifying data from the sheet, storing it into a variable, the pasting the individual values from the list into individual cells on a different sheet within the same workbook. Here is the code I have so far...

Code:
Sub Test()
'' freeze screen updating to remain on main worksheet
Application.ScreenUpdating = False
'' move to Avaliable worksheet
[Code] ..

When this code runs the first item in the list pastes into Sheet10 A1, but no other values from the list are placed into Sheet10. Previous to this I had been using a variation of this code to push the list into a combobox list within a userform. In that case instead of

Code:
ThisWorkbook.Worksheets("Sheet10").Range("A1") = x

I had

Code:
Combobox1.list = x

And this worked perfectly; creating a list within the combobox (in fact I can use this modification to interrogate my code to determine if the list is being properly generated, and it is).

View 2 Replies View Related

Macro To Choose First Immediate Date Of Each Individual

Feb 21, 2014

I have a group of huge data as follows. I want to create a macro to just choose the first immediate date of each individual (in another word hide the second rows onwards for each Name). How can i do it?

Name
Travelling date

A
2-3-2014

A
4-4-2014

A
5-7-2014

B
3-3-2014

B
8-9-2014

View 7 Replies View Related

Macro’s For Individual Combo Values

Dec 26, 2007

I have created a combo box and have inputted the values

Jan, Feb, Mar (think you get the idea).... Dec

I am looking to assign a macro to each value so that each one shows a graph, i have already created the macro’s to show the graphs. Just unsure how to make them work with the combo box

View 9 Replies View Related

Macro To Print Individual Records

May 11, 2007

I have a excel file wherein a sheet in database form is kept which contains data related to employess payroll.In another sheet,payslips are there. but as I want only one payslip to appear, so I have made the same using Validation dropdown list & VLookup function.The problem is now that the user has to select individual employee name from the drop down list & then to click the print as the no. of records are rising, it is not possible to print individually in the present manner.So I want a macro code which will:

1. Give users to print payslips with two options: Print payslips for all employess,print payslips for individual employees.

2. To print the all payslips, one page of A4 size should accomodate only at most 16 to 20 rows so that payslips can be later on torn out separately.My payslip format uses four(4) rows for one pay slips.

View 9 Replies View Related

Macro- Move Each Worksheet To Individual Workbook

May 10, 2007

I need a macro that will take each worksheet in my file & create a new workbook for each. The new workbook name will need to be the original worksheet name. The number of worksheets & the worksheet names will very each time I run this code.

View 9 Replies View Related

Macro To Copy Worksheets Into Individual Workbooks

May 27, 2008

Have a workbook with approximately 25 worksheets in it.

I want to copy/paste/value each worksheet and save it as it's own workbook.

My code doesn't work, it hangs up on ws.copy every single time, on Excel 2007 and prior versions.

Sub SaveWS()
Dim wb As Workbook
Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Copy
Set wb = ActiveWorkbook
wb.SaveAs "t:dir1expenses" & ws.Name & ".xls"
wb.Close False
Next ws
End Sub

View 9 Replies View Related

Excel 2010 :: Summary All Statement From 4 Worksheets And To Summary All Total ICC

Dec 23, 2013

excel 2010. This workbook has 4 worksheet(Process Engineer,OSBL,OSA,Lab Operator) I want to know what is the best excel formula/function to summary this 4 worksheet.

Example:I want a formula/function to summary all the statement from 4 worksheets and total number of answer "1" per statement from 4 worksheet.

Sample Statement below

"Demonstrate Interpersonal (People-to-People-) Skills" Question:What is the formula if above statement contains this statement in 4 worksheet?As i checked the total is 4 then What is the formula to get all total answered ICC on this statement from 4 worksheet?

View 2 Replies View Related

Macro To Find Individual Divisions Then Copy Info

Nov 13, 2009

I am comfortable with getting a macro to copy & paste where I need it to, however how to get the macro to recognise when a certain value has stopped.

For example in Column B on "DataDrop" are all the divisions that exist in my company i.e East Anglia, Essex, London etc. There is an empty row between each division.

View 4 Replies View Related

Copy And Paste Value Macro / Save Individual File To Right Of Specific Tab

Mar 25, 2013

I have a current macro that saves each sheet as their own file. The first 10-15 sheets are not really necessary, so I typically delete them once the macro has run. I am looking to add two things to my current code:

1. Add a tab called Start. Make the macro look for 'Start' tab and then just save each sheet to the right of that as its own file.

2. Save each sheet in the macro as just a copy/paste value. They current have a lot of links and it makes each file close to 3MB. That makes it incredibily difficult to paste all in one email.

Here is the current code:

Sub CreateWorkbooks()
'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object
Dim strSavePath As String
On Error GoTo ErrorHandler

[Code] ..........

View 1 Replies View Related

Macro To Generate New Quote

Mar 19, 2009

What I require is the macro code to achieve the following:-

Cells A2, B2 and C2 will be completed manually, however, when a new quotation is required the user must enter a new Control Reference Number in cell E2 and then press the Generate button.

Once pressed the Macro behind the button will generate the following :-

(a) Quote number underneath the last one in column 'A'

(b) Change Ref underneath the last one in column 'B' (this was the ref number input above in E2)

(c) today’s date underneath the last one in column 'C'

(d) a blank Quote Tab i.e. labelled the next number in the sequence i.e. Quote 2, Quote 3 etc which should be created copying the Template Tab

N.B. the total number of Quotes in any one work book should be a maximum of 30 so some form of checking would be required.

View 9 Replies View Related

Macro To Generate 3 Tabs

Jul 24, 2008

I am badly in need of a macro that will create 3 tabs using a value in sheet1 cell a1.The tabs created should be unique in the workbook and no sheet has to be duplicated or cancelled.After creating the tabs the macro should also create a list of the 3 tabs created in a sheet called list.

View 8 Replies View Related

Macro To Generate New Tabs & Name Them

Jul 29, 2009

I have a spreadsheet with a table of data. Each row needs to have its data on a separate tab, so they can be presented in idividual reports. Each tab then needs to be given a name which corresponds to the values in the column A from the spreadsheet.

I can generate a new tab with the following:

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet1").Select
Sheets("Sheet1").Name = ""

But I have a few issues with this... Firstly, the number of new tabs required will change, so it needs to replicate the formula for the number of entries in the reference table. Secondly, this relies on the new sheet being "sheet1" which I'm not happy with in case someone accidently creates a sheet 1 by hitting the new tab button. I then don't know how to give each one a separate name based on column A from the reference sheet.

View 9 Replies View Related

Macro For Summary Sheet

Jan 10, 2013

I have to prepare monthly sales register with a consolidated sheet. I exported site wise data from Tally. Since there are more than 100 sites, it is very difficult to prepare it manual. I am posting a sample file with 7 sites data and a consolidated sheet. I have copy paste first 3 sheets data to the respective column of consolidated sheet. Is there a way to do it through macro? Below is the link of sample file

[URL] .......

View 6 Replies View Related

Macro To Split Table Of RawData Into Individual Worksheets Based On Column C Value?

Jun 19, 2014

I am trying to divide a table of data (7 Columns, 500-1000'ish rows) into individual worksheets based on any unique values in column. There may be anywhere from 10-30 instances of the same value in Column C, and I need to bring all rows of each unique instance into a newly created worksheet to be labelled the same as the value in Column C.

The RAW data will be updated monthly by keeping a template file copying and pasting data, therefore I am hoping to have a "SORT" button with this Macro assigned to it. I have quickly thrown together a sample workbook with how the RAW DATA will be pasted into the file, with additional sheets showing how I would like the data split and organised.

View 2 Replies View Related

Generate A Workbook With Macro Included

Feb 17, 2007

Using either excel VBA or a .NET tool/functionality, is it possible to generate a new excel workbook which contains macros? I need to dynamically create excel files which will have macro functionality already built in.

This should be equivalent to asking how to use VBA or .NET to change/create macros in an existing Excel file.

This is weird, meta-macro stuff, I know. Any help at all would be much appreciated. If any of you know how/if any of this is different in 2007, it'd be amazing.

View 5 Replies View Related

Macro For Summary Of Multiple Worksheets?

Feb 6, 2014

I am trying to create one Excel file for accounting purposes. Within this file I want to create a worksheet for each customer I have in my store. On this sheet I want to list my costs and their payments. I would like to then create a summary sheet which adds all of the individual customer sheets together. Now this is easy to do manually, but what I need to create is an automation (Macro I assume) that does the following:

I add a new sheet and name it after the new customer. The Macro automatically adds that sheet into the formula for the summary sheet. This way may secretary only needs to activate the macro, which will copy a sheet and automatically update the summary page to include this newly copied sheet and all its information. Now the process for automating projected costs and profits is very easy and requires nothing more but to activate the macro.

View 14 Replies View Related

Macro To Generate Email When Number Reached?

Feb 6, 2014

I have a spreadsheet that in column A has someones name, in Column B it has a number.

For example

Column A Column B
Bob Smith 1999

I am looking for a macro that will automatically send an email to this address for example test@test.com, when the number in column B reaches 1200.

I need the email to have in the body - "Bob Smith has reached 1200"

I need this macro to run against 20 sheets in my workbook.

View 4 Replies View Related

Excel 2007 :: Use Macro To Generate A Form

Mar 11, 2012

I have a sheet with 500 rows and 20 columns. I want to put an input box and a button on a different sheet. What I would like to happen is that when you put the criteria in the input box and click the button it opens the form for the row corresponding to the criteria and allows you to edit the 20 columns. Excel 2007.

View 6 Replies View Related

Macro To Generate C++ Header File Using Excel

Feb 19, 2014

I am thinking to make a excel file which can generate a headre file for my c++ source file. Previoulsy we used to generate .h files using excel but i dont know the logic behind that(Hope some macros using for that).

MY header file contains this many data and my intention is to give "MYapp Alpha 0.0.3" through excel file because the version number changes for each release. If i used excel file then I can edit that excel and it creates .h file for me, later some more informations i can make configurable through excel file

#define APP_FLASH_APP_ID 0x123
#define APP_VERSION_NUM "MYapp Alpha 0.0.3 "
#define APP_PRODUCT_NAME "TPI "
#define APP_DESCRIPTION_STR APP_PRODUCT_NAME APP_VERSION_NUM
#define APP_RELEASE_DATE_STR "10/11/13"
#define APP_SOFTWARE_PARTNUM_LEN 10

View 3 Replies View Related

Autofill Summary Macro As New Worksheets Are Added

Jun 11, 2014

Trying to write a macro so that my summary worksheet will auto populate when new sheets are added and filled out. I want the PO#, Quanity, Date ordered, Vendor, Subtotal, Tax and Total all to transfer from worksheets like F001 to the summary sheet. Yes there is only one F001 sheet filled out but that number will rise into the hundreds as the project progresses. No idea where to begin when writing this macro.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved