CODEWITHSUNDEEP
vbaexcelloopscopy-paste
Hiimtroymcclure
Hiimtroymcclure

Reputation: 3

VBA Excel - Copying and Pasting Column Width/Groupings

I'm trying to copy the entire worksheet from "DNU" into each proceeding worksheet. I'm looking to do a regular paste to keep the width and groupings but this will only paste the formulas and table format. Thank you.

Here is the code:

~

Dim wsVar As Worksheet
Dim i as Integer
With ThisWorkbook
    For i = 6 to .Worksheets.Count
        .Worksheets("DNU").Range("A1:Y200").Copy destination:=.Worksheets(i).Range("A1:Y200")
    Next 
End With

Upvotes: 0

Views: 924

Answers (1)

GMalc
GMalc

Reputation: 2628

You dont't need to use copy/paste. Value can have arguments, 11 transfers both value and formats.

Dim i As Integer
With ThisWorkbook
    For i = 2 To .Worksheets.Count
        .Worksheets(i).Range("A1:Y200").Value(11) = .Worksheets("Sheet1").Range("A1:Y200").Value(11)
        'Unsure how you accomplish your grouping,
        .Worksheets(i).Range("A1:Y200").OutlineLevel = .Worksheets("Sheet1").Range("A1:Y200").OutlineLevel

        Dim colx As Long
        For colx = 1 To 25
            Worksheets(i).Columns(colx).ColumnWidth = Worksheets("Sheet1").Columns(colx).ColumnWidth
            Worksheets(i).Columns(colx).OutlineLevel = Worksheets("Sheet1").Columns(colx).OutlineLevel
        Next

        Dim rowx As Long
        For rowx = 1 To 200
            Worksheets(i).Rows(rowx).OutlineLevel = Worksheets("Sheet1").Rows(rowx).OutlineLevel
        Next

    Next
End With

Upvotes: 1

Related Questions