I've got a possible tricky one here, i have a summary sheet with data sheets
behind it, users can press a button on the last data sheet which creates a
new sheet containing the same formulas, into which new data can be inputted.

on my summary sheet i have a section showing the totals column from each
sheet, so i have tried to include in the macro that after the new sheet is
made, a new column is put in and the formulas are entered.

I have worked out the code to use the next column along and to input the
formula into the top cell before dragging it down, but the problem lies in
naming the sheet the data comes from. my code is as follows:

Sub Macro13()

sheetname = ActiveSheet.name

Dim x, y


y = 10

Do Until x = 1

If Sheets("download").Cells(7, y) = "" Then

Sheets("download").Cells(7, y).FormulaR1C1 = "='sheetname'!R6C5"

x = 1

End If

y = y + 1

Loop

'


End Sub


where the formula is defined (where it now says 'sheetname') i want to be
able to put the active sheet name in or even text from a cell which contains
the sheet name.

any help would be much appreciated

regards

Patrick