Create Array Set For Workbooks?

May 15, 2014

Array set for workbooks? I have more than 10 workbooks to update

refer to attachment. the VBA code is in Consolidation SOP.

View 13 Replies


ADVERTISEMENT

Copying Ranges Between Workbooks In An Array

Jun 4, 2009

I am trying to copy and paste between workbooks that are defined in an array. However, I get an out of range error when the code first reaches the line Workbooks(Wkbk(WkbkNum)).Activate.


Sub Gather_Risks()

Dim MasterRow As Integer ' Declares row number in Master Worksheet
Dim RowNum As Integer ' Declares row number in active array worksheet
Dim Wkbk(13) As String

MasterRow = 3
WkbkNum = 0

' Declare Wkbk array

Wkbk(0) = "G:Catering-RiskRegister-0409.xls"
Wkbk(1) = "G:CFO-RiskRegister-0409.xls"
Wkbk(2) = "G:Freight-RiskRegister-0409.xls"
Wkbk(3) = "G:GCA-RiskRegister-0409.xls"
Wkbk(4) = "G:IT-RiskRegister-0409.xls"
Wkbk(5) = "G:People-RiskRegister-0409.xls"
Wkbk(6) = "G:Regional-RiskRegister-0409.xls"

StartAgain:

Workbooks.Open (Wkbk(WkbkNum))

RowNum = 3

Do Until WorksheetFunction.CountIf(Rows(RowNum), "") = 0

View 9 Replies View Related

Close Workbooks Saved As Elements In An Array

Feb 3, 2010

I have an array of data type Variant, who's elements are workbooks opened by a user.

The array size is static, which for now isn't a concern but I can't work out how to close the workbooks in the array via a loop and the usual vba code of Workbooks("file").Close

Code I have that doesn't work is:

Sub Close_Workbooks_In_An_Array ()

Dim dFile (1 to 6) As Variant
Dim i As Integer, j As Integer

' // Some code to open files, set each dFile(i) as a file and then process
' // them. Max value for i is 6

j = 1
For j = 1 To i
MsgBox ("Closing: " & vbNewLine & vbNewLine & dFile(j))
Workbook.(dFile(j)).Close
Next j

End Sub

View 9 Replies View Related

Create Reports From Workbooks

Oct 21, 2009

I do testing of industrial equipment, collecting a lot of data along the way. Once I've calculated the performance metrics of existing systems, I do a cost analysis on proposed repairs or replacements.

I have a couple of goals in trying to streamline my spreadsheet process and make it more effective.

My worksheets usually end up with way more columns than I can view on the screen and that gets pretty cumbersome, so I want to develop input forms that will make data entry simpler. I've tried transposing the formats but they get even more difficult to handle that way. I just ran across the "Build a UserForm for Excel" pdf in this forum and I think that's going to help quite a bit.

Then for each of these types of projects I need to develop at least two report formats, one that will be comprehensive for each individual piece of equipment or subsystem that I'm analyzing, and another that's more of a summary of entire systems, such as a network of pumps all owned by the same customer. Ideally, this summary report would allow me to rank the subsystems by various criteria, such best ROI or most critical to operations. Format-wise what I'm looking for here is to strip away anything not relevant to decision makers, just reporting the important ID info and key results.

Finally, I'd like to be able to store all similar jobs in a single database so I can easily search through past results for comparative purposes when doing higher level analysis on new projects. I keep seeing the term "relational database" bandied about but don't really know if that's what I'm talking about or not.

View 10 Replies View Related

How To Create List Of Excel Workbooks

Nov 27, 2013

make a list of all Excel Workbooks in specific folder(My Documents). And once clicked, it will open the file immediately.

View 10 Replies View Related

Create Pivot Table From Different Workbooks?

Jun 17, 2014

For example...

I have data for monday.. Tueday ..Wednesday as individual excel

All the excel have same column names and dynamic values.

Is there a way i can create one pivot with different excel as source

View 1 Replies View Related

Create List Of Open Workbooks

Dec 10, 2009

create a list of all open workbooks? I've found various examples of code that will do this in a Message Box, but what I am after is for them to be listed actually in the worksheet (let's say in column AA).

View 5 Replies View Related

Create Query Of Mutiple Workbooks

Jul 27, 2006

I have a workbook that query's our case tracking database and pulls data into a csv. I am trying to write something that would do the following:

