Free Microsoft Excel 2013 Quick Reference

Need to add data from multiple sheets Results

I am looking for a simple way to add data from multiple sheets without having to individually select each sheet, select the cell, and move to the next sheet, adding them on my "master" sheet using a "+" formula.

i have a checklist. my supervisors mark off yes or no for safety compliance on a given date. i want to compile all the data for a week under a single cell while maintaining the individual supervisor's data. i have a sheet for each supervisor. Cell C2 is titled date, D2 is Yes and E2 is No. My data starts at cells C3 which has the actual date of the observation, for instance 8/20/11. Cells D3 and E3 would then have either a 1 or 0 depending on the findings. this is uniform through all sheets.

i want to add all the D3 cells within a date range on a separate sheet where i can graph a week or month or quarter of results. Using "<date>date" i know i can specify a given range, but i dont know how to do that for multiple sheets. i suppose i could use access in some way, though i am not familiar with it and would like to keep everything in excel.

any suggestions are greatly appreciated

Hi,
I want to copy data from multiple sheet and paste in a summary sheet. Every days the number of increases, from where i need to copy the data. Can we write a macro such that it automatically copy the data from all the sheet for the specified field and past in summary sheet.

Hi Guys,

First post so hello!

I have been googling and searching forums for a while and havent found a working solution to my problem.

After reading a few threads here I understand you get frustrated with posts just asking for help and not actually including enough detail to provide a sutable awnser so I will try to give as much detail as possible.

I have come across another thread here where there is code posted to read multiple text files in VBA and import specific data to an excel spreadsheet. I have managed to use this code to get the results I desire, however unfortunatly the files I need to extract are also in multiple folders (all under a specific subfolder however).

I'm using the following code..


	VB:
	
 read_text() 
     
     'Set wb = Workbooks.Add
    workingflnm = ActiveWorkbook.Name 
    i = 5 'First row in Active Sheet
    Set fd = CreateObject("Scripting.Filesystemobject") 
    pthnm = "[URL="file://gbdb1012/spparchive/SPP/110822/PRINT"]gbdb1012spparchiveSPP110822PRINT[/URL]" 'Please change to
your desired folder
    Set fs = fd.GetFolder(pthnm) 
    For Each fl In fs.Files 
         
         
        If InStr(1, fl.Name, "eodlog.spp", vbTextCompare) > 0 Then 
             
            Set Txtobj = CreateObject("Scripting.filesystemobject") 
            Set Txtfl = Txtobj.getfile(fl) 
            Set Txtstrm = Txtfl.openastextstream(1, -2) 
            Do While Txtstrm.AtEndOfStream  True 
                rdln = Txtstrm.readline 
                 
                 
                If InStr(1, rdln, "rfsruc", vbTextCompare) > 1 Then 
                    x1 = InStr(1, rdln, "^", vbTextCompare) 
                    x2 = InStr(1, rdln, "^GBVC110007^", vbTextCompare) 
                    Workbooks(workingflnm).Sheets("Log File Extract").Cells(i, 1) = fl.Name 
                     'Construction of Ohms String
                    strg = Mid(rdln, x1 + Len("^"), x2 + Len("") - (x1 + Len("^"))) 'The String picks the character Ohms in
the Line as well
                    Workbooks(workingflnm).Sheets("Log File Extract").Cells(i, 2) = strg 
                    i = i + 1 
                End If 
            Loop 
        End If 
         
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
This code will pull the data I require from the specified text file in gbdb1012spparchiveSPP110822PRINT

The folder stucture is as follows.

Root Folder
gbdb1012spparchiveSPP

Every Day a new folder is created in a YYMMDD format
gbdb1012spparchiveSPP110822

Within this daily folder is another folder called print, in here is the file i need to pull data from
gbdb1012spparchiveSPP110822PRINT

I need to be able to scan for text files in all the sub folders, i.e.

gbdb1012spparchiveSPP110821PRINT
gbdb1012spparchiveSPP110822PRINT
gbdb1012spparchiveSPP110823PRINT
gbdb1012spparchiveSPP110824PRINT
gbdb1012spparchiveSPP110825PRINT
gbdb1012spparchiveSPP110826PRINT
gbdb1012spparchiveSPP110827PRINT

