VBA Help - Change my code to fit new requirements

[Kestral79]

Hi,

i have the code below that moves data from one work sheet to another.

This code works as follows

• Iterate across each column header in <CODE>Sheets Summary</CODE> and see if a matching header exists in <CODE>Sheets Log </CODE>
• <CODE></CODE>If a match is found, copy the column contents across to the relevant column in <CODE>Sheets Log. </CODE>

i am trying to adapt the code to paste the data on the next avalible row & to referance a workbook rather than a sheet.

can anyone help ?

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

thanks

 

[Rijnsent]

Hi Kestral79,
I understand your code, but don't understand your question, what is it you are trying to achieve?
Maybe something like this helps:

Set actwb = ActiveWorkbook
Set wb = Workbooks("your_opened_file.xlsx")
Range(header.Offset(1, 0), header.End(xlDown)).Copy Destination:=wb.Worksheets("ws2").Cells(2, GetHeaderColumn(header.Value))

Cheers,
Koen

 

[VKMouli]

can you add this with past especial option because i am using but some formulas in ws1 not coming to ws2 with paste special. its shown as #NA