CODEWITHSUNDEEP
excelcopy-pastevba
Ole Henrik Skogstrøm
Ole Henrik Skogstrøm

Reputation: 6493

Copy multiple sheets at once (keeping relative formulas)

I have been struggling for a while to find a way to copy multiple sheets at once using VBA. The catch is that i need them to keep their relative reference to each other. For example:

I have 3 sheets:
1. Print                      (formulas point to TV - Input)
2. Print - Input
3. Print - plan               (formulas point to TV - Input)

I need to copy them so that all formulas point to their new respective sheets.

1. Print (2)                  (formulas point to Print - Input (2))
2. Print - Input (2)
3. Print - plan (2)           (formulas point to Print - Input (2))

This is done easily by hand by Ctrl. + dragging the sheets to a new location. However how to i do this in VBA?

Edit: The name "Print" is set runtime. So it can also be TV or Radio. It is pased from a string.

Any help is appreciated!

Upvotes: 1

Views: 1243

Answers (1)

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

Try something like this:

Sheets(Array("Print", "Print - input", "Print - plan")).Copy After:=Sheets(Sheets.Count)

Edited- with index references

Sheets(Array(1, 2, 3)).Copy After:=Sheets(Sheets.Count)

With variable references:

Dim sht1 as String, sht2 as String, sht3 As String
sht1 = "Print"
sht2 = "Print - input"
sht3 = "Print - plan"
Sheets(Array(sht1, sht2, sht3)).Copy After:=Sheets(Sheets.Count)

With dynamic array (here 3 element static, but could be any dynamic one, too):

Dim arrSHT as Variant
arrSHT = array("Print", "Print - input", "Print - Plan")
Sheets(arrSHT).Copy After:=Sheets(Sheets.Count)

Upvotes: 2

Related Questions