Obviously this is dynamic and ever changing so I imagine I will need some kind of loop to go though all the folders in the root folder one by one till it reaches the end?

Any suggestions on how I can alter the code to acomplish this?

Many thanks,

I know this question has been asked before, but I am totally new to macros, and have no idea how to apply them to my specific spreadsheet.

I have a workbook, that has 17 worksheets attached (named after each server rack). I need to pull data from 3 columns from each worksheet, and place into my summary worksheet.

3 columns needed are: Server | Asset Tag | Rack#

An added bonus would be the ability to say, add a new server to the summary sheet, and it would update the relevant worksheet based off of the rack#.

Could someone point me in the right direction?

Hi, everybody, im new to the board and I appreciate any help at all possible. Im trying to learn excel so I can make my job a little bit easiar. Right now I have a little problem that Im wondering if anyone has an answer for. Hopefully I can explain it to the best of my ability.

Alright, I have 4 excel workbooks opened that have the Income Statement numbers for the organization I work for. It lists the numbers by account code in column A. And then it lists the dollar values for all 12 months. The 4 workbooks are for the years 2005-2004-2003-2002.

My assigment is to gather all the dollar figures for each month from each year for each account number. Then I need to add up all 4 years and divide that number by 4 to give me an average.

Is there a way on VBA or something that I can take the dollar figures from columns C:N (each month) that match up to a certain account number and add them all up and divide them by 4 to get me the average?

And if thats possible, is there a way I can list the accounts in a format that looks like this.

A B C D E
Acct #1 Total avg January Avg Feb Avg etc
Acct #2
etc

I know I probably didn't give that great of an example but if anybody understands what Im trying to do and knows how to do it, any help would be great. Thanks in advance

Paul

Hi VB Experts,
Please somebody help me to create this macro.
I am very new to VB, I need an help to create a macro which should copy the data from multiple excel files which are stored in one location and paste it into the single master file. Could you please help me to create this macro, i need this macro some how otherwise i need to work on all 500 files manually
I have 500 workbooks which are saved as the different filename, for example each file name which is saved in the name of Item number. for example CIS11-0984-01MUR02.xls, TRIAG57757Q.xls, TYC0246458.xls, CIS11-1374-01.xls, CIS11-1531-01.xls, CIS11-219285.xls, CIS25-0697-01.xls, CIS25-0723-01.xls, CIS26-0900-01.xls, CIS27-0875-01.xls etc...

Each file file contains 2 worksheets named "AWF" & "Calculations". I need to open each file for example open CIS11-0984-01MUR02.xls and copy the data from the cell "B2" from the "AWF" worksheet and paste in the master file named as "Master.xls" in the cell "A1" and again copy the Total_Liab value from the sheet named "Calculations from the same workbook which is the last cell value in the column Q(example: Q51 in the Calculations sheet(the range will vary from file to file)) and paste that in the cell "B1" in the same "Master.xls" file.
Once it perform the action it should close the CIS11-0984-01MUR02.xls file and then open the TRIAG57757Q.xls file automatically and should follow the same procedure as the CIS11-0984-01MUR02.xls . Could you please help me to create this macro.

I have got this below macro from some of the website.
It full the correct data for some files and it doesnt work for some other file it is just copying and pasting same item number and pasting in the column B instead of total liability value.

Sub Test()
Dim i As Integer
Dim strPath As String
Dim wb As Workbook
Dim NewWb As Workbook
Dim NewR As Range
Dim NewR1 As Range
Dim SavePath As String

strPath = "D:MACROAWFItem" 'Change this to path of folder with files

SavePath = "D:MACROAWFItemCombined.xls" 'Change this to the path and filename you want the
'new workbook to be saved as

Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set NewWb = Workbooks.Add
NewWb.Sheets(1).Name = "Master"
Set NewR = NewWb.Sheets(1).Range("A1")
Set NewR1 = NewWb.Sheets(1).Range("B1")
NewWb.Sheets(1).Select
If ActiveSheet.Cells(1, 1) = "" Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "Site_PN"
Columns("A:A").ColumnWidth = 23.43
Range("B1").Select
ActiveCell.FormulaR1C1 = "Tot_Lia"
Columns("B:B").ColumnWidth = 17.43
Range("A1:B1").Select
Selection.Font.Bold = True
Selection.Font.ColorIndex = 5
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
With Selection.Font
.Name = "MS Reference Sans Serif"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
End If

