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

Sheets("download").Cells(7, y).FormulaR1C1 = "='" & Activesheet.Name &
"'!R6C5"
or
Sheets("download").Cells(7, y).FormulaR1C1 = "='" &
Range("whatever").Value & "'!R6C5"

Patrick Bateman wrote:
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

The syntax for getting the sheet name is:
Sheets("download").Cells(7, y).FormulaR1C1 = "=" & sheetname & "!R6C5"

This should correct your issue
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Patrick Bateman" wrote:

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

michael and JW a massive thankyou for your help, that was exactly what was
needed and works really well.

one other little thing though, i need the formula to now be coppied down the
column over z rows (z being a value in a cell) how do i select a range
starting with the cell i just inputted the data into, going down the column
to a specific row?

thankyou again for your help

regards

Patrick
"Michael" wrote:

The syntax for getting the sheet name is:
Sheets("download").Cells(7, y).FormulaR1C1 = "=" & sheetname & "!R6C5"

This should correct your issue
If this posting was helpful, please click on the Yes button.
Regards,

Michael Arch.




"Patrick Bateman" wrote:

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