Copy table/region from one sheet to another sheet with only the data and no formulas from the first

Question

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 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.

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

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

This copies the data from MAIN to Static Data when run from any sheet apart from MAIN.
It fails when run from MAIN and leads to all contents in MAIN being deleted.

You need to select a range to paste to. Not that you want to use `select`. Also, you want paste special values? — findwindow, Nov 01 '22 at 20:46
Side note: you want to [avoid using select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Nov 01 '22 at 20:51
I've added the the k and defined i as 0 and still getting the same error — daniel stafford, Nov 01 '22 at 20:55
There were two missing k's now the data is copied correctly, I will update the question to only include the removal of formulas aspect — daniel stafford, Nov 01 '22 at 20:56

Tim Williams · Accepted Answer · 2022-11-01T22:40:15.703

3

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

edited Nov 01 '22 at 22:40

answered Nov 01 '22 at 21:51

Tim Williams

154,628
8
97
125

This works, thanks a bunch. Just to double check my understanding: `Worksheets(i).Range("A1").CurrentRegion.Copy` is copying the region from MAIN. What is the purpose of the two `.Range("A1")`? Could you explain the logic/chaining after the first substring `Worksheets(i).Range("A1").CurrentRegion.Copy` – daniel stafford Nov 01 '22 at 22:34
The `With` block is a way of shortening the code contained inside it - a way of writing `.` instead of `Worksheets("Static Data").` in this case. – Tim Williams Nov 01 '22 at 22:39
Ok, I see how that helps with readability and conciseness. I understand how the first `.Range("A1")` is essentially setting this cell within the Static Data worksheet. However I'm struggling with understand the second line `.Range("A1").CurrentRegion.Value = .Range("A1").CurrentRegion.Value` – daniel stafford Nov 01 '22 at 22:42
`.Value = .Value` is a quick way of replacing any formulas with their current values – Tim Williams Nov 01 '22 at 22:45
When I run this macro from the MAIN sheet, it fails and all the data in the MAIN sheet is deleted. It works as expected if ran from any other sheet apart from MAIN. I have updated the question above – daniel stafford Nov 02 '22 at 08:46
Your `Cells.ClearContents` applies to the ActiveSheet - if you want it to apply to `Static Data` then you need to write `.Cells.ClearContents` with a leading period. https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/with-statement – Tim Williams Nov 02 '22 at 15:51
Thanks for the help, i forgot to tick it. Could you help with a similar question ie copying a defined region as static not just a table? https://stackoverflow.com/questions/74404741/how-to-copy-an-entire-sheets-values-as-static-into-another-sheet-using-vba – daniel stafford Nov 11 '22 at 15:32

Copy table/region from one sheet to another sheet with only the data and no formulas from the first

1 Answers1