Excel 2010 :: Creating Multiple Tables In VBA And Referring To Newly Created Tables?

Jul 1, 2013

Using Excel 2010. I'm writing a macro that sets up a workbook to be used for estimating at the beginning of a project. In the code I need to create multiple tables (formerly known as "lists") in the workbook. Then later in the code I need to refer back to those newly created tables. Currently, the code that creates the table is part of a loop that creates the table on many different worksheets. The problem of course, is that I have to name the Table, and then it won't create a table of the same name on the next sheet. Then, later in the code, I need to make adjustments to the table that was just created before looping to the next sheet.

Is there a way to create a table without giving it a constant name? Or by giving it a name that builds off of other info in the sheet? For example, I would be good with the naming the table after the sheet name: "Sheet1_Table" or such.

Code:
Sub Auto_Open()
'
Dim sht As Worksheet
If Range("A1") = 1 Then

[Code].....

View 2 Replies


ADVERTISEMENT

Creating Multiple Word Tables From Excel Data?

Sep 17, 2011

I'm working on a national survey with valuable data from students. The plan is to turn around, 100 different reports to the departments with their respective students.

The issue that I have is creating multiple tables from multiple sheets in excel into a word table, any way of automating it.

What the tables look like in excel:

What I want them to look like in word, with a chart if possible:

I realize that I can just copy and paste easy table into word from excel and with a few clicks of a button, get what I want. But i'm looking for something to automate and simplify this process. There are about 300-400 variables that I need to crosstab with certain demographics.

If it matters any, I bought a software package (Q Market Research) but it doesn't make the tables look exactly how I want them to look. The data is from SPSS, those tables are flat out.

View 9 Replies View Related

Excel 2010 :: Import Data From Other Spreadsheets Into Multiple Tables?

Sep 13, 2013

I'm trying to create some vba code that will go into other .xlsx documents, pull all cells with a value in the first 30 columns and then return them as a table. Basically each work crew has a .xlsx spreadsheet containing their schedule and I am trying to bring them all together into one nice little package. If I go to import from external sources then click XML data, navigate to the folder they are in and them click show all files and pick my spreadsheet it imports nicely. Then I go to the next blank cell in column "A" and repeat for the 4 crews. Now I can filter by each crew but all 4 are visible on a single page as a table. I recorded a macro that shows everything I did, but it is not dynamic (the .xlsx source files name changes based on the month) I'm running XP sp3 with Excel 2010

Here is a copy of what I have

Code:

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Password="""";User ID=Admin;Data Source=C:Documents and SettingsUSERNAMEDesktopSCHEDULE STUF" _
, _
"FAcrewCurrent.xlsx;Mode=Share Deny Write;Extended Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet OLEDB:Registry Path=""""" _
, _
";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;J" _
, _
"et OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt " _

[code].....

Is there a better or simpler way to do this? The tables don't need to be forever linked to their previous spreadsheets so if copy/pasting is a better way than I am all for that as well.

View 1 Replies View Related

Excel 2010 :: Macro To Import Multiple Word Tables Into Worksheet

Jun 5, 2014

I am trying to take multiple tables from a Word document and import them into an Excel worksheet. Currently I have found two versions that when combined, could yield what I am looking for. The first one imports the table's data from Word, but does not maintain formatting of the table (font, colors, rows/columns etc.):

The next code maintains formatting, but only imports/pastes one table:

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

For the second one, I do not like the fact that it is calling a specific Workbook to paste into. If I could somehow maintain the ability to import/past multiple tables while keeping formatting that would be perfect. An extra bonus would be to import each table within the Word document into individual Worksheets in Excel. I am also using Office 2010.

References: [URL] .........

VBA - How to preserve source formatting while copying data from word table to excel sheet using VB macro? - Stack Overflow

View 14 Replies View Related

VBA / Creating And Attaching New Sub To Newly Created ActiveX Control?

Jun 2, 2014

After programmatically creating a new activex checkbox, I want to create a sub for it's click event. I have given the object a name, and fortunately the code for it's click event is a one-line call of another sub, but how?

