VBA code to take table/region from one sheet and copy it into another sheet with only the data and n-CodePudding

Firstly, I am very new to VBA so apologies if this is trivial

My first table, named 'MAIN' (a comma indicating a different column):

1,2,3
4,5,6
5,7,9

The third row is calculated using the SUM function. I am trying to copy this entire worksheet into another worksheet named 'Static Data' but the third row should only contain the numbers 5,7,9 not the corresponding SUM formulas.

The code below, takes this data from MAIN and pastes it into the 'Static Data' worksheet. However, doesn't copy it starting from cell A1. It also doesn't remove the formulas from row 3.

This is my current code:

Public Sub CopyMain()
    Dim i As Integer
    i = 1
    Worksheets(i).Select
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Copy
    Worksheets("Static Data").Select
    ActiveSheet.Paste
End Sub

I use the i variable since MAIN will always be the first worksheet in the workbook.

This returns the following error and I'm so confused as to why:

Compile error: Sub or Function not defined

CodePudding user response：

One way:

Public Sub CopyMain()
    Dim i As Long
    i = 1
    With Worksheets("Static Data")
        Worksheets(i).Range("A1").CurrentRegion.Copy .Range("A1")
        .Range("A1").CurrentRegion.Value = .Range("A1").CurrentRegion.Value
    End With
End Sub

Edit - without the With

Public Sub CopyMain()
    Dim i As Long
    i = 1
    
    Worksheets(i).Range("A1").CurrentRegion.Copy Worksheets("Static Data").Range("A1")
    Worksheets("Static Data").Range("A1").CurrentRegion.Value = Worksheets("Static Data").Range("A1").CurrentRegion.Value
    
End Sub