0

I found Alex P's code to copy and paste column data from one sheet to another based on header values. I also found the way to offset the paste to the start cell and row.

Now I need a bit more refinement. The pasted data pastes where I want but I need to past values only as the routine pastes all and destroys all formatting. I would also like to use this to copy and paste values from an open a second open workbook but that can be accomplished later. Here is the code I am using. From this post: Copy data from one worksheet to another based on column.

Sub CopyHeaders()
Dim header As Range, headers As Range
Set headers = Worksheets("ws1").Range("A1:Z1")

For Each header In headers
    If GetHeaderColumn(header.Value) > 0 Then
        Range(header.Offset(1, 0), header.End(xlDown)).Copy Destination:=Worksheets("ws2").Cells(2, GetHeaderColumn(header.Value))
    End If
Next
End Sub

Function GetHeaderColumn(header As String) As Integer
Dim headers As Range
Set headers = Worksheets("ws2").Range("A1:Z1")
GetHeaderColumn = IIf(IsNumeric(Application.Match(header, headers, 0)), Application.Match(header, headers, 0), 0)
End Function

Any help would be appreciated.

Community
  • 1
  • 1
Bud W
  • 1
  • 2

1 Answers1

2

You could use xlPasteValues

From this:

Range(header.Offset(1, 0), header.End(xlDown)).Copy Destination:=Worksheets("ws2").Cells(2, GetHeaderColumn(header.Value))

To this:

Range(header.Offset(1, 0), header.End(xlDown)).Copy
Worksheets("ws2").Cells(2, GetHeaderColumn(header.Value)).PasteSpecial xlPasteValues
Joseph
  • 5,070
  • 1
  • 25
  • 26
  • for the win indeed. This was the answer I was needing. Oddly I tried to insert the same code in many ways on the same line but kept getting the "expected end" error. Return after .Copy and call sheet, Paste special code. Perfect. Thanks Joseph. – Bud W Feb 24 '15 at 23:18