Nigel
12/18/2006 7:56:00 PM
Ok this one I do Know how to do, I do this a lot with spreadsheets,
retreiving data from one source then coping the data to another worksheet
here is a sample of the code, I realsise that it could more than likely be
imporved on but you can use this as a starting point.
in the code it points to a folder on the c drive called spreadsheets that is
where it saves the new spreadsheet. I left all the formatting code in but you
can get the general idea,
I use this method which does it for select sheets and I have another method
where it will get every spreadsheet in a workbook and then copy it to a new
one without any formulas etc
CopySheet:
strDate = Format(Date, "dd-mm-yy") & " " & Format(Time, "h-mm-ss")
Sheets("Book Bill Back All Sales").Activate
ActiveSheet.Range("A1").Select
Dim NEWWORKSHEET As String
'Create a new workbook and copy Report 1 to Sheet 1
ActiveSheet.Range("A1").Select
ActiveSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
ActiveSheet.Range("A1").Select
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveWorkbook.ActiveSheet.Range("A1").Select
ActiveWorkbook.ActiveSheet.Range("E7:H7").Select
With Selection
.HorizontalAlignment = xlCenter
End With
Selection.Merge
ActiveWorkbook.ActiveSheet.Range("J7:M7").Select
With Selection
.HorizontalAlignment = xlCenter
End With
Selection.Merge
ActiveWorkbook.ActiveSheet.Range("E6:O6").Select
With Selection
.HorizontalAlignment = xlCenter
End With
With Selection
.HorizontalAlignment = xlCenter
End With
Selection.Merge
ActiveWorkbook.ActiveSheet.Columns("A:A").Select
Selection.ColumnWidth = 27
ActiveWorkbook.ActiveSheet.Columns("B:O").Select
Selection.ColumnWidth = 12
ActiveWorkbook.ActiveSheet.Columns("D:D").ColumnWidth = 1.29
ActiveWorkbook.ActiveSheet.Columns("I:I").ColumnWidth = 1.29
ActiveWorkbook.ActiveSheet.Columns("N:N").ColumnWidth = 1.29
ActiveWorkbook.ActiveSheet.Range("A5").Select
ActiveCell.FormulaR1C1 = todate
ActiveWorkbook.ActiveSheet.Range("A5").Select
With Selection.Font
.Name = "Arial"
.Size = 14
End With
ActiveWorkbook.ActiveSheet.Name = "Book Bill Back All Sales"
NEWWORKSHEET = "Your Report Name" & strDate & ".xls"
ActiveWorkbook.SaveAs "c:\spreadsheets\" & NEWWORKSHEET
Workbooks("TLC Reporting.xls").Activate
Sheets("Book Bill Back Outside Sales").Activate
ActiveSheet.Range("A1").Select
ActiveSheet.Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Workbooks(NEWWORKSHEET).Activate
ActiveWorkbook.ActiveSheet.Range("A1").Select
ActiveWorkbook.Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
ActiveWorkbook.ActiveSheet.Range("A1").Select
ActiveWorkbook.ActiveSheet.Range("E7:H7").Select
With Selection
.HorizontalAlignment = xlCenter
End With
Selection.Merge
ActiveWorkbook.ActiveSheet.Range("J7:M7").Select
With Selection
.HorizontalAlignment = xlCenter
End With
Selection.Merge
ActiveWorkbook.ActiveSheet.Range("E6:O6").Select
With Selection
.HorizontalAlignment = xlCenter
End With
With Selection
.HorizontalAlignment = xlCenter
End With
Selection.Merge
ActiveWorkbook.ActiveSheet.Columns("A:A").Select
Selection.ColumnWidth = 27
ActiveWorkbook.ActiveSheet.Columns("B:O").Select
Selection.ColumnWidth = 12
ActiveWorkbook.ActiveSheet.Columns("D:D").ColumnWidth = 1.29
ActiveWorkbook.ActiveSheet.Columns("I:I").ColumnWidth = 1.29
ActiveWorkbook.ActiveSheet.Columns("N:N").ColumnWidth = 1.29
ActiveWorkbook.ActiveSheet.Range("A5").Select
ActiveCell.FormulaR1C1 = todate
ActiveWorkbook.ActiveSheet.Range("A5").Select
With Selection.Font
.Name = "Arial"
.Size = 14
End With
ActiveWorkbook.ActiveSheet.Name = "Book Bill Back Outside Sales"
ActiveWorkbook.Save
ActiveWorkbook.Close
"Wayne Knazek" wrote:
> I'm not looking for volumes of info on this one. (Um, that doesn't mean I
> wouldn't appreciate it, though! LOL) I just need a jump start so I can
> figure out what to do to get started.
>
> I have a workbook with about 50 sheets. 4 of the sheets get data, dumped
> from a program. That data is carried to other sheets as needed.
>
> These "other" sheets basically just have a formula in each cell, so data is
> pulled from the main sheet it's "connected" to.
>
> Now that the workbook is working great, I FINALLY get feedback from our
> Engineering dept. They have 2 concerns . . .
>
> The dilemma . . .
>
> 1). If they send the finished workbook to the customer, the customer can
> view any/all formulae in the cells. They don't want the customer to see any
> formulae.
>
> 2). The size of the file is fairly large. Because not only are the final
> Engineering reports part of the workbook, but the sheets that do the "math",
> and our internal reports are all a part of the same workbook.
>
> So . . . they want to delete all but the 5 or 6 sheets with "their" data.
> But if they do . . . the source sheets supplying the data go, and their
> sheets end up with nothing in them.
>
> The solution(s) . . .
>
> I know I can hide formulae. Haven't yet gone in and tried to figure out
> how. But I know I can do that. So that part shouldn't be an issue. But . .
> .
>
> They can't delete the source of the data! So what I'd like to do is . . .
>
> Make another workbook with just the Engineering portion. Then, once all the
> calculations are done, and the data is in the appropriate Engineering sheets,
> have the data go from the Engineering sheets in the main workbook . . . to
> the copy of the workbook. But just the values . . . not the formulae.
>
> As in, EX: Sheet D2 gets data from one program, does some calculations, and
> puts the results into a column in that sheet. The data from the colum with
> the calculated results is sent to sheet E2. How? Sheet E2 has a formula in
> each cell pulling that data over. You know . . . if something is in this
> cell on that sheet, put it here.
>
> Now comes workbook 2. I'd like sheet E2 of new workbook to receive the
> values of main E2. Without having a formula in every cell in the new E2.
>
> So when it's done, Engineering can send the new workbook to the customer,
> and still have the "working" workbook for reference.
>
> My best guess is . . . have all the cells in the heading section of new
> workbook use the formula to pull identical info (text) over, so the heading
> is done identical to main workbook. Use the "hide formula" function for the
> heading. Then somehow have all columns in main workbook sent over to new
> workbook, without forumlae in each cell of new workbook. (Ugghhh!)
>
> Does that make sense?
>
> :)
>