-1

How would I replicate a sheet using VBA Macro but not use the VBA copy method?

So I want Sheet2 to look exactly like Sheet1 after.

I am new to VBA Macros so please guide me.

Community
  • 1
  • 1
NoobSa1b0tt
  • 1
  • 1
  • 1
  • 4
  • 2
    What's wrong with copy? `ThisWorkbook.Sheets("Sheet1").Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)` – Siddharth Rout Oct 25 '13 at 08:14
  • @SiddharthRout I don't want to use copy. can you please tell me another way? – NoobSa1b0tt Oct 25 '13 at 08:15
  • Yes there is another way as well but can you confirm why don't you want to use `.Copy`. I am actually intrigued. – Siddharth Rout Oct 25 '13 at 08:16
  • @SiddharthRout I want to see which way works better for me. Please show me the other way. – NoobSa1b0tt Oct 25 '13 at 08:18
  • 1
    Out of curiosity, can you answer Sid's question above? (i.e., why can you not use copy). I am asking because, `copy` is designed exactly for that purpose, and would like to know what is that it does not capture and in which context. Thanks! – Ioannis Oct 25 '13 at 08:26
  • quick question. do you want to make the copy in the same workbook or in another existing workbook or a new workbook? – Siddharth Rout Oct 25 '13 at 08:27
  • Ok then see my answer. Also see the recent edit that I made. You may have to refresh the page to see it. – Siddharth Rout Oct 25 '13 at 08:32
  • One difference between the .copy method and the _.sheets.add, cells.copy_ method is the latter won't copy any worksheet macros that might be stored with the source worksheet. That could be an important difference if you are concerned that the source sheets might have _malicious_, or just annoying embedded macros. OTOH, if you want to keep the worksheet embedded macros, you _have_ to use .copy. At least that's true with _my_ Trust Center settings. – riderBill Dec 09 '15 at 05:23
  • It looks like if you check the box by _Trust access to the VBA project object model_, you can write macros from a macro. Maybe even copy or delete them from worksheets or modules, I don't think a user would feel comfortable about that. Anyone tried it? I have not, do not plan to anytime soon. Although... K^J – riderBill Dec 09 '15 at 05:27

1 Answers1

7

Here are couple of ways

WAY 1 Best way to do it

ThisWorkbook.Sheets("Sheet1").Copy _
After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

Way 2

Sub Sample()
    Dim wsToCopy As Worksheet, wsNew As Worksheet

    On Error GoTo Whoa:

    Set wsToCopy = ThisWorkbook.Sheets("Sheet1")
    Set wsNew = ThisWorkbook.Sheets.Add

    wsNew.Name = "Copy of " & wsToCopy.Name

    wsToCopy.Cells.Copy wsNew.Cells

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

NOTE:

In case you are using Excel 2003, then WAY 2 might be the best way depending on the data. Please SEE THIS

Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250