With Application.FileSearch
.LookIn = strPath
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute > 0 Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(.FoundFiles(i), False)
wb.Sheets("AWF").Range("B2").Copy
Set NewR = NewWb.Sheets(1).Range("A" & NewWb.Sheets(1).Cells.Rows.Count).End(xlUp).Offset(1, 0)
NewR.PasteSpecial xlPasteValues
wb.Sheets("Calculations").Range("Q1").Select
Selection.End(xlDown).Select
Selection.Copy
Set NewR1 = NewWb.Sheets(1).Range("B" & NewWb.Sheets(1).Cells.Rows.Count).End(xlUp).Offset(1, 0)
NewR1.PasteSpecial xlPasteValues
wb.Close False
Next i
End If
End With

Application.ScreenUpdating = True
Application.DisplayAlerts = True
NewWb.SaveAs SavePath

Set NewWb = Nothing
Set wb = Nothing
Set NewR = Nothing
Set NewR1 = Nothing
End Sub

Thanks in Advance,
Nirmala

Thank you in advance to taking a look at this.

I have a series of workbooks with mutliple sheets in each. One of the sheets in these workbooks has a common name "Part Info". This is the sheet I need to pull data from, part numbers, prices, location, etc.. The info I need is in cells (D24, G6, D8, E15, F35, and so on) in the "Parts Info" sheet. I want to take the data that is being pulled from these sheets and place it in an existing workbook called "Parts List" and sheet named "summary sheet". The dat on the summary sheet will be all on the same row, column A through Z. This is the only part of code I have that works. I'll have to add a part at a time after the intail setup and need the info to be place under the last row of data.

Sub Summary_Sheet()

    Dim FileName
    Dim Title As String
    Dim i As Integer
    Dim wkbTemp As Workbook
    
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

'   Set the dialog box caption
    Title = "Select File(s) to Import"

'   Select XLS files
    FileName = Application.GetOpenFilename _
        ("Excel Files (*.xls), *.xls", Title:=Title, MultiSelect:=True)

'   Exit if dialog box canceled
    If Not IsArray(FileName) Then
        MsgBox "No file was selected."
        Exit Sub
    End If

        
ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub


Hi,

Apologies for the relative simple nature of my query. I have 9 sheets of sales data, some with over 50,000 records. I need a way to present this information in another worksheet so I can bring up just the data based on individual brands. I understand a pivot table is needed however I cannot see how to gather data from multiple worksheets.

For example, I need to look at all the different sales for Product A, which appears in multiple worksheets. Product A is broken down to store-level, with records for number of sales in each store, one record for units and one for value (for the same store and product). Is there a way I can get the data for all of Product A in one worksheet so I can then easily add the totals across all stores by both units and value?

E.g.
Store 1 - Product A - 10 units
Store 1 - Product A - £20
Store 2 - Product A - 7 units
Store 2 - Product A - £14

Many help much appreciated, or if you can direct me to somewhere that can help that would be great.

Hi,

I am developing some reports in excel and was just wondering if anyone can help me ease out the very time consuming activity I am doing.

I have multiple spreadsheets which has simillar data in them filled in by multiple teams. The file names for these are also simillar with only the team name changing (e.g. PSI_2009_Team 1.xls). I need to copy data from each of these sheets automatically to a consolidated master spreadsheet. Then I can play with the date to do my reporting.

Attached are sample sheets to work on a solution.

I need data from <PSI_2009_Team1.xls>, <Team2.xls> and <Team3.xls> to be copied to <Report.xls> one below the other.

Also, I need to be able to run the macro in the consolidated report sheet whenever a new PSI_2009 file is generated to add that data into my consolidated sheet.

