Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Copy column widths using resize and copy

Tags:

excel

vba

I have the following code that looks to the original worksheet on row 80 of each column and if it has the text "True" it copies that column to the destination worksheet. It then loops and goes through all of the columns. It works perfect, except I can not figure out how to copy the column widths. - Jordan

'Called from AddWorksheet
Sub CopyFinal(orgSheet As Worksheet, destSheet As Worksheet)

Dim j As Integer '**Why is j an Integer and others are Long?
Dim lastColumn As Long
Dim benRow As Long

j = 2
lastColumn = 2
'Counts the number of benefits on each sheet.  Assumes that they will not go past row 40
benRow = WorksheetFunction.CountA(orgSheet.Range("B3:B40"))

Application.ScreenUpdating = False

Do Until IsEmpty(orgSheet.Cells(3, j))
    If orgSheet.Cells(80, j) = True Then
        orgSheet.Cells(3, j).Resize(benRow).Copy destSheet.Cells(3, lastColumn) '**Need to paste column widths
    End If
    j = j + 1
    lastColumn = destSheet.UsedRange.Columns(destSheet.UsedRange.Columns.Count).Column + 1
Loop

Application.ScreenUpdating = True
End Sub
like image 622
JordanCA57 Avatar asked Aug 12 '26 02:08

JordanCA57


1 Answers

Do Until IsEmpty(orgSheet.Cells(3, j))
    If orgSheet.Cells(80, j) = True Then
        orgSheet.Cells(3, j).Resize(benRow).Copy
        With destSheet.Cells(3, lastColumn)
            .Paste
            .PasteSpecial Paste:=xlPasteColumnWidths
        End With
    End If
    j = j + 1
    lastColumn = destSheet.UsedRange.Columns(destSheet.UsedRange.Columns.Count).Column + 1
Loop

Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!