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.
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.
i have daily time sheets that make up a week and have 52 sheets for each week...there are contract numbers and contract ticket numbers that i want to use as criteria to sum the total hours of each day and export the data to a sheet that will keep a running total of all hours booked to those contract number and contract ticket number over the coarse of the year as i fill out the weekly time sheets.
where to start with automating a monthly report that I put together.
My report is generated from two or three tables of data - roughly 25,000 records.
Currently, I copy and paste this table of data into one worksheet, and then have pivot tables that are written to take data from this table (this is a typical marketing sales funnel - calls, raw leads, qualified leads, customers, revenue). Then I have another worksheet that is the display to the client, with the proper formatting, mathematical calculations, totals etc. On this client facing worksheet, I pull data from the pivot table (using =GETPIVOTDATA formulas).
It seems that I have constructed this report in a very inefficient way because I have a ton of manual work to do every month to make the client view presentable as changes come about in the raw data. For instance I segregate the client facing report by lead sources, but when new lead sources get added in month by month, I have to edit the client facing report at length.
where to start, or what steps to make this more automated
I have this table that I put together everyday describing the activity of my department. There is the date displayed as "Num-Date", the day of the week and multiple columns of data. Every day I have to add a new row and update the date, and the data, etc. I have a macro that inserts a new row and auto-updates the date and weekday, I also have a web query set up to fetch the data I want and display it right next to the table, but what I want to do is make the table more intuitive and automated.
I have table and need to take out montly total for each worker...
Now... Each hours in day have own factor. (I need total hours per day but for illustration)...
So when worker works day shift from 8:00 to 16:00 it's easy... 8 hours When works from 8:00 to 20:00 it's 8 hours + 4 afternoon hours When works from 20:00 to 8:00 it's 2 afternnoon hours + 8 night hours + 2 day hours
Aditional problem is when day intercept holliday or sunday when that factors need to be included (if holliday is at sunday then it's like holliday).
Here is some attachment:
Book1.xls
I've also added last day of previous month and first day of next month because of night shifts than need to be calulcated. Therefore correct number of hours is 168 and not 188.
Below I calculated manually those numbers wich I want to be automated...
Also.. This is table I get.. If it's easier to make it somehow else, OK by me. And any number of aditional columns is not problem...
I am new at excel and have very limited experience. I have a employee training database, with employees (in column A2:A54) and the certification names (in row A1:AS1) that I must generate a monthly report on employees that are expiring within the current month. The cells are already conditionally formatted to change colors for one month prior to expiration date, current month of expiration date, and past the expiration date.
I am manually gathering the data then adding it to another sheet. In the mist of trying to gather data, I am missing and/or overlooking information. I wanted to know if there is a way to automatically generate a report based on the expiration dates that would pull the employees name and which certification is expiring or expired, preferable both. I have tried reading threads and applying advanced filters but due to the number of certifications it is not functional to do it this way.
I have attached a sample of the spreadsheet : ETD.xlsx
I have a workbook with 12 sheets for monthly results and a Year to Date sheet. For presentation reasons, the monthly results are listed as seen in the first three worksheets, but I want the YTD results to be structured as seen in the fourth worksheet. I have shown how I am currently achieving this in the "Call" section of the YTD sheet, but as there are 12 months and many more variables in the original workbook, I'm wondering if there is any easier way than the one I'm using.
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.
I have looked through the forum and found pits and peices but can not put the puzzle together. Found the VBA code in my example from a earlier post but there was no final answer to the post.
Trying to have the user put in a date range via command button. Fro mthis date range the data thats falls within that range is copied to a report sheet. Will also need to have all the old data from a earlier querry removed. Have attached example sheet.
What i want to do is copy all records from whatever date i enter, onto sheet test. The full excel file has over 80 worksheets for each individual rep, the example i attached has 8 sheets..
I have lots of monthly reports to prepare. So what i do is to copy previous month report file and paste and rename it as current month report. Once i open it, i need to change the linking to the next colume of the source files. Eg Source files July is in Column H, then now i have to change it to Column I in my current month reports.
Furthermore, one file may have a few source files so the column may differ also.
I am to the end of my wits - or maybe it's impossible to do the following with formulas?
I have the data like this: Column A: Date (which is basically the date for the beginning of weeks) Column B: Month of the date in Column A Column C: Year of the data in Column A Column D: Weekly data.
Maybe it's because it's Friday night, but I just can't invent how to do the following:
Create a new column E that would contain the monthly sum of ColumnD across all weeks of this month - but entered only against the first week of that month (that is currently in Column A) I.e., in my example it should be: 113 empty empty 201 empty empty empty empty..................
I have 12 observations per variable over 2013. At the end of each month I gathered the average value of X of said month. What I want to do is convert these observations to a list of weekly observations, where each week in January gets the value of the January observation
Example:
Month123456789101112 Value 2345678910111213
And get it like:
Week Value 1 2 2 2 3 2 4 2 5 2 6 3 7 3
and so on.
Since I've got a lot of variables I'd like to use some kind of formula.
I have two tables "Table A" and "Table B" with records in rows (typically). On one Worksheet I have a report with 10 empty "slots" ("Slots A") waiting records from Table A, and 10 empty slots ("Slots B") waiting Records from Table B. In those slots, fields are arranged one below another.
PROBLEM 1. I need to find a way how to automatically copy chosen records from table A to slots A, and from table B to slots B. There is no rule how many records, and which records are to be copied. Some slots can stay empty.
PROBLEM 2. Slot B has the same number of fields as the Table B, plus one more field, let's call it "extra cell". In the "Extra cell" goes a result of a function, which arguments come form that Slot B, but from ONE Slot A as well. So, I need to "link" every Slot A with one or more "Slot B"s.
I have a workbook of about 30 worksheets which makes extensive use of Data Validation. Essentially, data entry cells is validated against values within some predefined ranges (there are 6 ranges in total).
I am preparing documentation for this workbook and would like to document the data validation being used. Is there a way to extract this information? Where/How is it stored?
I would like to transfer data from one tab to another to for a report.
First off I would like to take all the rows that have "O" in column B. But only if the year in coloumn Q or R is this year. (Q is order data so I show all new orders taken this year) (R is despatch date so I show all orders despatched this year)
There will be around 25 orders total
I would then like to add a total to the bottom of this set of data.
Then move 4 rows down and bring all the data that has a status "HP" in column B regardless of any other criteria.
There will be upto about 100 Hot prospects (HP's)
My data is held on tab "BRAND" and I would like to generate the report on "BRAND HP"
My data has a header row in row 1 and holds data from A:T
I want to create a report from an existing excel spreadsheet that will show the first row with column headings. I only want certain columns from the spreadsheet to show on the report (see attached). On the left (1st column), I want to show the data from each of the last 7 days. Then I want to show data for rolling last year to date, then for last rolling year to date, and finally for last rolling 30 days.
I have a large list of items (up to 3000) that are listed by category, description, part number and weight in columns C-F. Column A is where a user will enter a quantity in the rows of the items that they want. Once the user is done picking their items they hit a button that I have written a macro in, to copy and paste only the rows that contain a quantity in, on to another sheet. My problem is that for these items (that contain a quantity) I need to have a line number (in column B) that starts at 1 and counts up for each line item. Issues to consider:
-Line item 1 may not always be the top item. (because the user may not choose the top item) -There will be spaces inbetween the line items since the user may not pick all of their chosen items in order. -Users can pick an item at the bottom then pick one above it. (The Line item numbers will need to readjust to include the new number in the middle)
This has been bugging for a few days now cause whenever I think I finally get it I keep creating circular references
I need to extract and format a large data base of county real estate tax parcels for a report. See attached. How can I combine these or is it possible.
I have made several stabs at extracting data from an inventory report, but keep hitting roadblocks and would appreciate some help in my 1st VBA project.
I have attached a spreadsheet with sample data - the data is in a fixed format, and I have overwritten unnecessary data, but all the data to be extracted is in Columns D, O, and P.
My approach was to go down column D testing for non-blank fields that do not begin with "Total", thus creating an array of item names. Then I would add the code that would go across adding Quantity and Value into the array.
trying to do anything other than the basics with Excel. I would like creating a macro to import data tables contained in a report that I receive in HTML format.
The Report looks like the following. I de-identified the data
VRAAPCO1 VER 8.1 VANMANDY ADMINISTRATOR PAGE 1 01201010 CONNECT SUMMARY CPU 046E16 REPORT DATED: JUN 12, 2012 17:53 INFORMATION AS OF: JUN 11, 2012 23:09
EXTRACT FILE(S) USED: DDName=SVSAM DSN=SYSL.VRA.SVSAM.EXTRACT DDName=MVSAM DSN=SYSL.VRA.MVSAM.EXTRACT MASKING CRITERIA: MGROUP=FSCSC OR MGROUP=FSCORE OR MGROUP=FSTECH OR MGROUP=FSBSM OR MGROUP=FSCCS OR MGROUP=FSEMFE OR
[Code] ........
The column group represents a security profile for an application. I am auditing the access to that application. They either start with the '#' symbol or the '@' symbol.
I receive this report once a quarter. This being my first time. I had to manually cut and paste the data tables from the HTML report file into a spreadsheet.
This is what I would like to be able to do in excel using VBA. The report name is the same every time. 'FS.ASET.Users.htm' 1. Would like to create excel spreadsheet with a macro button and master worksheet that already has the headers.
Group Userid User Name Owner Create Date Special Operations Auditor Revoked ADSP GRPACC UACC Authority
2. That looks in a network directory(will be the same everytime) for this htm file(will be same name every time)
3. Pull only rows that start with # or @. Place them into single worksheet with sorted by 'Group' ascending.
4. A stretch goal would be to have the macro create a different worksheet within the spreadsheet for each profile name, I.e.,@UFSGUAR, #ZCBSAU, and copy only those rows that start with that profile name into each subsequent worksheet.
First I copy and paste the text into Excel. All of the data is in column A because of varying lengths, which seems to work fine. I've already written VBA code (shown at the end of this posting) that reads the contents of each cell in column A, pulls out the key information (like the actual error code or invoice #), stores the fields in variables, switches to a separate tab that has a more user-friendly layout, locates the next empty cell in the appropriate column for the field, and puts the variable contents into the cell. I realize that the code currently has some variables that aren't being used and other basic issues, but that's not really what I'm after at this point.
The report itself includes billing errors, with each billing error separated by a series of asterisks. My current code works just fine for sections of the report like the first section in the sample below, where the billing error only contains a single invoice, error code, description, and action item. If the entire report was formatted in that manner, my task would be finished because it does those sections perfectly well.
But...if you notice in the sample report below, the third billing error contains more than one invoice, error code, description, and action item. Obviously, using my current code, when the macro encounters that section, it continues to function, but the rows do not align properly in the separate worksheet because there are extra invoices, error codes, descriptions, and action items. I just cannot figure out a way to have the macro keep the information aligned. In other words, in the third section of the sample report below, the billing error contains more than one invoice and item.
One approach that I've thought of is to try to count the number of rows between the cell containing the previous set of asterisks and the cell containing the next set of asterisks, because it's the information between each set of asterisks that I need to work with. But I haven't found a way to do that and don't even know if that's the direction I need to go or not. I'm pretty much lost at this point.
Here's a sample of the text report as it appears in column A (the fields are actually longer than this - I tried to simplify as much as I could for this posting): ....
It does everything I need it to do but I would like it to sort 3 columns and then merge the data. I have included a sample with a "WishReport" tab the shows what I am looking for. If possible (not imperative) I would like it to add a "unit cost" column and provide a grand total.
Option Explicit Public Const SZ_INPUT As String = "SheetData" Public Const SZ_OUTPUT As String = "Report" Public Const I_FIRST As Integer = 0 Public Const I_LAST As Integer = 1 Public Const I_DATA As Integer = 2 Public Const ROW_HEADER As Long = 1 Public Const ROW_DATA As Long = 2 Public Const COL_FIRST As Integer = 1 Public Const COL_PROJECT As Integer = 2 Public Const COL_LAST As Integer = 5
Sub CombineProjectData() Dim lInputRows(I_FIRST To I_DATA) As Long Dim lFirstInstance As Long, lOutputRow As Long Dim szData As String Dim iCurrColumn As Integer Call FindRowArray(lInputRows(), , COL_PROJECT)...............