The sub would look like the following except be named after the new control (obviously), and yes, it is just the number in the name that changes:

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

View 2 Replies View Related

Creating Multiple Pivot Tables Using VBA

Apr 1, 2009

I am having a lot of trouble with pivot tables in Excel 2007 VBA. I am trying to create pivot tables using macros (connected to buttons the user can press to create the pivot table) - please don't ask why, but i need to do this!!!

I used the record fuction in excel 2007 to produce macro code which will produce the required pivot tables when the user presses a button.

Unfortuanately the coding seems to work fine when i have one pivot table in a file but breaks down if i record code to produce another pivot table.

I have attached some code below (which was produced by the record function) and is intended to produce 2 seperate pivot tables (the macro submacro2 produces the 1st pivot table and the macro submacro4 lower down the page produces the 2nd pivot table). I have also indicated the point in sub 4 where the code breaks down - basically submacro4 just doesnt run!

View 14 Replies View Related

Creating Relationships Lists From Data Tables Within MS Excel?

Mar 22, 2013

how to do this, but I know it can be done.... I want to use one drop down list created via the Data Validation 'List' Criteria to then lookup data based on the selection made in this list...... this will require a number of reiterations to get to the final result......

So:

Drop Down No:

1. List: Department Function:

2. List: Sub Function List Based on Selection from 1.

3. List: Job Code, Title and Pay Grade based on Selection from step 2

how to ensure that we are able to minimise the overall workbook size due to the complexity that is required here as this is just the basics.... this will need to applicable further once this basic requirement has been fulfilled.

View 9 Replies View Related

Excel 2010 :: Using Slicers On Tables?

Apr 11, 2013

Is there any way to use slicers on a table in 2010? I've seen posts that it is available in 2013, but can't find find a definitive answer on if there is a way to get it to work in 2010.

View 5 Replies View Related

Excel 2013 :: Creating Two Tables That Can Reference With Microsoft-query

Jun 28, 2013

I don't have Excel 2013 so unfortunately I can't easily create relational data in Excel 2010. I'm looking for a solution to a design problem. I'd like to have 2 tables which I can join with Microsoft Query and run pivot table reports.

My department processes payments, both for internal clients and external clients. My Payments table looks like:

Date | Type (internal/external) | Operation (what type of payment) | Method (internet, mail, etc) | Quantity

Additionally, I have a table for Mail Opening, which looks like:

Date | Employee | Operation | Quantity

My overlapping fields are Date and Operation. Using each table individually, I can get nice pivot table reports. What I'd like to do though is be able to not just see what operations and methods were run each day with what quantities, but also to compare that to how much mail was opened. Employee and Operation is a multi-multi relationship, so when I join by date, I end up getting incorrect numbers because of problems with the data layout. I'm open to changing my data structure, as I know the way it's set up right now isn't great, but I'm having a mental block on how to redesign it. I attached a sample workbook.

View 3 Replies View Related

Excel 2010 :: Comparison Of Contents Of Two Tables

Oct 31, 2012

I have Table1 in sheet1 and Table2 in sheet2 of same Excel Workbook. The contents ( Data in columns B, C, D, E and F) of Sheet1 Assets are to be verified with respect to the contents ( Data in columns B, C, D, E and F) of the same Asset in Sheet2. If the all contents (of all columns) are matching, it should return Boolean value (True) in column G of the sheet1 or else Boolean value (False).

I am using Excel 2010 version and I do not have Power Pivot menu.

Table1:
Excel 2010
A
B
C
D
E
F

1
Asset
Prevention
Control
Detection
Mitigation
Escape, Evacuation & Rescue

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

Table2:
Excel 2010
A
B
C
D
E
F

1
Asset
Prevention
Control
Detection
Mitigation
Escape, Evacuation & Rescue

2
U1101JBM
Y
Y
Y

[Code] .....

View 4 Replies View Related

Excel 2010 :: Sum Values Between Two Dates In Tables

