Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting values from many worksheets
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" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting values from many worksheets
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" wrote in message ... 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" wrote in message oups.com... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting values from many worksheets
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting values from many worksheets
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting values from many worksheets
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" wrote in message oups.com... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting values from many worksheets
Look at it after work Greg
-- Regards Ron de Bruin http://www.rondebruin.nl "GregR" wrote in message ups.com... 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting values from many worksheets
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" wrote in message ups.com... 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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting values from many worksheets
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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Exporting values from many worksheets
Ron, msoLastModifiedAnyTime (default) would work. TIA
Greg |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting calculated values | Setting up and Configuration of Excel | |||
Exporting multiple worksheets as CSV | Excel Programming | |||
Exporting multiple worksheets as CSV | Excel Programming | |||
Exporting Access data to different worksheets on the same workbook | Excel Programming | |||
Help exporting worksheets/data to a workbook. | Excel Programming |