## Copy a range of cells

To copy a range of cells you use the `ExcelRangeBase.Copy` method.  
The sample below copies a range from the Sales Report generated in Sample 8 into a new workbook.  
The code comes from sample 1.4 - [C\#](<https://github.com/EPPlusSoftware/EPPlus.Samples.CSharp/tree/master/01-Workbook worksheet and ranges/04-Fill copy and sort ranges>) / [Visual Basic](<https://github.com/EPPlusSoftware/EPPlus.Samples.VB/tree/master/01-Workbook worksheet and ranges/04-Fill copy and sort ranges>)

```csharp
//Add a new worksheet
var ws = p.Workbook.Worksheets.Add("CopyValues");

//Use the first 10 rows of the sales report in sample 8 as the source.
var sourceRange = sourceWs.Cells["A1:G10"]; 

//Copy the source range to the destination range. 
//Only one cell is needed for the destination as the size of source range determines the copied size.
sourceRange.Copy(ws.Cells["C1"]);
```

```vbnet
'Add a new worksheet
Dim ws = p.Workbook.Worksheets.Add("CopyValues")

'Use the first 10 rows of the sales report in sample 8 as the source.
Dim sourceRange = sourceWs.Cells("A1:G10")

'Copy the source range to the destination range. 
'Only one cell is needed for the destination as the size of source range determines the copied size.
sourceRange.Copy(ws.Cells("C1"))
```

From EPPlus 5.8 you can also exclude different cell properties in the copy operation:

```csharp
//Copy the same source range to C15 and exclude the hyperlinks.
//We also remove the Hyperlink style from the range containing the hyperlinks, so the blue underline is removed.
sourceRange.Copy(ws.Cells["C15"], ExcelRangeCopyOptionFlags.ExcludeHyperLinks);
ws.Cells["D19:D24"].StyleName = "Normal";

//Copy the values only, excluding merged cells, styles and hyperlinks.
sourceRange.Copy(ws.Cells["C30"], ExcelRangeCopyOptionFlags.ExcludeMergedCells, ExcelRangeCopyOptionFlags.ExcludeStyles , ExcelRangeCopyOptionFlags.ExcludeHyperLinks);

//Copy styles and merged cells, excluding values and hyperlinks.
sourceRange.Copy(ws.Cells["C45"], ExcelRangeCopyOptionFlags.ExcludeValues, ExcelRangeCopyOptionFlags.ExcludeHyperLinks);
```

```vbnet
'Copy the same source range to C15 and exclude the hyperlinks.
'We also remove the Hyperlink style from the range containing the hyperlinks, so the blue underline is removed.
sourceRange.Copy(ws.Cells("C15"), ExcelRangeCopyOptionFlags.ExcludeHyperLinks)
ws.Cells("D19:D24").StyleName = "Normal"

'Copy the values only, excluding merged cells, styles and hyperlinks.
sourceRange.Copy(ws.Cells("C30"), ExcelRangeCopyOptionFlags.ExcludeMergedCells, ExcelRangeCopyOptionFlags.ExcludeStyles, ExcelRangeCopyOptionFlags.ExcludeHyperLinks)

'Copy styles and merged cells, excluding values and hyperlinks.
sourceRange.Copy(ws.Cells("C45"), ExcelRangeCopyOptionFlags.ExcludeValues, ExcelRangeCopyOptionFlags.ExcludeHyperLinks)
```

Here is what the end result will look like:

## Copy Values without formulas

Sometimes it's useful to copy a range and remove the formulas:

```csharp
var ws = p.Workbook.Worksheets.Add("CopyValues");
//Add some numbers and formulas and calculate the worksheet
ws.Cells["A1:A10"].FillNumber(1);
ws.Cells["B1:B9"].Formula = "A1+A2";
ws.Cells["B10"].Formula = "Sum(B1:B9)";
ws.Calculate();

//Now, copy the values starting at cell D1 without the formulas.
ws.Cells["A1:B10"].Copy(ws.Cells["D1"], ExcelRangeCopyOptionFlags.ExcludeFormulas);
```

```vbnet
Dim ws = p.Workbook.Worksheets.Add("CopyValues")
'Add some numbers and formulas and calculate the worksheet
ws.Cells("A1:A10").FillNumber(1)
ws.Cells("B1:B9").Formula = "A1+A2"
ws.Cells("B10").Formula = "Sum(B1:B9)"
ws.Calculate()

'Now, copy the values starting at cell D1 without the formulas.
ws.Cells("A1:B10").Copy(ws.Cells("D1"), ExcelRangeCopyOptionFlags.ExcludeFormulas)
```

## Copy Styles

You can also use the `CopyStyles` method to copy and fill styles to a destination range.

```csharp
//Copy the styles from the sales report.
//If the destination range is larger that the source range styles are filled down and right using the last column/row of the source range.
sourceWs.Cells["A1:G5"].CopyStyles(ws.Cells["A1:G50"]);
```

```vbnet
'Copy the styles from the sales report.
'If the destination range is larger that the source range styles are filled down and right using the last column/row of the source range.
sourceWs.Cells("A1:G5").CopyStyles(ws.Cells("A1:G50"))
```

## Copy fill

*This method is available from EPPlus 8.0.* If the destination range is larger than the source range, and the number of cells in the destination range is evenly divisible by the number of cells in the source range, the values from the source range will be repeated in the destination range. For example, if the source range contains the values 1 and 2, and the destination range has 4 cells, it will be filled with 1, 2, 1, 2.

```csharp
ws.Cells["A1:A2"].CopyFill(ws.Cells["B1:B4"]);
```

```vbnet
ws.Cells("A1:A2").CopyFill(ws.Cells("B1:B4"))
```

## Naming tables and pivot tables when copying a worksheet

*This feature is available from EPPlus 8.7.*

When you copy a worksheet within the same workbook, its tables and pivot tables are given generated names on the copy (`Table1`, `PivotTable1`, and so on), which are awkward to match back to the originals afterwards.

By passing an `ExcelWorksheetCopyOptions` object to `Worksheets.Add`, you can supply a handler that is invoked once for each copied table and pivot table, letting you assign a meaningful name at copy time. The rename is routed through the normal `ExcelTable.Name` / `ExcelPivotTable.Name` setter, so formula references are updated and name uniqueness is validated.

The example below stamps out one sheet per market from a template, giving each copied table a market-specific name:

```csharp
var template = p.Workbook.Worksheets["SalesTemplate"];

foreach (var market in new[] { "US", "UK", "SE" })
{
    var options = new ExcelWorksheetCopyOptions
    {
        //Invoked for each copied table. args.SourceTableName is the name on the
        //template; args.DefaultName is the generated name (Table1, Table2, ...).
        //Set NewName to rename the copy.
        TableCopyHandler = args => args.NewName = $"{args.SourceTableName}_{market}",
        //Invoked for each copied pivot table.
        PivotTableCopyHandler = args => args.NewName = $"{args.SourceTableName}_{market}"
    };

    p.Workbook.Worksheets.Add($"Sales_{market}", template, options);
}
```

```vbnet
Dim template = p.Workbook.Worksheets("SalesTemplate")

For Each market As String In {"US", "UK", "SE"}
    Dim options = New ExcelWorksheetCopyOptions With {
        'Invoked for each copied table. args.SourceTableName is the name on the
        'template; args.DefaultName is the generated name (Table1, Table2, ...).
        'Set NewName to rename the copy.
        .TableCopyHandler = Sub(args) args.NewName = $"{args.SourceTableName}_{market}",
        'Invoked for each copied pivot table.
        .PivotTableCopyHandler = Sub(args) args.NewName = $"{args.SourceTableName}_{market}"
    }

    p.Workbook.Worksheets.Add($"Sales_{market}", template, options)
Next
```

`NewName` must be unique within the workbook; assigning an existing name throws the same validation exception as a normal rename. Leave `NewName` as `null` to keep the default generated name. Note that `GETPIVOTDATA` references are address based, so renaming a copied pivot table requires no formula adjustment.

## Copy an entire worksheet.

Sometimes it's useful to use an existing worksheet as a template when adding a new one. This can easily be done by supplying the template worksheet when adding the new worksheet:

```csharp
//To copy the entire worksheet just add the source worksheet as parameter 2 when adding the new worksheet.
p.Workbook.Worksheets.Add("CopySalesReport", sourceWs);
```

```vbnet
'To copy the entire worksheet just add the source worksheet as parameter 2 when adding the new worksheet.
p.Workbook.Worksheets.Add("CopySalesReport", sourceWs)
```

## See also

- See [Sample 1.4-C\#](<https://github.com/EPPlusSoftware/EPPlus.Samples.CSharp/tree/master/01-Workbook worksheet and ranges/04-Fill copy and sort ranges>) or [Sample 1.4-VB](<https://github.com/EPPlusSoftware/EPPlus.Samples.VB/tree/master/01-Workbook worksheet and ranges/04-Fill copy and sort ranges>)
- [Insert/Delete rows, columns and ranges](<https://epplussoftware.com/docs/worksheet-and-ranges/insert-and-delete-rows-columns-and-ranges/index.md>)