Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save specific sheets to new workbooks adding tomorrow's date to na
My company refuses to use a database to collect data so I'm stuck
distributing Excel workbooks everyday with specific sheets to different offices, consolidating the inputs in one workbook, then updating the separate workbooks with the information from consolidated workbook. The piece of VBA code that I haven't figured out is copying Sheet 1, 4, 6 to workbook 1, sheet 1, 3, 5 to workbook 2, and sheet 1, 2, 7 to workbook 3 from the master and adding tomorrow's date to the end of each file name. -- I know just enough to be dangerous and lock up the computer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Save specific sheets to new workbooks adding tomorrow's date tona
On Jul 24, 8:27*am, TryingToFigureItAllOut
m wrote: My company refuses to use a database to collect data so I'm stuck distributing Excel workbooks everyday with specific sheets to different offices, consolidating the inputs in one workbook, then updating the separate workbooks with the information from consolidated workbook. The piece of VBA code that I haven't figured out is copying Sheet 1, 4, 6 to workbook 1, sheet 1, 3, 5 to workbook 2, and sheet 1, 2, 7 to workbook 3 from the master and adding tomorrow's date to the end of each file name. -- I know just enough to be dangerous and lock up the computer TryingToFigureItAllOut, You can set the code up to do more of a loop to remove some of the redundancy, but the syntax for what you are looking to do is below (or at least one way to do it is below). Best, Matthew Herbert Sub CopySheetsToNewBook() Dim wkbOne As Workbook Dim wkbTwo As Workbook Dim wkbThr As Workbook Dim varOne As Variant Dim varTwo As Variant Dim varThr As Variant Dim myDate As Date Dim intWkb As Integer varOne = Array(1, 4, 6) varTwo = Array(1, 3, 5) varThr = Array(1, 2, 7) myDate = Date + 1 Set wkbOne = Workbooks.Add Set wkbTwo = Workbooks.Add Set wkbThr = Workbooks.Add With ThisWorkbook .Worksheets(varOne).Copy wkbOne.Worksheets(1) .Worksheets(varTwo).Copy wkbTwo.Worksheets(1) .Worksheets(varThr).Copy wkbThr.Worksheets(1) End With wkbOne.Close True, ThisWorkbook.Path & "\One-" & Format(myDate, "mm-dd- yy") & ".xls" wkbOne.Close True, ThisWorkbook.Path & "\Two-" & Format(myDate, "mm-dd- yy") & ".xls" wkbOne.Close True, ThisWorkbook.Path & "\Thr-" & Format(myDate, "mm-dd- yy") & ".xls" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula to return tomorrow's date. | New Users to Excel | |||
tomorrow's date on an invoice made today | New Users to Excel | |||
How to limit 'calculate' (F9) to specific cell, range, sheets, workbooks? | Excel Programming | |||
tomorrow's date | Excel Programming | |||
tomorrow's date | Excel Programming |