1) Prompt the user to enter in a case number
2) Open up the following file:
-"C:Documents and SettingsDetails.csv"
3) Look through A:A for a match of the user entered number
4) If a match is found then pull the data from cell B in that Row
5) Output the data (from row b)into a text box on the user form along with the original entry made by the user.

View 2 Replies View Related

Create Master Workbook From Multiple Workbooks

Jul 3, 2013

I need to consolidate a lot of information from multiple workbooks all the workbooks are located in a folder, i am not bothered about running each one separately or a group at a time, each work book has ten sheets with each sheet in the workbook being different, it needs to add to the next blank row on each sheet.

View 1 Replies View Related

Using Autofilter Values To Create New Worksheets Or Workbooks

May 13, 2014

I often have a table (sometimes formatted as a table, sometimes just data arranged like a table but not formatted as a table in Excel) where I'd like to create individual worksheets (eventually workbooks) based on the information in one of the columns. Take for instance the attached file. I'd like to create a macro to create worksheets (or preferably workbooks) based on data in the Region column. So workbook 1 would be something like North Region Sales 2014, and contain only the data for the North region. Workbook 2 would be something like South Region Sales 2014, and contain only the data for the South region, and so on. Sometimes I might need to create these based off the Region field, another time I might need to do it based off of the Salesperson.

Region Sales 2014.xlsx

To do this currently, I'd use the filter and unselect whichever data I want to keep and then delete all the remaining (visible) rows. When I unfilter, I'm only left with the data I want. This works, but it takes a long time when working with 50 or more "Regions" and large amounts of data.

View 14 Replies View Related

Create Multiple Workbooks From A Single Workbook

Jun 3, 2009

On a weekly basis, I receive a single worksheet in a workbook that contains ~30,000 rows of product sales.

Row one contains column headings that is unique to all other rows. Column A contains the store number that sold the product. There are ~50 unique store numbers.

I am trying to create a macro that will break the report up into seperate workbooks.

For example, assume in column A there are 30 rows of data for 3 different store numbers (say store 112, 386, & 798, each with 10 rows of data). I want to create 3 new workbooks and include the same column heading for each. For example, name new workbooks as follows: "Store 112", "Store 386", "Store 798".

View 7 Replies View Related

How To Create New Workbooks Based On Validation List

Feb 22, 2012

Every two weeks: During Payroll, we have a list of managers and specific employees under that manager.

The managers name is in Column A and they repeat.

Normally we filter that manager, copy and paste the data into a new Workbook, save it, and send the info out to that manager. We do not want other managers to see the information provided to the specific manager.

This is a long tedious process as there are close to 30 managers.

1. Is there a way to run a macro (presumably based on a validation list) to filter, copy the worksheet into a new workbook for each of the 30+ managers?

2. Is there a way to save each with a specific name like "Payroll Feb11 - Manager Name.xlsx"

View 9 Replies View Related

Combine Workbooks And Create Pivot Table

May 4, 2014

I have 16 different workbooks for different regions of my company. Each workbook has one worksheet (sheet named each region name), and all workbooks are formatted same regarding column names. Ultimately, I would like to combine all workbooks into one summary workbook where I can build a pivot table and pull out various data. Do you think this method is the best way to do what I am trying to do? I am very novice when it comes to VBA. I have written the code below, however, it is not working.

Code:
Sub GetSheets()
Path = "C:UsersDanielleDesktopWorksheets"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub

View 8 Replies View Related

Create Workbooks & Save-Name By Sheet Tab Names

Oct 10, 2006

Every month I work on an audit that has data from 35 different distributors. I have code below that puts each distributors audit/sales information on a new tab and each new tab is given the distributors name. This also creates a new workbook for each distributor.

When the new workbooks are created, how can I name each workbook with the distributor name it's being created for? Is it possible to predefine a file path to where these new workbooks will be saved?

The distributor names I'm using are in column AF.

Sub FormatList()
'The code below creates and names a new tab for each members info
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")


'extracts a list of member or distributor names
ws1.Columns("R:R"). AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("T1"), Unique:=True
r = Cells(Rows.Count, "T").End(xlUp).Row

'set up Criteria Area
Range("U1").Value = Range("R1").Value

For Each c In Range("T2:T" & r)
'adds the member name to the criteria area above
ws1.Range("U2").Value = c.Value
...............