Hi there, I want to design a macro or code to consolidate data from multiple workbooks. I am afraid I am a complete beginner on VBA...and proper use of macro's.. So even tho I found a similar post (http://www.excelforum.com/excel-prog...worksheet.html) and had a look at the macro's I still couldnt work it out.

Can anyone signpost me to a tutorial that would help? FYI, this is for a small business my family run – I am not involved and currently the business is very small with little £ available for investing in IT systems, which is why I offered to help out. Obviously in the long run a commercial solution might be better.

What I want to do
• The data i need to consolidate are customer orders, in separate xl book’s, all saved within one folder.
• I want to consolidate data from 3 columns into a master order on one xl sheet in a new book in the same folder.
• the format of info to consolidate will be the same.
• The customer order is a form staff fill in, with quantity and product codes
• Each separate customer order could have 482 product lines from the total 2076 product lines on the catalogue
• Within an individual order, a lookup formula is used to complete the item description cell and unit price cell, checking whether the product code entered by staff matches a code in a separate xl workbook in the same folder, which is essentially the product catalogue, (the lookup goes to 65,536 product lines just in case!).
• The data in the orders to consolidate from (excl column labels which are on row A19:E19)
o ‘Qty’ A20:A482
o ‘Code’ B20:B482
o ‘Unit Price’ C20:C482
o I then in column D of the master order xls book want to multiply col A by C to calculate total line price
• However, name and number of separate customer order xl books could vary greatly, so I need to look at all excel books within a folder, excluding the actual catalogue (so maybe I should just move that file to avoid problems?)
• I thought maybe we should
o scan all workbooks for a certain code in B20:B482
o add up all the Qty values in the corresponding row in column A of the orders,
o then maybe just use a lookup function for the Unit price, to go to the catalogue in the same folder?
• Ideally I want to add a button on the master order sheet, so staff can click to run the macro or code.

Example cust order Ardvark

Qty Code ITEM DESCRIPTION UNIT PRICE LINE PRICE
5 BO1234 BO1234 broach £4.50 £22.50
100 BO5678 BO5678 bracelet £1.50 £110.50
3 AD0987 AD0987 necklace £50.00 £150.00

Example cust order Beaubangles

Qty Code ITEM DESCRIPTION UNIT PRICE LINE PRICE
100 BO1234 BO1234 broach £4.50 £450.00
10 BO5678 BO5678 bracelet £1.50 £11.50

Example Consolidated Order book
Qty Code ITEM DESCRIPTION UNIT PRICE LINE PRICE
105 BO1234 BO1234 broach £4.50 £472.50
200 BO5678 BO5678 bracelet £1.50 £122.00
3 AD0987 AD0987 necklace £50.00 £150.00

Any help or signposting to tutorials much much appreciated.

Annie

Hi,

I have three sheets. Each sheet as a column with a unique ID. All three sheets will not have the same total number of IDs.

I need to match the ID in sheet 1 and 2, then take the data from sheet 2 and add it to the row in sheet 1. Then I need to do the same for sheet 3.

So sheet 1 is like this:
unique ID Data
1 x
2 d
3 ff
4 wff
5 sdfs

Sheet 2 is like this:
unique ID sheet2Data
3 aa
1 bb
4 cc

The end result I want is:
Sheet 1:

unique ID Data sheet2Data
1 x bb
2 d
3 ff aa
4 wff cc
5 sdfs

I have no idea how to do this.

thanks for any input.

Firstly apologies to all reading this
I had trouble putting a meaningful title on this request

I have a need to add or delete rows from a number of known sheet names using a table of variables on another sheet that tell me the start row of the sheet I need to go to and the number of rows I need to either add (ie copy rows and paste / insert these) or delete (delete rows).

There are multiple blocks of data I mey need to amend on each sheet and the values in my table of variables will change on each iteration (ie if I delete rows from the first block on a sheet, the start row for the 2nd block I need to amend will need to be updated in the table of variables before I can edit the 2nd block on that sheet).

I have been able to get the process to work for a single instance (ie one sheet and amendments to the first block of that sheet) but I can't figure out how to create the loop to elegantly move to the next set of variables and repeat the process for the 2nd, 3rd, 4th block etc on the first sheet and then move to the 2nd sheet to repeat the process etc.

