VBA code to convert all data + formatting of entire sheet to VBA code

Change "Sheet1" to whatever sheet you want the formatting copied from. Remove the ClearContents line if you want to preserve the data on the new sheet.

Sub jocanon()
Set ws1 = Sheets("Sheet1").Copy
ws1.Cells.ClearContents
End Sub

Thank you walruseggman. That code would definitely work but what I would like to do is instead of copying the sheet, actually generate the code to write the entire sheet from scratch because I do not want to have to open another workbook to copy the sheet and the sheet I need is not in the workbook that will be open at the time. So in other words, I would like to be able to create a certain template sheet from vba code independent of anything else. For instance, if the only thing I had on the template was the word "HI" in cell A1 and "BYE" in cell A2, then what I want to be able to do is run a routine that would generate this:

Range("A1").Value = "HI"
Range("A1").Value = "BYE"

I would then be able to take this code and copy and paste it into another macro so that whenever I want I could create a new sheet with those exact contents from scratch, not by having to copy another sheet. Of course my actual template is much more complex than that, or I would just write the code out myself. It's probably wishful thinking, but it would be cool if someone knew a way, or had a vba code to do this.

Said another way, what I would like is to be able to translate all the contents, including formatting, data, formulas, links, everything exactly as it is in the existing sheet of an entire existing Excel sheet into VBA code that when executed would write to a new page all those contents and formatting, formulas, and data... without the need of copying, but just independently all on its own from scratch.

what I would like is to be able to translate all the contents, including formatting, data, formulas, links, everything exactly as it is in the existing sheet

What you describe there is literally .Copy. If you're insistent on not using .Copy, any other method would just mimic what .Copy does, so therefore it's not worth writing. The functionality already exists.

Recreating .Copy would be incredibly long and enormously tedious to write. If you're not using copy, there are hundreds of formatting variables you'd have to account for, and do so for each range that has different formatting. That is, unless you had a very specific list of just the formatting variables you want to use. But even then, it's a lot of work compared to two lines of code.

I wish you luck on getting the answer you want, but I don't think you're going to find it.

Yes, I understand. Yes, it is impractical. Like I mentioned, there are hundreds of formatting variables you would need to capture for every range that has a different format.

Anyway, just a few things to note:

• You can have the code open up a workbook and copy a sheet, this would work well on a network drive if multiple people need to access it. the user need not be involved until a new template sheet is automatically created via .Copy.
• In your hypothetical email situation, you could just email the template sheet, there's no need to re-create it from scratch. I know you said that wasn't you exact application, but still. I think that would apply to any scenario.
• I understand you want to "see the code" behind what .Copy does. But again, I have doubts that anyone would willingly try to recreate something so massive and unnecessary.

Again, I wish you luck. I you do decide to use a .Copy approach, I would be happy to help.