View 9 Replies View Related

Create Workbooks & Worksheets For Each Group In Table

Jan 17, 2008

I have a workbook that contains one worksheet with data. This data has 7 columns and is a database metadata report.

The columns are as follows:

Table
Joined Tables
Column Name
Column Alias
Column Description
Column Data Type
Column Length

From this data, I am looking to have a script that automates the creation of a new workbook for each unique value in the Table column (i.e. tablename.xls) and saves them to my local drive. Each workbook then would have x number of worksheets named joined table 1, joined table 2, etc that relate to the name of the table in the original Table column. Finally, each worksheet will contain the related Column data for each Joined Table as mentioned above.

View 9 Replies View Related

Excel 2010 :: Create Database That Contains Data Taken From Several Workbooks?

Mar 8, 2014

I am trying to create a database that contains data taken from several work books that i have saved in one folder on my hard drive. These workbooks contain quite a lot ** data, but i only want to extract the data i want to instead ** having to extract it all? these works books are used by several other people as well, so i want it to be able to update as data changes.

I was thinking about using data links, but that only seems to extract all the data instead ** just what i want?

View 1 Replies View Related

Create Common Dropdown Lists Across Multiple Workbooks?

Apr 28, 2014

I expect to deploy over 200 iterations of a workbook (templates if you will) containing a mix of up to one hundred different attributes each with its own list of valid values. My problem is the attributes don't always default to the same column in every template workbook. Therefore, to make things easier for users I would like to create attribute valid value dropdown lists on the fly using the "Valid Values' table as the source data (see the attached). Since the attribute names will be the same from one template workbook to the next, I wanted to come up with a macro that would reference a source table (a separate workbook) containing all valid values for all attributes which users would be required to download to their local machine.

See the attached example workbooks and an example of a "Valid Values" list. Here's what needs to happen: When a user opens template workbook #1 I need drop down lists containing the valid values for the attributes contained in cell B3 (User Code),C3 (Department) and D3 (Category). Likewise, when another user opens template workbook #2, I need drop down lists containing the valid values in cell B2 (Department), C3 (Group), D3 (Category), E3 (Level), F3 (Material) and G3 (User Code). Notice that "Department", "Category" and "User Code" are data elements common between both workbooks but resident in different columns.

View 2 Replies View Related

Macro To Create New Workbooks Based On Values In A Column

Jun 27, 2014

I have data in Column C that looks like this:

John
John
John
Ted
Ted
Ted
Gary
Gary

Is there a macro that can create workbooks based on this data. So, i'd have one workbook that would contain all the Johns and all the other data in Columns A -Z, then another workbook for Ted, and so on...

The number of workbooks I need is not fixed. It all depends on how many names are in column C.

View 14 Replies View Related

Create Workbooks Based On Unique Values From A Column?

Oct 9, 2012

I have a workbook with about 10000 rows of data for about 100 suppliers in Sheet1 and about 15000 rows of same Suppliers payment details in sheet2.

What I am doing now is:-Filter every supplier names in Column A of sheet1, copy all the rows and paste in sheet1 of a new workook & Again copy the Payment details of a same supplier from master file sheet2 and paste the same in sheet2 of this new workbook then save the files with the supplier name as file name in my documents folder code, that filters each suppliers data from sheet1 & 2 and paste in new workbook sheet1 & 2.

View 1 Replies View Related

Macro To Create Multiple Workbooks Based On Data

Jul 31, 2013

I have a worksheet with the following columns:

Brand
UserName
FirstName
LastName
Email

[Code]....

This is a relatively long list - 1000's. What I'd like to do is create a macro that sorts by brand, and at every change in Brand, copy the user details (Username, firstname, lastname, email) to another workbook with those labels at the top of the table. Upon completion, it saves the workbook with the name of the appropriate brand.

E.g. I'd have 3 workbooks:
- BrandA.xlsx - with the 2 user listed
- BrandB.xlsx - with the 1 user listed
- BrandC.xlsx - with the 1 user listed

View 3 Replies View Related

Create Multiple Workbooks With Data From Single Workbook

Mar 18, 2014

I have a TEMPLATE workbook that has 106 cells (all in the same worksheet) that need to have data input in them.