Jan 27, 2014

Code:
tblCluster
Jan-14 Feb-14 Mar-14
Salary 10,000 20,000 30,000

Jan-14
Feb-14
Salary 30,000

I have a table above (in red), as an example, my actual table goes out 60 months.

Where the Green Value is, I need a formula that will reference the two months above it (They could be any of the 60 months), that will then sum the salary from the table between the selected two months (including these months)

I have tried,

Code:
=SUMIFS(C6:BJ6,
tblCluster[[#Headers],[Jan-14]:[Dec-18]],">="&'Cluster Analysis'!$I$51,
tblCluster[[#Headers],[Jan-14]:[Dec-18]],"

View 2 Replies View Related

Excel 2010 :: Find All The Data Tables In Workbook?

Aug 7, 2013

I am using Excel 2010. I am digging through a workbook with 80-some worksheets. There is one worksheet with 11 data tables. When the workbook refreshes, there is a note at the bottom saying something about 21 data tables. I am not sure where the other 10 are. In an online post, someone said that the Name Manager should show them. But in Name Manager, when I filter on Tables, the list goes blank, and all the icons are the same for the other named ranges. Is there another way to find the data tables?

View 9 Replies View Related

Excel 2010 :: Grouping Information In Pivot Tables

Oct 24, 2013

I have a pivot table below: Excel 2010. All of the information within it is text, not numbers, as illustrated below as well.

Below is what I have now....

Column A, Row 1 = Red
Column B, Row 1 = Blue
Column B, Row 2 = Yellow
Column C, Row 1 = Green
Column C, Row 2 = Green

What I want it to look like is below, removing the second word "green" from column C.

Column A, Row 1 = Red
Column B, Row 1 = Blue
Column B, Row 2 = Yellow
Column C, Row 1 = Green

Is there a way to do this and do it automatically? If I was doing this in a regular excel sheet, I would have a merged cell in column C, rows 1 & 2.

View 2 Replies View Related

Excel 2010 :: Filter Table Into Smaller Tables?

Jan 4, 2014

I have a table that contains all my data and would like to filter it by company into their own tables that will stay up to date with the main table and then hopefully somehow calculate their investment returns (future stage). I thought Microsoft Query would work but I came across a problem see my other post MS Query returning data to excel some columns I cant sum looks like text

Excel 2010

View 1 Replies View Related

Excel 2010 :: Pivot Tables - Data Source Reference Is Not Valid

Jun 27, 2012

I've been trying to create pivot table in excel 2010. Unfortunately I've been getting this message "Data source reference is not valid"?

View 2 Replies View Related

Excel 2010 :: Tables - SUMIFS Formula With Structured References And Greater Than Or Equal To

Nov 13, 2013

SUMIFS with less than & more than

I found this thread which works for normal ranges

I want to Sum for Values Greater than a specific date in my formula.

This first one works for a specific date (Relevant reference in red), so say 01/01/2014

Code:
=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])

Code:

To Sum for ranges >= 01/01/2014, I have tried this below based on the thread above but Excel does not accept this.

=SUMIFS(Table2[[#All],[Basic]],
Table2[[#All],[Cluster]],Table3[[#Headers],[Company]],
">="Table2[[#All],[Start Date]],Table3[#Headers],
Table2[[#All],[Perm / Temp / Prof / ETA]],[@Company])

View 4 Replies View Related

Excel 2007 :: How To Use Query Tables With Multiple Web Pages

Apr 22, 2014

I'm trying to query a web site and export the data to a single sheet in Excel. I've used the record macro function in Excel (2007) and come up with the following but I want to query over 20 pages from this site and wondered if there was any way in looping this macro to do the hard work for me;

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.futwiz.com/en/players", Destination:=Range("$A$1"))
.Name = "players_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False

[Code] ........

On the second page the URL changes to;

URL;FIFA 14 Ultimate Team Players - FUTWIZ

and the .Name field changes to;

.Name = "players?page=1_1"

and on the 3rd page I get;

URL;FIFA 14 Ultimate Team Players - FUTWIZ

and..

.Name = "players?page=2"

View 3 Replies View Related

Creating Tables In Spreadsheet Won't Let To Insert New Row?

Apr 17, 2014

sample worksheet Antligen.xls

I have a spreadsheet that I need to add new rows everytime I have a new client. I a formula in the N column and a formula in the F column that I would like to automatically insert itself into the new rows.

The problem is this: I have arranged my table so that I can look at clients alphabetically, and according to other date... BUT now it wont let me insert a new row when I have a new client!!!

Is there anyway of keeping it so that I can insert a new row into my table, but preserve all of the formulas that I want automatically put into both the F and N column?

Attached is the sample spreadsheet.

View 1 Replies View Related

Creating Table From Data From Several Other Tables

Jan 10, 2014

My family owns a Fast Casual Middle Eastern/ Mediterranean Restaurant. I'm taking charge of making it as profitable as can be! Our plates are different combinations of kebabs, rice, Falafels, Gyros, salad, platters. So theirs tons of combinations.

I am Dead serious about knowing REAL plate costs:

I spent days entering price data, weighing meat, produce, calculating usable yields. I then built "component builders" using data validation drop-downs, and Index + Match functions to construct the costs of the recipe items,standard base items and side items. So I now know How much a skewer of Marinated Kebab Costs me, and all the Components that go into building a plate.

I've now built a "Plate Builder" (See Pic) to create the EVERY combination available at our restaurant. So when I'm done filling in all the builders, I'll have a whole lot of tables. I'd like to pull the name and plate cost from each table and create a big table organized by whether its eat in or take out. How do I do it.

TL;DR: Need to create a table that pulls data from several other tables....

View 2 Replies View Related

Creating Tables Correctly To Get Data

Aug 12, 2006

I've attached what I've done so far. I'm trying to figure out a way to capture everything. For instance, instead of using separate boxes for the different groups, isn't there a way that I can put the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend, and list the different units, and different groups. I'd like it to be sort of like the very last box where I have the different sessions and the different groups in there, but I'm not sure where I could add the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend, because the last box only formulates the percent attendance for each session by the different groups. I need it to also formulate the # Registered, # Attendance, % Attendance, % Didn't Attend, Units Didn't Attend. Is there a way this can be done without using separate boxes for each group.

View 3 Replies View Related

VBA - Creating Pivot Tables In New Sheets Using Dynamic Objects?

Oct 29, 2013

I am trying to solve in VBA how to create pivot tables in new sheets using objects instead of relying on the sheetcounter, which errors out.

I found an old Tip on Mr. Excel: Excel Create and name a new worksheet with VBA

But I can't figure out how to apply the object to the rest of the script. Here is what I have so far:

Sub Macro2()
'
' Macro2 Macro
'
Dim WS As Worksheet
Set WS = Sheets.Add
'

[Code]....

View 1 Replies View Related

Creating Pivot Tables With VBA And Putting In Tabular Form With No Subtotals

Jun 28, 2012

I have created the code below to create a Pivot Table using VBA, so that it populates from a list box option.

I would prefer if it didn't show any subtotals and was in a tabular form. It keeps dropping off at that point for each row field.

CODE
Sub trail()
'
' trail Macro
'
'
Dim wksPivot As Worksheet
Dim wksData As Worksheet
Dim pc As PivotCache
Dim PT As PivotTable
Set wksPivot = Sheets("PIVOT")

[Code] ......

View 2 Replies View Related

Can Pivot Table Be Created From Several Other Pivot Tables

Mar 5, 2014

I have 12 months worth of data in twelve separate workbooks each with a pivot table, I have copy/moved the pivot table tab from each into one workbook so now I have a workbook that consists of 12 tabs each with a pivot table. What I would like to be able to do is create a summary table with the full years data; where I am running into problems is that each months table has slightly different row and column counts and labels making any formula like =sum([sheet 1 cell a1]+[sheet 2 cell a1]) problematic.

View 3 Replies View Related

Paste Into Newly Created Tab

Jul 21, 2009

I originally posted this in the "Excel New Users forum" - i guess that was an error, but I'm very new (second post) and very new to VBA in general - so please be gentle!!

I've created a macro which opens a workbook, creates and renames a new worksheet, and moves it to the end of the workbook.

I then need to paste into this new worksheet a selection from another workbook.

How would I specify in the code that the selection needs to be pasted into the newly created tab?

View 12 Replies View Related

Paste Special Into Newly Created Tab

Jul 21, 2009

I've created a macro which opens a workbook, creates and renames a new worksheet, and moves it to the end of the workbook.

I then need to paste into this new worksheet a selection from another workbook.

How would I specify in the code that the selection needs to be pasted into the newly created tab?

The code I have so far is as follows:

Worksheets.Add.Name = Workbooks("SHEET1").Worksheets("monthly report").[p1]

ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

Windows("SHEET1.xls").Activate
Range("A1:N23").Select
Selection.Copy

However, I got a subscript out of range error pointing at the line of
Worksheets.Add.Name = .Worksheets("monthly report").[p1]

View 9 Replies View Related

Newly Created File Not Saving At The Given Location

Jul 1, 2014

I have an issue with saving the file at the desired location below is the code i have currently used . I have given the destination as "D:New folder". The new file does get saved at location "D:" but not inside the "D:New folder", instead names the file as "New folder".

View 3 Replies View Related

Saving Workbook In Newly Created Directory

Jun 18, 2009

I am trying to have the user hit a button and save the workbook in a newly created directory. Currently it creates the new workbook and the new directory with the correct names, however the workbook is not in the newly created directory.

View 5 Replies View Related

Excel 2010 :: Creating Reports With Multiple Conditions

Jun 24, 2014

I am creating a report where there are multiple conditions and I am using MS Excel 2010.

Summary sheet will display
Date of Completion - If the exam has been taken already
In Progress - if the exam has been started but not yet completed
Not Started - if the exam has not yet been taken

Available data in Source sheet
Completion_Date - Date when the exam was taken or "Blank" if the exam is still In Progress
Completion_Status - Completed & In Progress only (all subjects that are not in the Source sheet shall be tagged as "Not Started"

View 4 Replies View Related

Excel 2010 :: Unmerge Cells And Fill Down Values In Newly Inserted Column

Jul 9, 2014

I am using Excel 2010 and have the problem as shown in the attached file.

Input Sheet shows the Data I have at present
Output Sheet is the desired result.

I need a macro which should create an "Output" sheet by doing the following on the Input Sheet

1)Insert a Blank Column before Column A
2)Unmerge the Region Heading and insert the respective Region Name in the newly inserted Column. Region Heading will be in Bold Font.
3)Repeat Step 2 for all Regions
4)Delete the Rows which was merged.

Please note that the number of Data Rows will vary for each Region.

I have shown two Regions for explanation purpose only. There will be several Regions in reality.

The result is shown on the Output sheet

Merge Problem - Forum.xlsx‎

View 3 Replies View Related

Newly Created Tab Names Placed As Hyperlink On Content Sheet

May 11, 2006

I attach an example worksheet with the code I have thus far.

In my workbook I have other sheets, one of which changes and updates a specific one each time a new client's data is entered on said other sheet. Because I want to save the specific client's data and not lose it when another client's stuff is entered on this other sheet, I copy the sheet where the data is summarised (I called this sheet "Sheet to Copy From") to a newly inserted sheet and use Paste Special, Values Only to change all functions /f ormulae / Links ect to values.

I then change the name of the sheet to the name of the client.

I then use this sheet name / cell value to polulate a range on another sheet (Next Empty Cell) as a Hyperlink to the newly created sheet above. This sheet I called "Table of Contents".

how to code the hyperlink. Using Macro Recorder uses the specific case's names, but the Tab name to be used as Hyperlink value will always be the name of a new client,

View 8 Replies View Related







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