PDA

View Full Version : Create Summary Sheet then export that to a Master Workbook



Esposa
01-19-2009, 11:50 AM
I have a workbook with 22 pages the first page is a Employee Assessment plan with their personal details on and details of when they will e assessed and on what they will be assessed in a 2 year period. Sheets 2 to 21 are all individual units thet will be filled in by assessors once the assessment has taken place. The final page is a summary sheet.

I need to create some VBA that will copy any row that has NO in column F to the summary sheet. This workbook will be updated monthly over a two year period so the information only requires copying once if that makes sense.

I then need some VBA that will loop through all the closed workbooks collating the summary sheets into one spreadsheet to enable reports to be created. It would be great if I can get the code to fit in the the master soreadsheet we currently update manually but I am not sure that would work because it requires each member of staff to be updated under their own name everytime.

It seems I can only upload one file at a time so if anyone needs/wants to see the national record worksheet I will have to forward it.


I am new to VBA and don't have a clue where to start, can anyone point me in the right direction?

lucas
01-19-2009, 12:24 PM
I'll try to help get you started. This is fairly major project so if you're not willing or interested in learning how to do it yourself you can check out the site links and select consulting to find someone for hire to do it for you.

The following code(with attached example) will search column A of all sheets except the search sheet and if it finds the string you type into the msgbox it will copy that row to the search sheet.

You can alter the row in the code, it is well commented and instead of using the msgbox you can hard code the word no instead of using the messagbox return which is WhatFor.

If you have questions just post them here.

Option Explicit
Option Compare Text '< ignore case
'
Sub SeachSheets()
'
Dim FirstAddress As String, WhatFor As String
Dim Cell As Range, Sheet As Worksheet
'
WhatFor = InputBox("What are you looking for?", "Search Criteria")
If WhatFor = Empty Then Exit Sub
'
For Each Sheet In Sheets
If Sheet.Name <> "SEARCH" Then
'the 1 tells it to search in column A
With Sheet.Columns(1)
'search for any part of the string in col A
Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlPart)
'comment the line above and use this for an exact match.
' Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlWhole)
If Not Cell Is Nothing Then
FirstAddress = Cell.Address
Do
Cell.EntireRow.Copy _
Destination:=Sheets("SEARCH").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Set Cell = .FindNext(Cell)
Loop Until Cell Is Nothing Or Cell.Address = FirstAddress
End If
End With
End If
Next Sheet
'
Set Cell = Nothing
End Sub

Esposa
01-19-2009, 12:34 PM
Thank you I will go and give that a go now, I am interested in learning, bought a book to try doing it myself (VBA & Macros by MrExcel and Tracey Syrstad) but it is an awful lot to take in. I don't learn very well from books either!

I can modify some coding like php and html but I have never tried starting from scratch with any.

Esposa
01-19-2009, 01:04 PM
I have that coding working perfectly thank you. Would I be able to amend that coding for the naional spreadsheet? Going to have a play with it because something I didn't think about is showing which unit each line of information was drawn from.

I am certain theres a way of copying the sheet name too (I hope lol)

lucas
01-19-2009, 01:19 PM
What national spreadsheet?

Esposa
01-19-2009, 01:32 PM
I need to collate all the summary sheets into one spreadsheet so we can use that to run reports and create charts. I have looked at the RDB Merge Add in but IT will not let us install it on any company computers.