Exporting values from many worksheets

Background: Hi! I have built a very big reporting and forecasting tool
that has over 30 worksheets tht link and roll into each other.

The report is HUGE and we have no central repository to share this tool
plus only a couploe of people need to actually use the tool to
forecast. Mainly, we want people to see the end results.
Question:Is there a way to export the values of each sheet (30 of them)
at one time into an empty shell of a different work book so we can use
that one to distribute?

So basiclaly, I am looking to write a macro to export the value and
formats (no formulas) of many sheets into a new workbook without having
to copy and paste one sheet at a time.

thanks for your amazing help!
Tina

See
http://www.rondebruin.nl/copy2.htm

I use a master sheet in the same workbook but you can also change it to put the info in a new workbook
If you need more help post back


--
Regards Ron de Bruin
http://www.rondebruin.nl


"dreamkeeper" <[email protected]> wrote in message news:[email protected]...
> Background: Hi! I have built a very big reporting and forecasting tool
> that has over 30 worksheets tht link and roll into each other.
>
> The report is HUGE and we have no central repository to share this tool
> plus only a couploe of people need to actually use the tool to
> forecast. Mainly, we want people to see the end results.
> Question:Is there a way to export the values of each sheet (30 of them)
> at one time into an empty shell of a different work book so we can use
> that one to distribute?
>
> So basiclaly, I am looking to write a macro to export the value and
> formats (no formulas) of many sheets into a new workbook without having
> to copy and paste one sheet at a time.
>
> thanks for your amazing help!
> Tina
>

> If you need more help post back
Bed time for me soon so here is small example to copy to a new workbook

Sub Test1()
Dim sh As Worksheet
Dim DestSh As Worksheet
Dim Last As Long

Application.ScreenUpdating = False
Set DestSh = Workbooks.Add.Worksheets(1)
For Each sh In ThisWorkbook.Worksheets
Last = LastRow(DestSh)

sh.Range("A1:C5").Copy DestSh.Cells(Last + 1, "A")
'Instead of this line you can use the code below to copy only the values
'or use the PasteSpecial option to paste the format also.


'With sh.Range("A1:C5")
'DestSh.Cells(Last + 1, "A").Resize(.Rows.Count, _
'.Columns.Count).Value = .Value
'End With


'sh.Range("A1:C5").Copy
'With DestSh.Cells(Last + 1, "A")
' .PasteSpecial xlPasteValues, , False, False
' .PasteSpecial xlPasteFormats, , False, False
' Application.CutCopyMode = False
'End With

DestSh.Cells(Last + 1, "D").Value = sh.Name
'This will copy the sheet name in the D column if you want

Next
DestSh.Cells(1).Select
Application.ScreenUpdating = True
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function




--
Regards Ron de Bruin
http://www.rondebruin.nl


"Ron de Bruin" <[email protected]> wrote in message news:[email protected]...
> See
> http://www.rondebruin.nl/copy2.htm
>
> I use a master sheet in the same workbook but you can also change it to put the info in a new workbook
> If you need more help post back
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "dreamkeeper" <[email protected]> wrote in message news:[email protected]...
>> Background: Hi! I have built a very big reporting and forecasting tool
>> that has over 30 worksheets tht link and roll into each other.
>>
>> The report is HUGE and we have no central repository to share this tool
>> plus only a couploe of people need to actually use the tool to
>> forecast. Mainly, we want people to see the end results.
>> Question:Is there a way to export the values of each sheet (30 of them)
>> at one time into an empty shell of a different work book so we can use
>> that one to distribute?
>>
>> So basiclaly, I am looking to write a macro to export the value and
>> formats (no formulas) of many sheets into a new workbook without having
>> to copy and paste one sheet at a time.
>>
>> thanks for your amazing help!
>> Tina
>>
>
>

Thanks Ron.
Will this copy all th epages to one page ina new work book?

What I am trying to do is past 30 sheets with formulas to a new
workbook and end up with 30 sheets with just value and format from
themaster workbook.
I thought i hsould explain that before I tried this!
any insight?
Thanks,
Tina

Ron, I would like a little variation of this code. I want to open files
in a dir and its subdirs that match a date criteria. In other words,
there are many files in each subdir, but I only want to open the last
revised version or it could be a specific revised date. Copy sheet(1)
of each file to a summary workbook, close the file without saving and
print the summary workbook. Let me know if you need more details. I
have the following code which finds all the files I need (hard coded
date, I would prefer latest revised date), but doesn't copy sheet(1) to
summary WB and doesn't print. TIA

Sub FindRecons()

Dim fs As Object
Dim temp_name As String
Dim i As Long

