Macro to insert this formula automatically on start up

I need an excel sheet to automatically run a macro on click command, so
that this formula

=Sheet3!A1&""

is inserted in all cells in the worksheet.


Please advise on how i can go about doing this.

Cheers!
Sunny

Sorry. My subject says run on start up...I am okay with either a click
command or running on start up. All it needs to do is insert formula in
the sheet

To fill ALL cells in a worksheet would mean nearly 17 million formulas
- I tried it and ran out of memory.

For a lesser reange of cells, however, use this:

(Ignore the text wrapping)

Private Sub Workbook_NewSheet(ByVal Sh As Object) 'Change to whichever
event is best

ActiveSheet.Range("A1:C20").Formula = "=Sheet3!$A$1&" & Chr(34) &
Chr(34)

End Sub

If you want the references to be relative, remove the $ signs.

Hi Sunny,

Try assigning the following code to a button from the Forms toolbar:


Public Sub Tester()
ActiveSheet.UsedRange.Formula = "=Sheet3!A1&"""""
End Sub


---
Regards,
Norman



<[email protected]> wrote in message
news:[email protected]...
>I need an excel sheet to automatically run a macro on click command, so
> that this formula
>
> =Sheet3!A1&""
>
> is inserted in all cells in the worksheet.
>
>
> Please advise on how i can go about doing this.
>
> Cheers!
> Sunny
>

I am really curious as to why you would want to do this whether
it involves the entire worksheet (impossible), the used range,
or even a large chunk of cells.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

<[email protected]> wrote in message news:[email protected]...
> I need an excel sheet to automatically run a macro on click command, so
> that this formula
>
> =Sheet3!A1&""
>
> is inserted in all cells in the worksheet.
>
>
> Please advise on how i can go about doing this.
>
> Cheers!
> Sunny
>

I tried doing it to the complete worksheet. Not possible -ran out of
memory. Then I did it for a range..works fine..Why I need it, is
because I export from Access, multiple tables, which can only paste as
new worksheets...I already have an existing worksheet with graphs which
needs to pull data from there...so, I have linked the source for graphs
to that existing worksheet, and written a macro to that existing sheet
to copy from the new sheet that has been exported to excel...

I don't think i am clear enough...let me know if you understood what i
am trying...the number of records may sometimes exceed 50000, so i may
need to do this manually by copying

No I don't understand. What does having a sheetfull
of the same values get you that one cell with the
value does not. Why do you even need the sheet.

<[email protected]> wrote in message
> I don't think i am clear enough...let me know if you understood what i
> am trying...the number of records may sometimes exceed 50000, so i may
> need to do this manually by copying
>

I am looking at automating a report, so i have cells that are
referenced to a sheet with a specific name "DUMP" . Access is unable
to export data to that specific sheet, so it creates a new sheet called
DUMP1 everytime..What i need to do is put a macro, which i can execute
from access immediately after the export, so that the sheet DUMP gets
populated with all the data on DUMP1 (sheet that just came in from
access), so that the formula reference works fine.

I cannot put formula reference to sheet DUMP1, because the DUMP1 doesnt
exist, but only is created when exported. When the sheet doesnt exist,
the formula changes to #REF error.

I tried a macro and it works fine, except in cases of large chunks of
data...Maybe i will try a macro which inserts the formula in the
specific cells mentioned...
Can you please help me, as to how a macro can be asked to paste a
formula on different range of cells in a sheet, like from C1:C20 and
C25:C40 and so on..

Cheers!

David,

Another question, how do i just copy the used range?

Regards
Sunny

Hi Sunny,
How about copying the new sheet to one with the permanent sheetname.

You can probably record a macro using edit, move or copy sheet, copy sheet.

Sub Macro14()
On Error Resume Next
Sheets("permanent sheet").Delete
On Error GoTo 0
' ---Sheets("Summary").Copy before:=Sheets(1)
ActiveSheet.Copy before:=Sheets(1)
ActiveSheet.Name = "Permanent sheet"
ActiveWorkbook.Save
End Sub

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

<[email protected]> wrote in message news:[email protected]...
> David,
>
> Another question, how do i just copy the used range?
>
> Regards
> Sunny
>

This works David, thanks for the help!