Copy sheet including VBA, Macro's and Formatting

[APML]

Hi All, I have a workbook with numerous worksheets. I want to copy one of the worksheets within the workbook to a new tab.
My question is how do I copy an entire worksheet including macro's and VBA's and formatting? I'm doing this because I need to delete
the original sheet. I've already tried to copy it but I'm finding that the macro's don't seem to work on the copied (new) worksheet.
Do I need to somehow reassign the macro's ?

 

[Jeffrey Mahoney]

When I copy a sheet as a copy within the same workbook, it makes an exact duplicate with the VBA code

Sheets("Sheet10").Copy After:=Sheets(14)

 

[APML]

When I copy a sheet as a copy within the same workbook, it makes an exact duplicate with the VBA code

Sheets("Sheet10").Copy After:=Sheets(14)

I'm not so good with VBA/ macros. Is there a way to reassign the vba/ macro from 1 sheet to another?

 

[Jeffrey Mahoney]

If I'm interpreting your question correctly, your VBA code should reference Activesheet. If you are calling other macros that serve many sheets and you want to send the sheet as a reference to them, that is possible, like this:

Sub DoingSomethingToASheet(ShtRef As Worksheet)
  Dim Cel As Range
  
  For Each Cel In .Range("A:A")
    'Looking at cells in column A
  Next Cel
  
End Sub

 

[Jeffrey Mahoney]

Show me some code and I'll tell you if you have any issues

 

[APML]

If I'm interpreting your question correctly, your VBA code should reference Activesheet. If you are calling other macros that serve many sheets and you want to send the sheet as a reference to them, that is possible, like this:

Sub DoingSomethingToASheet(ShtRef As Worksheet)
  Dim Cel As Range
 
  For Each Cel In .Range("A:A")
    'Looking at cells in column A
  Next Cel
 
End Sub

I have a problem somewhere in the original sheet that for some reason it switches the date formatting but only on this 1 sheet, I've tried everything
but can't solve the issue. So I've copied the sheet (i.e A1: AA400) to a new tab, this has fixed the problem with the date formatting.. The sheet has VBA and macro's..so I was wondering if there's a way to just resign the VBA / macro's. My original sheet is called Gaps, I was wondering if there's a way to
say reassign the macro/ vba is the new sheet (which I've called GAP (no s at the end)), then I'll delete the original sheet called Gaps.
You can probably tell by the way I'm asking this that I'm not so great with VBA.

 

[APML]

Show me some code and I'll tell you if you have any issues

Hope I'm explaining myself correctly. Basically I want this macro (which is module64) to work with the new sheet

 

[Jeffrey Mahoney]

I can tell. As I said, all macros get copied with the sheet. You could easily delete the old sheet or rename it, and then name your copied sheet as GAPS

 

[Jeffrey Mahoney]

So, that macro will work on any sheet sheet that is the activesheet. IF you need it to work on a specific sheet then you'll need to change it like this:
Sheets("GAPS").range("P127:Q127,P130:Q130, etc)