# Macro: Analyzes Data In 365 Sheets ( Named "1" Through "365") And Creates A Table/report With The Results

Mar 13, 2007

I 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.

## Use A Macro That Creates Sheets And Places Formula In Cells

Jun 20, 2006

I am trying to use a macro that creates sheets and places formulae in cells but the resulting formulae are not what I coded. Here is similar

Function CreateSheet(i As Integer)
Set Adjustment = Worksheets("Sheet1").Cells(6, 3 + 4 * i)
With Selection
.Offset(22, 9).Formula = "=Sheet2!B14*Rating!" & CellAddress
.Offset(23, 9).Formula = "=Sheet2!C14*K4*Rating!" & CellAddress
.Offset(24, 9).Formula = "=Sheet2!D14*K5*Rating!" & CellAddress
End With.............

## Excel 2010 :: Changing Pivot Table Report Filter Via VBA With Named Range Or Array

Jul 21, 2011

I have a set of four pivot tables on a sheet that I need to programmatically change a Report Filter (Page Field) so I can create sets of reports in an automated fashion. This will be the first step in that process. The change will involve choosing > 1 Role each time the code loops through based on Named Ranges I've defined that are associated with that Role.

My code thus far:

Code:
Sub TestCode()
Dim pt As PivotTable
Dim pf As PivotField
Dim pi As PivotItem

[Code]....

emm_dc_gsr is one of many Named Ranges that will contain a variable number of elements. Just using the one right now to see if I can get the code to work, I'll eventually make another Named Range/Array of all them so I can loop through each Report ("ReportPick").

I want the Report Filter to consult that Named Range for its values and apply those values to PivotField "Role" that is used as a Report Filter.

When running this code above, I get a "Role" Field that says "All" but no values (the table is completely blank), with no evidence as to why it'd be blank (all filters in every Report, Column and Row are working normally and are filled in). When I choose a value manually after the code is run, the pivot table values populate. Do I need to somehow index the Named Range in that loop? I'm just confused about this step right here:

For Each pi In pf.PivotItems
If pi.Value = RolePick Then
pi.Visible = True
Else: pi.Value = False

When I've run other versions of the code, I've gotten an array version of it to "work" using LBound and UBound, but it never chooses the right two values even though those are verified as stored in the array via a pass-through. It chooses the first few values in the Report Filter.

Here's the corresponding code for that:

For i = LBound(myArray) To UBound(myArray)
pf.PivotItems(i).Name = myArray(i, 1).Value
pf.PivotItems(i).Visible = True
Next

I do not care if I use an array or a Named Range. I just want something that is simple and works. Passing the values directly from the named range seems easiest to my brain, but I'm open to anything and I'm clearly missing something (probably silly).

I also have no idea why " .AutoSort xlManual, .SourceName, .EnableMultiplePageItems" is necessary though every piece of sample code I've seen seems to have some variation of it.

(Using Excel 2010, Windows 7.)

## Pivot Table, Calculated Item.... No Data, Still Creates A Row

Oct 12, 2007

I have a Pivot table.

Layout:
Rows: City, Restaurant, Cashflow
Column: Date (Month/Year)
DataField: \$Amount

"City" might be 7 cities
"Restaurant" may be 32 restaurants, some in some cities; but not other
"CashFlow" can be either "Revenue" or "Expense"

The layout of the Pivot table is nice, only shows the Rows where a State, Restaurant, and CashFlow entry exists for at least one Date on that row. And there are no extraneous rows for combinations that don't exist.

Now.... the trouble...

I added a Calculated Item, "Profit" which = "Revenue" - "Expenses".
Now, every possible combination of State and Restaurant appears in the pivot table displayed. Only the "Profit" (calculated item) is shown for the previously hidden rows; and of course it's "\$0.00" since there are no "Revenue" or "Expense" entries.

How can I get the Pivot table to not display the results of a Calculated Item row, when there are no entries otherwise for that row combination?

Is there a way to condition the Calculated Item to not calculate if there are no data entries in the addends for a particular combination?

## Pivot Table Creates Extra Data In Fields

Jun 17, 2009

I'm trying to create a pivot table, but there is a problem. Although my data only has 2 years (1996 and 1997), it creates a new year it calls "blank," and a lot of the data that is supposed to be under 1996 and 1997 appears under this "blank" year. How do I fix this? See the attachments for the data and pivot table.

## Results From Multiple Sheets To Table

Jan 24, 2010