Code so far is below

	VB:
	
 EditCurrentBlock() 
     
    Dim rowcount As Integer 
    Dim startrow As Integer 
    Dim endrow As Integer 
    Dim rowcountBal As Integer 
     'Dim selSheet As Worksheet (tried to use this to nominate the sheet variable but
     '                                          had problems so scrapped it)
     
     'Reconfigure the GP Revenue block
     
     'Set selSheet = Sheets("GP Accs Block").Name (scrapped see comment above)
     
    rowcountBal = Range("rcGPTot").Value 
     
     'want to run a loop while rowcountBal  0
     
     'Case1 (NB- refer next 4 "Cases" after End Sub statement)
    rowcount = Range("rcGPRev").Value 
    startrow = Range("srGPRev").Value 
     
     'should run all below as a subroutine then get the next set of
     ' rowcount and startrow variables for the first sheet
     '  (then repeat the whole thing for the next 3 sheets)
     
    If rowcount > 0 Then 
         'Add rows
        endrow = (startrow + rowcount) - 1 
         'select the sheet variable here (had to hard code for now)
        Sheets("GP Accs Block").Select 
         
        Rows(startrow & ":" & endrow).Select 
        Selection.Copy 
        Rows(startrow).Select 
        Selection.Insert Shift:=xlDown 
         
    ElseIf rowcount < 0 Then 
         'delete Rows
         'select the sheet variable here (had to hard code for now)
        Sheets("GP Accs Block").Select 
         
         'Sheets("GP Accs Block").Select
        endrow = (startrow - rowcount) - 1 
        Rows(startrow & ":" & endrow).Select 
        Selection.Delete Shift:=xlUp 
         
    End If 
     
     'do a recalc here to update values on the Selections sheet before getting the next rowcount and start row
    Calculate 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The next 4 "Cases" I have (relating to the 1st sheet) all have their own paired sets of startrow and rowcount variables:


	VB:
	
rowcount = Range("rcGPCOS").Value 
startrow = Range("srGPCOS").Value 
 
 'Case3
rowcount = Range("rcGPLab").Value 
startrow = Range("srGPLab").Value 
 
 'Case4
rowcount = Range("rcGPOH").Value 
startrow = Range("srGPOH").Value 
 
 'Case5
rowcount = Range("rcGPDep").Value 
startrow = Range("srGPDep").Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Can someone Please help with this

Cheers

Johnno

Please see the file attached.
i wish to copy the data from sheet1 to Final. The data in sheet1 is in fixed "row" format, but the format of Final is in column.
i want to transfer the data in the Sheet1 like:
the field next to "Add" need to be copied to the final sheet under the "Add" column,
the field next to "Phone" need to be copied to the final sheet under the "Phone" column, etc.,

Please give me a vb code for the same.

I have incomplete lists of postcode (PC) data on multiple sheets. On a summary sheet I need to lookup each PC on each sheet and insert a new row with that data if it doesn't exist, or add the data to and existing PC row. The summary sheet can't contain duplicates.

