I have recently devised a little program for work.
This program simply works over a 12 week cycle. The program identifies what week it is between 1 - 12, and then what day it is, and wether the shift is an AM shift or a PM shift.
Once this has been established it finds the correct Logsheet (Excel sheet) for that day and launches it.
The problem I have is that I have 144 excel sheets that all need modifiying if a change is made.
To get around this problem I have an idea to simply used one modified logsheet/excel sheet and use that as a master copy so to speak.
So within VB6 I want to select the master excel sheet and run a vb6 program that will take the contents of a selected sheet from the logsheet and copy that info to the master excel sheet.
Once the data has been taken then delete the old logsheet
Put this data into the mastercopy then rename the master excel sheet and then save it.
I want to do this by not opening either excel workbooks.
Then loop until all 144 excel sheets have been modified.
I have some code that will take the contents of a closed book and place that data into a seperate open workbook.
'//////////////////////////////////////////////////////////////////////////////// Private Sub Workbook_BeforeSave (ByVal SaveAsUI As Boolean,Cancel As Boolean) 'Put in the UsedRange Address of Sheet1 Book1.xls (this workbook) Sheet2.Cells(1, 1) = Sheet1.UsedRange.Address End Sub Sub PullInSheet1() ''''''''''''''''''''''''''''''' 'Pulls in all data from sheet1 of a closed workbook. '''''''''''''''''''''''''''''''' Dim AreaAddress As String 'Clear sheet ready for new data Sheet1.UsedRange.Clear 'Reference the UsedRange Address of Sheet1 in the closed Workbook. Sheet1.Cells(1, 1) = "= 'C:\MyDocuments\"& "[Book1.xls]Sheet2'!RC" 'Pass the area Address to a String AreaAddress = Sheet1.Cells(1, 1) With Sheet1.Range(AreaAddress) 'If the cell in Sheet1 of the closed workbook is not empty the pull in it's content, else put in an Error. .FormulaR1C1 = "=IF('C:\My Documents\"[Book1.xls]Sheet1'!RC="""",NA(),'C:\My Documents\"[Book1.xls]Sheet1'!RC)" 'Delete all Error cells On Error Resume Next .SpecialCells(xlCellTypeFormulas, xlErrors).Clear On Error GoTo 0 'Change all formulas to Values only .Value = .Value End With End Sub Private Sub Workbook_Open() Run "PullInSheet1" End Sub
The code works but not like I would like. I dont want to open 144 excel sheets to copy data from one book to another. I thought that maybe if I leave out the Open from Workbooks.Open (C:\My Documents\"Book1.xls")But that didn't work.
If anybody can may be give any pointers in the right direction, or even clearify wether this can actually be done.
Any information would be really greatful.
Merry xmas everyone.
Admin Edit: Please use code tags when posting your code. Code tags are used like so => Thanks, PsychoCoder