i have a workbook with 30+ sheets and i want to create a new sheet containing a table from aprox 6 values from the others.

i have done this in the past by typing =(then select the cell from the sheet i want)

while this works it is very time consuming and filling down after i have done 1 row gives the wrong results

## Button That Creates New Sheets With Buttons In Those New Sheets?

May 27, 2014

I'm editing some else's workbook. He has a button on sheet 1 that creates sheets 2 ad 3. I can see and edit his old code. I need to add a differet button to these sheets and 3. I can easily do this once those pages are created, but I need those buttons to come up as the sheets are created from the button on sheet 1. T

## Macro To Copy Data From Excel And Creates Pipe Delimited Text File

May 27, 2014

Macro to copy data from Excel sheet and creates a Pipe delimited text file.

## Macro To Report How Many Values Are Returned In Pivot Table

Jul 15, 2014

I'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

## Data Table: Incorrect Results Inside Table

Feb 22, 2007

I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?

## Summary Report Of Dynamic Table Data

Nov 10, 2008

I am interested in finding vba code that I can enter as an add-on for a weekly training report that I receive.

An example of the weekly report is attached. A Computer based training program populates the reporting tool with the date that the course was completed. Each week a report is generated as attached with the dates completed filled in the matrix.

The goal is to: Report the total percentage of training completed. I would like to be able to run a vba add-in that will determine what rows have entries. Perform a countA on the date fields. Sum the counta's and populate a cell with the % complete. In one simple touch of a button or keyboard function that can be ran each week without editing the code. These reports are filtered by department and the size of the matrix change all the time.

The hang up I am having is making the code dymanic enough to figure out what cells are filled and calculate and populate by that factor.

## Format Data For Pivot Table Report

Dec 27, 2007

