0

I am naive to macros and i tried the below code to copy data based on column header from workbook1 to workbook2. But it copies only the rows with data. I have some blank rows in between. Any help will be much appreciated.

I presume the End(x1Down).Copy is stopping to proceed further. Instead I tried using EntireColumn.Copy, but it spolis the formating in the destination workbook. 

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
Community
  • 1
  • 1
Siraj
  • 157
  • 1
  • 5
  • 16
  • Find the last row using [This](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) and then construct your range. You should avoid using `xlDown` as explained in the above link – Siddharth Rout Aug 13 '16 at 14:34

2 Answers2

1

in your question you mentioned different workbooks and if that is the case, you may want to specify the workbook names as well to ensure the program runs predictably.

e.g.

Set headers = Workbooks("wb1").Worksheets("ws1").Range("A1:Z1")

Assuming that this is just about copying from one worksheet to another, then you might want to heed Siddharth Rout's advice on using End(xlUp) instead due to the presence of blank rows.

e.g.

Worksheets("ws1").Range(Worksheets("ws1").Range("A1").Offset(1, 0), Worksheets("ws1").Range("Z1048576").End(xlUp))

Please note I dont exactly like this method since it isnt very dynamic but because of the way header is set...and the lack of information on your worksheet structure, i have given this example to illustrate the use of End(xlUp).

Also please note that if you are using older versions of Excel, do change 1048576 to 65536 (or any row that you deem safe for your worksheet)

AiRiFiEd
  • 311
  • 2
  • 12
1

If you are in a block of non-empty cells Range.End() will bring you to the last used cell.

If you are at the end of a block of non-empty cells Range.End() will bring you to the next used cell or the end of the column.


Range(header.Offset(1, 0), header.Cells(Rows.Count).End(xlUp)).Copy Destination:=Worksheets("ws2").Cells(2, GetHeaderColumn(header.Value))
  • Excellent, this works. You saved my time and many thanks Thomas. – Siraj Aug 13 '16 at 17:58
  • Also thanks to Siddharth to enlighten with great knowledge of using proper syntax. Appreciate all your contributions. Thanks once again to all of you – Siraj Aug 13 '16 at 18:00