temp_name = "Rev 3_27_06"

Set fs = Application.FileSearch
With fs
..NewSearch
..LookIn = "G:\IS\IsFinancials\Greg\Project Recons\Active\"
..SearchSubFolders = True
..MatchTextExactly = False
..Filename = temp_name
..Execute

If .FoundFiles.Count > 0 Then

For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i

Else

MsgBox "No files !"

End If

End With

End Sub

Greg

> Will this copy all th epages to one page ina new work book?

Yes it copy a range from each sheet to one sheet in the new workbook


> What I am trying to do is past 30 sheets with formulas to a new
> workbook and end up with 30 sheets with just value and format from
> themaster workbook.

That is not what you ask ?

See the examples on this page
http://www.rondebruin.nl/tips.htm



--
Regards Ron de Bruin
http://www.rondebruin.nl


"dreamkeeper" <[email protected]> wrote in message news:[email protected]...
> Thanks Ron.
> Will this copy all th epages to one page ina new work book?
>
> What I am trying to do is past 30 sheets with formulas to a new
> workbook and end up with 30 sheets with just value and format from
> themaster workbook.
> I thought i hsould explain that before I tried this!
> any insight?
> Thanks,
> Tina
>

Look at it after work Greg

--
Regards Ron de Bruin
http://www.rondebruin.nl


"GregR" <[email protected]> wrote in message news:[email protected]...
> Ron, I would like a little variation of this code. I want to open files
> in a dir and its subdirs that match a date criteria. In other words,
> there are many files in each subdir, but I only want to open the last
> revised version or it could be a specific revised date. Copy sheet(1)
> of each file to a summary workbook, close the file without saving and
> print the summary workbook. Let me know if you need more details. I
> have the following code which finds all the files I need (hard coded
> date, I would prefer latest revised date), but doesn't copy sheet(1) to
> summary WB and doesn't print. TIA
>
> Sub FindRecons()
>
> Dim fs As Object
> Dim temp_name As String
> Dim i As Long
>
> temp_name = "Rev 3_27_06"
>
> Set fs = Application.FileSearch
> With fs
> .NewSearch
> .LookIn = "G:\IS\IsFinancials\Greg\Project Recons\Active\"
> .SearchSubFolders = True
> .MatchTextExactly = False
> .Filename = temp_name
> .Execute
>
> If .FoundFiles.Count > 0 Then
>
> For i = 1 To .FoundFiles.Count
> MsgBox .FoundFiles(i)
> Next i
>
> Else
>
> MsgBox "No files !"
>
> End If
>
> End With
>
> End Sub
>
> Greg
>

Hi Greg

Is this a option for you

.LastModified = msoLastModifiedYesterday

This are the options

msoLastModifiedAnyTime (default)
msoLastModifiedLastMonth
msoLastModifiedLastWeek
msoLastModifiedThisMonth
msoLastModifiedThisWeek
msoLastModifiedToday
msoLastModifiedYesterday


--
Regards Ron de Bruin
http://www.rondebruin.nl


"GregR" <[email protected]> wrote in message news:[email protected]...
> Ron, I would like a little variation of this code. I want to open files
> in a dir and its subdirs that match a date criteria. In other words,
> there are many files in each subdir, but I only want to open the last
> revised version or it could be a specific revised date. Copy sheet(1)
> of each file to a summary workbook, close the file without saving and
> print the summary workbook. Let me know if you need more details. I
> have the following code which finds all the files I need (hard coded
> date, I would prefer latest revised date), but doesn't copy sheet(1) to
> summary WB and doesn't print. TIA
>
> Sub FindRecons()
>
> Dim fs As Object
> Dim temp_name As String
> Dim i As Long
>
> temp_name = "Rev 3_27_06"
>
> Set fs = Application.FileSearch
> With fs
> .NewSearch
> .LookIn = "G:\IS\IsFinancials\Greg\Project Recons\Active\"
> .SearchSubFolders = True
> .MatchTextExactly = False
> .Filename = temp_name
> .Execute
>
> If .FoundFiles.Count > 0 Then
>
> For i = 1 To .FoundFiles.Count
> MsgBox .FoundFiles(i)
> Next i
>
> Else
>
> MsgBox "No files !"
>
> End If
>
> End With
>
> End Sub
>
> Greg
>

I will rephrase and post a new topic.

I do want to copy the value of 30 sheets from one work book to 30
sheets of another workbook that is already formatted to be exactly like
the workbook with the formulas.

Your code copies to one sheet.

Thanks Ron!

Ron, msoLastModifiedAnyTime (default) would work. TIA

Greg