I am building a tracking process for marketing and relationship management purposes. My company has a database (Advisor's Assistant - which the server is on site) that is for lack of a better term very limited. I have tried to identify if we have the capability to use SQL in excel to pull the information we want but that avenue looks bleak, since my co-worker that is pretty tech-savvy has had very little luck working with the database provider to get to information we want.

Anyway, I have determined that I can get the information I am interested in by way of several reports that the current database will do and printing them to a .pdf file. Then by way of a program called Able to Extracted I can get them into an excel format. The problem I am trying to solve is using excel to pull the information I want out of these twice converted reports into a format that means something in excel.

Only the reports reach excel they have many empty cells and some of the information is offset and does not follow the same pattern as you scroll down through the report. I have attached part of one of the reports. I would like to automate the process of searching the data and creating a new format that I can use a pivot table to create reports off.

## Summary Report Sheet From Data Table

Apr 30, 2008

I'm going to try to explain this as accurately as possible. This is my first post, and I'm seriously struggling with VBA, but i'm confident that someone out ther will have done this before.

I have a single workbook which has a single datasheet, and further sheets representing different months of the year.

Sheet 1 ("Data") contains the following columns and data:

01/05/2008013 get 13
02/05/200813260
03/05/200826100 euro 74
05/05/20081131130
06/05/20081131130
07/05/20081131238 alex begg1125
08/05/2008123812510
09/05/200812511288 service 37
10/05/2008128812980
11/05/2008129813110
12/05/2008131113780
13/05/2008137814000
14/05/2008140014560
15/05/200814561490.10

I then have a series of Summary sheets which are supposed to summarise the data on this single data sheetl. But this is where I have issues.

Basically, from the list of data above (running from 1st may 2008 - 31st December 2008), I want to pull out, per month all of the trips in that month. So for May (shown above) I need the date, business trip name, and the business mileage where the business trip name <> "".

To compound the issue, the columns that these are moving into are columns 1, 2 and 6 in the summary spreadsheet.

I have used some example code from cpearsons website to give me a list of nonblank cells in the Business Trip column, and this has worked, but I can't pull the other two columns out effectively.

## VBA Macro To Name Sheets Based On Lookup Results

Dec 27, 2009

I want to name three sheets (sheets will have a different name every month so I refer to them below as Sheet(1), Sheet(2), and Sheet(3) (Their sheet index)) using a vlookup in VBA. I want the code to promt the user for input and based on the entry, I want to use a vlookup to name the sheets. There are three "lookup tables" on a sheet named "Ref" that I have defined as Table1 (used for naming Sheet(1)), Table2 (used for naming Sheet(2)), and Table3 (used for naming Sheet(3)) that I want to reference in the code. I also want a code to check the first MyLookUp1 which I have attemped below, but I do not know if I am doing this correctly. Below is my bad VBA attempt so you get an idea of what I am trying to do. Also, attached is an exaple file.

Sub namesheets()
Dim MyEntry As Variant
Dim MyLookUp1 As Variant
Dim MyLookup2 As Variant
Dim MyLookup3 As Variant
Dim MySTRING As String
MyEntry = Application.InputBox( _
Title:="Lookup sheet name", _
Type:=2)
MySTRING = MyEntry
MyLookUp1 = Application.VLookup(MySTRING, Table1, 2, False)
If IsError(MyLookUp1) Then Exit Sub
Sheets(1).Select
ActiveSheet.Name = MyLookUp1....................

## Number Enter Creates New SHEETS And Auto Assign Names

Jun 29, 2007

B20 is the number that the user enters. It can be any number and it will create new sheets below and text of sheet name in B21:F21.

If i change the sheet name below, B21:F21 would also automatically change.

## Pivot Table Report Was Saved Without Underlying Data?

Aug 19, 2013

I have a file with lots of pivot tables which works fine. But if I save and reopen the file then I get the following message when I try to change a filter on any of the pivot tables:

"The pivottable report was saved without the underlying data"

(ideally without refreshing all pivot tables using a workbook open event as this will cause a 10-20 second time-lag)

p.s. the file used to work fine before I added an extra pivot table (there are roughly 10 pivot tables in the file)

## Loop Through Specific Ranges From All Sheets To Summarize Data In Report Sheet

Jul 1, 2012

i attached link to sample and its contains employee sheets and Report sheet ,and in Report sheet there is comment

[URL]

## Copy Data Based On Date Entered By User For Report Across Multiple Sheets

Jun 20, 2006

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..

## Macro To Run Vlookups On Pivot Table Results

Jul 14, 2014

I'm trying to write a macro that will look up information about clients that result from a pivot table. Depending on what two inputs are put into the pivot table (coverage, existing or target company) the client names that the vlookup needs to reference will change.

The vlookup information for each client would paste into a different cell on another worksheet, and change depending on which clients came up in the pivot table. So the macro would need to look up clients from a specified range that the pivot table is in.

## Macro To Run Vlookups On Pivot Table Results?

Jul 14, 2014

I'm trying to write a macro that will look up information about clients that result from a pivot table. Depending on what two inputs are put into the pivot table (coverage, existing or target company) the client names that the vlookup needs to reference will change.

## Pivot Table Report Daily Data & Group Same Days In Year

Feb 23, 2008

Last week I posted a question related to formatting a cell to return a Day of the Week versus a numerical representation IE "Wed" instead of 02/20/2008 12:00AM. The solution provided worked for me:

1) Format cell to DDD MM/DD/YYYY HR:MN. Cell range (A1:A500)
2) Format destination cell with DDD. Cell range (B1:B500)
3) Destination cell (B1) = to original cell A1
4) B1 displayed data as "Wed"

However, the issue I still have is; I wanted to create a pivot table summarizing a year activity by Day of Week (in other words 7 entries for the year) and the pivot table still recognized all the MM/DD/YYYY. I ended up with a table displaying every day of the year instead of a yearly summary by Day of Week. Is there some way to strip out all the other numerical data from the new column I created to run a pivot table by Day of the Week for a whole years activity?

## Search Sheets For Data Match, Paste Results

May 13, 2009

I have a worksheet named "List" with static values in columns A & B. I want to search all the other worksheets in the same workbook for the one that matches the value I entered in column B on the "List" sheet. The cell on the other worksheets with the possible match is B5. When it finds a match, I want it to take the data from cell B3 on that worksheet and paste it in Column C of the "List" sheet on the corresponding row (all rows in the "List" sheet have a different value in Column B).

In other words, when "List"b7 = worksheetb5 , then worksheetb3 is pasted to Listc7.

## Match Values Between 2 Sheets, Copy All Corresponding Data & Sum Results

Feb 2, 2009

I want to locate the corresponding acct number between worksheets “primary_data” and “qty_movement” and once a match is found (from acct worksheet) then copy over the acct numbers and the share data to the results page. If there is no match I don’t want anything copied to the results page, just ignore that data on either worksheet “primary_data” or “qty_movement”.