Hi --
I posted earlier on ozgrid.com asking for help with graphing multiple rows against each other with a userform (http://www.ozgrid.com/forum/showthread.php?t=21840), and got a great response and I was able to use the basic code and incorporate it into my spreadsheet. I actually have a follow-up question -- what code would you suggest I use if I'm looking to also graph data from other sheets? The data on the other sheet would be on the same row, same column, it just needs to be a different sheet, and whenever I try to play with the SeriesCollection.Add function etc., i run into problems. I've attached a basic sample of what I'm trying to do -- ideally, when neither of those checkboxes is checked, it graphs the series chosen on sheet1; when the sheet2 checkbox is checked, it graphs the series chosen on sheet1 in addition to the equivalent series on sheet2; etc. I hope I'm being clear...Thanks a lot for your help.
--Reed

Hi all,

With great help from forum members, I have been able to use my macros as an Add-In(s), also I can now trigger them from Menu/Sub Menu from the command bar.

These Add-In(s) solve data in a workbook and give some results and I name these workbooks as ‘Sample Workbook’ i.e. one workbook for one tested sample.

I test anywhere between 6 to 100 or even more samples (no fixed limit) and each sample gives a ‘Sample Workbook’. A macro copies the data and results of relevance in the different sheets of a particular ‘Sample workbook’ to cells A1 to A10 of Sheet5 in the same workbook.

After this I collect all the ‘Sample Workbooks’ in a folder and then I have to extract data from the 10 cells (A1 to A10) of Sheet 5 of each of the workbooks in this folder and copy them to a new workbook called ‘Database workbook’ (in the same folder).

The database workbook has a particular format and the cells from the first Sample workbook in the folder are pasted to cells D10 to D20, then E10 to E20 are filled from the second, then F10 to F20 are filled from third and so on …( there can be any number of sample workbooks files)

I have found a code by forum member BrianB

http://www.mrexcel.com/forum/showthread.php?t=140187

This code extracts and copies data from all files in a folder to a new file but I am not able to change the Transfer_data procedure to suit my particular need.

The data is to be copied only from cells A1 to A10 of sheet5 and from nowhere else and has to be pasted in subsequent columns starting only from column D.

Can someone please help me with this !

Thank you and Regards
kanwer

Hi,

I need a spot of help with an obstinate macro please

I have a workbook with two sheets in, one which contains the names of other workbooks in column B and an other empty sheet I want to add data to from each workbook mentioned in column B

I want to open each workbook in turn and scroll down column A taking every entry until I reach a blank cell and putting this entry in column A of the second sheet in the workbook the macro is running in.

Here is the code I'm trying to use:

Sub GetNames()

Dim rng As Range

x = 1

Set rng = Range("B1:B10")

'Open the workbooks
For Each c In rng
Workbooks.Open ("M" + c.Value)
For y = 1 To 10000
'Check if next cell is empty
If Worksheets(1).Cells(y, 1) = "" Then Exit For
N = Worksheets(1).Cells(y, 1)
Workbooks(ThisWorkbook.Name).Activate
Worksheets("Data").Cells(x, 1) = N
Worksheets("Data").Cells(x, 2) = c.Value
x = x + 1
Next
Workbooks(c.Value).Close
Next

End Sub

The first problem is it will not open the workbook but comes back with an Application Error. I can't see anything wrong with the command workbooks.open. I've checked the file name exists and I have drive M mapped correctly!

Can anyone help pleaseeeeeee

Thanks

Chris

Hi,

I am working on a large file which takes data from different datasources to sum revenue and count entries for sales people. I've now been asked to a add a summary sheet which shows the sum of entries by customer by brand and to highlight customers that do not have any entries. The sheet already makes heavy use of SUMPRODUCT and is already running slowly. So I would like to use VB to do the summary. My VB skills so far extend mostly to copying and modifying.

The summary sheet needs to consolidate two different data sources. At the moment I am working only on the 1st data source and hopefully I can modify the results of this to work with the 2nd data source.

I am unable to share the actual file but I have created a test file which is attached.

CUSTOMER worksheet defines the customer list.

Most customer fall under an entity which has a unique entity name and client ID. An entity may have many customers or just one customer.

As there is over one thousand customer names in the 1st data source and three thousand in the 2nd data source I will use Client ID as the basis to summarize the data.

SUMMARY The Summary worksheet shows the format I am after.

I need to sum value and count entries (from EXTRACT) for customers under each entity (using Client ID) based on the following criteria;

Status of the entry is defined as Hot, Warm or Cold.
Hot = Grade one + Grade two
Warm = Grade Three
Cold = Grade Four + Grade Five

Some customer numbers have an X or Y in front of the customer number in the Extract. This happens when a sales rep has not selected a customer number in the entry system, a random temporary number is assigned until the rep fixes the entry. In this case a lookup on the customer name is performed. I prefer not to lookup on customer name as the format is not always consistent, eg. Ltd or Limited, Uni or University but I'm not sure how else to capture these entries.

For each opportunity number there may be multiple detail keys. If this is the case there will be a sub-brand code.

Once the results are returned to the summary page I need to highlight/color cells that do not return a value. (I can do this using conditional formatting if it is not worth doing in VB).

Let me know if I need to provide more detail.

I would be prepared to pay $40USD.

Many thanks

Hi,

This is my first post, hope I'm respecting all the forum rules (I am trying to be concise but clear).

I would like to use a vba procedure/procedures to achieve the following:
I have a folder with many Word2003 forms in and I want to save just the data from each form and then import the data into an Excel spreadsheet.

Currently I am opening each .doc file in turn, saving just the data to a new plain text (comma separated file) in a different folder and am unable code searching that folder for all the text files and importing them into the spreadsheet.
I have a two part question to my current approach:

1) I am 99% there with the first part (opening and converting the forms) with the following code having followed advice from another thread but I need Word open and not showing an open document. Is it possible to add code to take care of opening Word in the background and close it again after so the process is fully automated?:

Sub BatchConvertCSV()

Dim i As Integer, doc As Document, NewName As String

'search for all .doc files in specified folder
With Application.FileSearch
    .NewSearch
    .LookIn = "C:Attendance Sheets"
    .SearchSubFolders = False
    .Filename = "*.doc"
    .Execute

'open each document
    For i = 1 To .FoundFiles.Count
    Set doc = Documents.Open(Filename:=.FoundFiles(i))
'save data only from form to a plain text (default setting) file
        With ActiveDocument
            .SaveFormsData = True
        End With
'Call new file "i" so will populate new folder with files called 1, 2, 3..i etc)
'change folder where file will be saved, save file and close document
    NewName = i
    ChangeFileOpenDirectory "C:CSV Files"
    ActiveDocument.SaveAs Filename:=NewName
    doc.Close False

'do the same with the next file found from the search
    Next i

End With

End Sub
Second part:

2) Currently after I have run the code above I have a new folder full of plain text files each containing only a single line. How can I import each file into a new row in excel?
I have recorded the import process with the macro recorder but have to state the file explicitly rather than something like
With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & Filename, Destination _
        :=Range("A1"))
Extra thoughts (these are not intended to be anticipated solutions just things I have struggled with as ways around my problem):
- Would this process be better achieved virtually without actually creating a folder full of new files? How would I do that?
- Would it be better, since it is a text file that I want to write the data to, to write data from each form to a new line of the same text file? Then I would have only 1 file to import? I don't know how to do that either though.

I know this is long winded but I am new to VBA and have spent hours playing with code (unsuccessfully) to try and figure it out and wanted to be as clear as possible.

If I am going about this all wrong please enlighten me with an approach to deal with my initial statement of what I trying to achieve.
All help gratefully received!
Matthew

Hello, Everyone!

I know this question has been asked many times and dealt with in many ways, but I don't know how to modify it for my personal dilemma. I found this code from Beegee and Rylo regarding copying a single range from all sheets of all books in a folder to a single new master sheet.

Sub Open_All_Files2()
Dim oWbk As Workbook
Dim w As Worksheet
Dim sFil As String
Dim sPath As String
Dim k As Long, n As Long
sPath = "C:Documents and Settingsgl1bDesktopTacoma 2007"
ChDir sPath
sFil = Dir("*.xls") 'change or add formats
Application.DisplayAlerts = False
k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
    n = k + 1
Do While sFil <> ""
    k = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
    n = k + 1
    Set oWbk = Workbooks.Open(sPath & "" & sFil)
    Range("A13:J27").Copy
    Set w = ThisWorkbook.Sheets(1)
        ThisWorkbook.Sheets(1).Range("A" & n).PasteSpecial
oWbk.Close True
sFil = Dir
Loop
Application.DisplayAlerts = True
End Sub
I ran it and it seems to open, copy and close everything. I just don't see the pasted data anywhere.

My specifics are these:

I need to copy everything from B4 to B?? <-- wherever the data in col B runs out, C4 to C?? and N4 to N??, paste it continuously down Cols A, B and C of a new master sheet.

I have puzzled over this code for 2 days, changing things and changing them back when it didn't work. If anyone could make the necessary tweaks, I would appreciate it tremendously.

Thanks for your time.

Arlen