Run Array.. run array... (Which to use?)
 

Hi Alex

It seems like you have recorded a macro and modified it. The code can be
cleaned a little bit.

A few questions:
In your posted code, you are setting:
- the Active Sheet 's name to Summary.
- the header row names : where in the sheet? is it always in A2:E2?
- and the formulas, could you please give more details?.... could you give a
short sample of the final product (sheet) ?

Concerning the sheet name and setting the headers, the following code would
be more efficient (i places the header in row 2)
Dim HeaderArr()
HeaderArr = Array("Project #", "Project", "Weekly Hours", "Forecasted",
"% to Forecast")

With ActiveSheet
.Name = "SUMMARY"
.Range("A2").Resize(1, UBound(HeaderArr) - LBound(HeaderArr) + 1) =
HeaderArr
End With

Regards,
Sebastien

"Alex" wrote:

I have a spreadsheet that I've gotten half of it automated. Now I'm just
trying to automate the remaing part.

Basically what I want to do is generate a summary sheet (which I already
have the code for that completed) and then populate it with info from the
other worksheets that are in the workbook. This is just part of the code that
it's based around to generate the summary sheet.

ActiveSheet.Select
ActiveSheet.name = "SUMMARY"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Project #"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Project"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Weekly Hours"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Forecasted"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "% to Forecast"
ActiveCell.Offset(1, -4).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MZ6756!RC[1]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MZ6756!RC[1]"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MZ6756!R[66]C[3]"
ActiveCell.Offset(0, 2).Range("A1").Select
ActiveCell.FormulaR1C1 = "=MZ6756!R[68]C[1]"
ActiveCell.Offset(1, 0).Range("A1").Select

These are the things that I need it to do:

1) Pull the name of the worksheet, this is always a variable as the names
are not always the same.
2) The formula cell references will change, but will always be at the end of
the data. Same column different row, ActiveCell.FormulaR1C1 =
"=MZ6756!R[68]C[1]". The others that generate the header row will stay the
same.
3) Do a loop til all worksheets are in the summary.


I'd really like some info on Array's also, any links or info would help!!

Your help is appreciated!!


--
Jack of all trades... master of none..