•Both ranges on “primary_data” and “qty_movement” worksheet are variable, as accts are left off or kept on depending on the daily activity so the ranges are never set.
•I need to concatenate on “primary_data” worksheet cells A&B&C to get acct number
•concatenate on “qty_movment” worksheet cells B&C (need to keep zeros in front of actual numbers for acct number reasons)
•these accts need to be cross referenced as the accounts on the “acct” worksheet (pre-populated with corresponding acct numbers that never change and will always be on that worksheet)
•If a match is found on the “acct” sheet then I would like it to either replace the acct so they match on both worksheets or just to recognize those accounts correspond with each other and do the below.
•then take the 2 concatenated acct numbers off of “primary_data” and “qty_movement” in the I cells and copy them over to the “results” worksheet as well as the share data from “primary_data (column E)” and “qty_movement(Column D)” and then compare the 2 share amounts on “results(column E)”

i need to elaborate on anything, the attached sample sheet shows what i want to do, but without any formulas or code.

## Mastersheet - Auto Input Named Rows Onto Other Named Sheets

Apr 1, 2014

I have a master sheet and 102 'advisor named sheets'. The master sheet is updated daily with information (number and text) along 1 row in different columns (A:W). I would like when the advisors name is typed (W) =joebloggs! for this whole row to be auto input on joebloggs sheet. There can be multiple of these entered daily for same person, so the information would need to populate on the next row so not to type over the previous entry.

## Named Ranges On Multiple Sheets With The Same Named Range & I Cant Figure Out How To Do This

Jun 2, 2006

I need to create a named range on multiple sheets with the same named range & i cant figure out how to do this. EG :- I want to create a named range called "_SubUnitRows" on sheet1 starting from "A1:A50" & other named range again called "_SubUnitRows" on Sheet2 starting from "A1:A25" ...

## Creating A Table From Various Results Of Data Validation

Nov 17, 2006

See the attached:

1/ Need to be able to populate a table with the following data (or as illustrated in ' Sheet 1'.

Company Name, Year, Manufacturing, Non-Manufacturing

This data will need to come from the lists for each of the variables as opposed to from the table, as this will be hidden from the user. Also, more company details will be added over time.

## Show Report Results In Chronological Order

Apr 17, 2008

I'm currently working on a model that calculates the costs associated with various phases of a project. I want to allow the user to input the number of weeks that each phase requires as part of the calculations. The result will then be displayed horizontally in chronological order with the values associated with each phase underneath ex:

phase 1 phase 1 phase 1 phase 2 phase 2 phase 3 phase 3 phase 3

Through multiple lookups I've been able to get the data into this format:

phase 1 phase 1 phase 1
phase 2 phase 2
phase 3 phase 3 phase 3

What can I do to merge these series into one series that is in the order I desire (each phase will vary in length based on the user's input)?

## Hide Data Field Results In Pivot Table

May 19, 2008

I have used a pivot table with help from members here for a rota.

Now i am wondering if i can add a column to the original data which is hidden to times the number of hours worked by the hourly rate which is in another sheet.

The pivot table will be in an admin sheet with protected access so employees cant see it.

## Print Named Ranges .. Report Manager

Feb 8, 2008

I am working on building a better report manager using named ranges

1.) Loop through the workbook and find all named ranges
Output the named ranges to a worksheet

2.) Read the values from the worksheet and print each one

Seem simple enough and is really just first step
Would also like to store all print settings next to each named range to be modified by user as needed and used to format report before printing each range as needed

But I'm getting ahead of myself.
First is to print a named range

My code is below
Error msg:

Run Time Error 91
Object variable or With Block Variable Not Set

Debug points here:

Set rngPrint.Name = wsPrnt.Range("B" & lngRows).Value

1. Loop through all named ranges and output to worksheet. This part works fine:

Option Explicit

Sub GetNamedRange()
Dim nName As Name
Dim wb As Workbook
Dim wsPrnt As Worksheet
Dim Row As Long

With Application
. ScreenUpdating = False
.Calculation = xlCalculationManual
End With

## Copy Worksheet Data By Condition/Criteria To Respective Named Sheets

Jun 2, 2008

Now what I am looking to do is search my spreadsheet for specific criteria and then copy and paste these rows of information to a new sheet.

In my first spreadsheet that I am using as a database, I want to be able to select any entry from the month and copy it to another spreadsheet named for that criteria.

In other words, I want to find every entry for January, copy just that data's rows and paste them to the January spreadsheet, February to February, etc. My date fields are in column A.

I also want to do the same with representatives names found in column B.

This will break down the data for each rep and each month. Using the filter, then copy and pasting would be cumbersome.