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
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With