I have a macro (see attached) that works fairly well, but the one thing I cannot figure out how to do it how to merge cells whenever there is a break in Project ID.
If you take a look at the spreadsheet, you will see that columns B and C for the 2nd Project ID is merged just how I want it to look. The number of rows in each Project ID is variable.
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???
View 2 Replies View RelatedI 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 RelatedI download excel report from Business object reporting tool and the file will be very huge something like 25MB and it takes nearly 40 Min to open the report and the data will be merged in the report......can any one suggest what can be done to open the report quickly or can we open the report by using macro.... can we improve the performance of opening the 25 MB report thru macro...
View 9 Replies View RelatedI've got a basic handle on the application/use of macros, & personally use them for cleaning up data exported from a very very old as400esq application.
My problem is that i have managed to clean up all the blank data & piffle, but Im stupped by the header part of the text dump.
Heres an example;
A B C D E F
-------------------Header A-------------
1 3 john 50 piano
4 1 Bill 25 horn
6 5 Ted 60 trumpet
-------------------Header B-------------
3 3 Phil 71 Cello
1 1 James 83 Viola
3 2 Earnie 62 Violin
I basically want to rid the header rows from the spreadsheet & drop the header description (Header A/ Header B) into column F for all those items below until you get to the next header.
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.
I have n rows on a tab and I have counted them using Offset and Counta and put that count on B1.
I now need to make a little macro that print from row n to row number in B1. I've seen macros using things like "For i" "Next i", etc... but I do not know how to set the macro to start at a cell with a number n and repeat till the number in B1.
I have a very large workbook which includes 100 sheets (A) each containing fixed data relevant to one unit (n) (in this case n = a building; there are 100 buildings), a few sheets (B) containing parameters & settings, and then 1 main sheet (C) which does complex calculations pulling & combining data from sheets (A) based on the parameters in sheets (B). The workbook is so large, (100MB+) that it has to be set to calculate manually.
One of the settings in one of the sheets (B) dictates which building (n) is currently being looked at. As such, sheet (C) effectively builds a report for building (n) and so can report on just one building at a time, that building (n) being determined by a drop-down box listing all of the available buildings and that list is in turn defined in one of the sheets (B).
If I want to get a consolidated picture for all buildings I currently manually change the building choice (n) in the dropdown box, and then run a macro (m1) which updates the report to show what I want, selects the specific bits of the report I want and copies the selection, switches to a separate pre-formatted consolidation sheet I have created (D), jumps to the last row in that sheet, pastes values and then jumps back to Sheet (C).
I then need to manually change the selection (n) and then rerun macro (m1).
What I would like is another macro (m2), which automatically loops through the different values for (n) so that the whole process is automated. Then in a perfect world, perhaps even a separate macro (m3) which would give me a list of tickboxes so that I could select specific properties for which to run the report.
I need to be able to select a pic from a client's folder and imbed that pic for my report.
View 1 Replies View RelatedI'm trying to find a macro that will report how many values are returned in a pivot table. For example, if there is 5 values returned, I want it to return the value "5" in a cell If there are 10 values returned from the pivot table, I want to return the value "10" in a cell
View 1 Replies View RelatedPlease see attached two books,
The first book is a report that is generated on my reporting systeM (Report I use everyday)
The second report is the one that I have created myself using a macro (macro report)
I have to manually manipulate this report everyday and extract some information from it to
a custom report(macro report) that we use everyday.
I have created a macro for this - please see my code below.
I need some help with some small issues I am having,(Highlighted in yellow)
1) I need to include a Total line, but the size of the report varies everyday from say
around 30 rows to over a 100 rows. Is there a way i can make the total always 2 rows below
the report, please see attached for an example.
2) I need to hardcord a formula in on Column F and G. But again the report varies in size,
the columns do not change but there could be lots more rows. how do i take this into
account
The formula i need to hard code into column F is
=(IF(($D$60>=0)*AND($D$60<=5000000),0.0009))+IF(($D$60<=10000000)*AND($D$60>5000000),0.0007
)+IF($D$60>10000000,0.0005)
D60 in the above formula is just the total of all the other months summed together.
For Column E the formula is just =F*E But again the size in the report varies.
I have a pivot chart, filtering data from a pivot table, the problem I've got stuck is that every time I filter the chart the colors and weights of the chart change to default values, not what I want, I've managed to every time I click on the pvt cht tab it updates with the colors and weights I want, but when filtering I don't know how to do.
View 1 Replies View RelatedI want a macro that will transfer the data of billing report in the data sheet of the excel file billing dec 2011.
View 1 Replies View RelatedI have an excel sheet containing data as given below, wherein we receive certain report from each department on monthly basis and the same is mentioned as received against each month under header column. We need to remind the department about non receipt of report for particular months which contain empty cells. Macro is required to generate the report of empty cells mentioning the months against each department.
Available Data
Dept
Sep-12
[Code]....
find the attached Example file. I need to create a report either by using or without using VBA.
View 3 Replies View RelatedI have a procedure that processes data in a spreadsheet. It analyzes data in 365 sheets ( named "1" through "365") and creates a table/report with the results. When I run this on a scaled down version of my workbook (5 instead of 365 worksheets), the result is almost instantaneous. When this is on my normal workbook, the initial processing is about 1 second per day on Day 1, and the last day it speeds up to almost be instantaneous. It is not a linear relationship between the processing time (still working to get more exact timing information). Also, even if I limit my processing to 5 or 10 sheets, Days 1-10 are always slow and days 350-365 are always fast. The code being run is in the structure as shown below:
numSheets = 365
For counter = 1 To numSheets
x = CalculateSomething(counter)
Next
Private Function calculateSomething(counter As Integer) As Integer
Dim strCounter As String
strCounter = counter
With worksheets(strCounter)
For i = 0 To someNumber
For j = 0 To someDifferentNumber
'Data analyzed on worksheet
Next
Next
End With
End Function
The functions obviously aren't copied and pasted/functional, but the relevant efficiency stuff should be there.
1. Macro runs and Insert 10 blank rows after each item row. EXCEPTION: if rows are identical on Column A do not insert. Insert - only after non-duplicates. There are times where there 3-4 rows that are part of the same group.
2. Insert a bottom border line to separate each group. See attached for sample.
I have an array which includes an if state as such:
for j = 0 to 5
for i = 0 to 10
if menu.value = "Class 1" then
Worksheets(currentsheet).Cells(startrow + j, startcolumn + i).Value = Class1_Schedule(j, i)
elseif menu.value = "Class 2" then
Worksheets(currentsheet).Cells(startrow + j, startcolumn + i).Value = Class2_Schedule(j, i)
else
Worksheets(currentsheet).Cells(startrow + j, startcolumn + i).Value = NightClass_Schedule(j, i)
next i
next j
I want to write some sort of formatting code within this forloop to say
if menu.value = "Class 1" then
position (j,1) = a number to 0 decimal places and position (j,2) has data validation lists.
I know how to use the record macro button but I dont want to code for fixed cells. I would like to use the forloop to do it.
I need help with speeding up my macro. I have been building this using alot of the recorder and the maco is just horribly sluggish. I don't know if it is possible, but it seems like it is. I need the macro to obviously work the same way. The way it has been recorded, the order of operation is important. But if any one knows how to do this better it would be a huge help. The slowest part is during the insertion of the formulas into col. J,K,&L takes like over 7min -- Macro does work perfect though.
View 9 Replies View RelatedI am looking to create a macro that conditionally formats a cell depening on what words it contains.
if the cell has the word "level 1" in it = pink backround
"level 2" = red backround
"level 3" = Orange backround
"level 4" = Green Backround
"level 5" = Blue back round
i would like this to run on the active cell.
I am trying to work with conditional formatting using a macro.
How it works is I select the range I want to format manually then I run this macro.
It doesn't work
When I check the conditional format the formula is wrong. Instead of referencing the correct cell in columns E & F is is referencing E65282 & F65282
What do I need to change? ...
A1=text A2 will either be "yes" or "No"
What I want to do is format A1 by either a fill colour or text colour. Any type of format I guess. If A2 is "Yes" then A1 is green and If A2 is "No" then A1 is red.
To start off macro should pick selected columns such as owners and their projects, start date, price Final price, priority and status columns and paste into new sheet
At the same time only pick selected owners(Dave, Brian, Ken, Russell, Ben)data along with their high low, and medium projects then format the price and the final price column (that is to first convert them to numbers, then change it to the currency format and set the decimal place to 0) and then sort owners (AtoZ) along with Price and final price columns (largest to smallest) into a new workbook and to a new sheet in the same workbook with a name to the sheet called Extracted data
note owners may change and new members might get included in the list ( I am not sure if something dynamically can be done about this in the macro)
I have included sample data and how the output should look like.
I have a complex sheet where rows are continuosly overwritten and new data needs to be formatted each time. My macro works great aside from the fact the conditional format formula expression adds double quotes rendering the conditional format useless. After running the macro I can go into the conditional format field, remove the quotes, and the formatting formula works as expected. So...I first did a "record macro" to grab the code for conditional formatting:
View 2 Replies View RelatedI am trying to record a macro for conditional formatting but when I check the VB Editor after I finish recording it, it only has
Sub CFTest()
'
' CFTest Macro
'
'
Range("D1:D4").Select
End Sub
How to get the keystrokes to record or what I need to do?
Reference with the different possible codes used in creating your own conditional formatting in a macro?
I need to conditionally format cells, based on a different column of cells having a particular letter, to have a black up or down arrow.
I have the code below in a macro, but cannot figure out how to add the following format to the left and right header:
size 12 Ariel font, Bold and Italic
I cannot seem to get it right!
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.166)
.RightMargin = Application.InchesToPoints(0.166)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(0.8)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.3)
[code].....
I am having trouble with what I thought was a simple macro.
Data from report comes in dd.mm.yyyy format so I need to replace . with /
When manually carrying out the replace operation it works fine but when recorded macro carries out the operation a further task does not go as expected.
Next column is AGE (=today()-[date]+1) gives some unexpected results i.e. incorrect to the point of 100's days out!
I have a very large spreadsheet that is run by a different department. Regularly, I need to go into it do the following:
-filter the sheet by the information in one particular column - - the column is always the same but the number within it by which I filter changes as needed
-double-click each column to autofit the columns
-go to the page break preview to make the document fit one page
-print the results.
My question is can I automate this somehow with a macro or something else?