I have a separate DATA workbook with 3,000 rows of data, each row has 106 columns that correspond to the cells in the TEMPLATE workbook.

I need to create 3,000 new workbooks that are populated with the data from the DATA workbook.

View 3 Replies View Related

Create Multiple Workbooks With Names Based On List

Sep 2, 2007

I have some very tedious work to do in Excel:

table looks like following:
DepID name function
S1 a YY
S1 b XX
S1 c ww
S2 d oo
S3 e ii
S3 f ll
S4 t mm
. . . . . .. . . .
. . . . . .. . . .
. . . . . .. . . .

S7999 u ee
S7999 w aa

My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?

View 9 Replies View Related

Create New Workbooks From Filtered List Then Autofit Columns

Jan 16, 2008

I used the code below to successfully create workbooks from the filtered list using the below code provided on this forum (see link below). The only problem is that I want the resulting columns to fit to width.

Looping Through A Range - Use Result As Criteria In A Filter

View 9 Replies View Related

Create An Array In VBA

Dec 20, 2006

I am trying to create an Array, and have searched through many threads on this website and realise I don't have a basic understanding of Arrays. Specifically the ReDim Array function.

View 2 Replies View Related

Create Associative Array

Jan 25, 2013

I have a doubt, in VBScript for excel is possible create an associative array?, for example:

PHP Code:
Dim columnValue(26) As Variant    columnValue("A") = 1    columnValue("B") = 2 

So if I want to know the value that corresponds to the letter B the result must be: 2

MsgBox columnaValor("B")

View 2 Replies View Related

How To Create Array Formula

May 24, 2014

I have attached a spreadsheet of data that I would like to reduce.

In the workbook there is a set of data (rows 1 - 24) which are defined with random dates (row 2) and number criteria column A.

In the table below that (rows 28 to 37) I have set out how I would like to present the data. how to create an array formula that sums the above data months into the criteria of column A.

View 5 Replies View Related

Create A Table From An Array

Jan 6, 2009

I have an array which I would like to extract the information and put it in a table in a spreadhseet. Does anybody knows a faster or better way to do it different than using a for next?

View 2 Replies View Related

Create Array From Row By Row Evaluation?

Apr 15, 2014

As part of a larger formula, I need a way to evaluate a range row by row and generate an array with the results. More specifically, I need to evaluate whether at least one of the cells in a row has a specific value.

So given the following table:

1
1

1
2

2
1

2
2

3
3

When querying the table for the value "1", it should result in the array {TRUE, TRUE, TRUE, FALSE, FALSE}.

How can I achieve this?

View 4 Replies View Related

Create Subfiles From Master File Using Key To Split Workbooks To Separate Files

Jun 13, 2013

I am trying to make my macro more dynamic for a spreadsheet I am working on. I have this code which splits out worksheets (using the specific names of the worksheets-not dynamic) from a main workbook into template workbooks (which are basically just an empty workbook because I didn't know how else to do it) and then saves them using the paths below. I would like to make this more dynamic by splitting the different worksheets into new workbooks based on a key column in the hierarchy worksheet.

Basically I have a list of accounts in column B with the file name they should be exported to in column A. There are about 30 accounts being split into 6 different region files. Also note that the same account might be listed multiple times in column A (needed to add multiple numbers for other lookup formulas in the worksheets) but that account worksheet will still only be going to one of the six region files and not to multiple regions. After these are copied to an individual file I would like to save it to a location on my computer. All files will go to this location.

VB:
Sub Create_Subfiles()

Dim FDMName As String
Dim FBName As String
Dim DIYName As String
Dim WMName As String

[Code] .....

View 3 Replies View Related

Create Multiple Workbooks From One Single Workbook Based On Customer Code?

Jul 6, 2008

I have an excel file which contains following data in it.

Col-A Col-B Col-C ......
Cust Cd Name Sales
=======================
101 AAA 1000
101 AAA 500
101 AAA 3000
102 BBB 800
102 BBB 200
103 CCC 200
103 CCC 200
103 CCC 200

I need to create following three workbooks with name based on Cust Cd from above excel file.

Workbook - 1 : 101.xls which contains records only pertaining to Cust Cd 101.

Workbook - 2 : 102.xls which contains records only pertaining to Cust Cd 102.

Workbook - 3 : 103.xls which contains records only pertaining to Cust Cd 103.

View 4 Replies View Related







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