0

I having a trouble in getting the column value of a header name. I tried to search online but i cant find any solution. The column value is need to be use to copy the data from a sheet to another sheet.

your help is greatly appreciated 

Sub CopyCols()

    Dim h As Range, f As Range, sht1, rngDest As Range
    Dim ColNum As Integer
    Dim lastRow As Long

    Set sht1 = ThisWorkbook.Sheets("Data")
    Set rngDest = ThisWorkbook.Sheets("DataDb").Range("A1") 'start pasting here

    'loop through the headers to copy
    For Each h In sht1.Range("N1:AJ1").Cells
    'find the header on sheet1
    Set f = sht1.Rows(4).Find(what:=h, lookat:=xlPart)
    ColNum = f.Column
        If Not f Is Nothing Then
            'found the header: copy the column
            lastRow = sht1.Cells(Rows.Count, ColNum).End(xlUp).Row
            Sheet2.Range(Cells(4, ColNum), Cells(lastRow, ColNum)).Copy
            rngDest.PasteSpecial Paste:=xlValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
            Application.CutCopyMode = False
        End If
        Set f = Nothing
    Next h
End Sub
Miles Fett
  • 711
  • 4
  • 17
Sim Cheng Keng
  • 1
  • 1
    And exactly what is the problem with your code? A sample of data, that reproduces whatever problem you are having, would be userful. – Ron Rosenfeld Sep 01 '19 at 18:01
  • First of all, probably there could be a problem with this line Sheet2.Range(Cells(4, ColNum), Cells(lastRow, ColNum)).Copy put here sht1 instead of Sheet2. Also what I can see, every loop You are pasting your found data in the same spot You set before the loop. – Teamothy Sep 01 '19 at 19:54
  • Also see [this question](https://stackoverflow.com/questions/17733541/why-does-range-work-but-not-cells) for why `Sheet2.Range(Cells(4, ColNum), Cells(lastRow, ColNum)).Copy` is problematic. You need to qualify the worksheet the `Cells` are on – BigBen Sep 01 '19 at 20:39
  • `If Not f Is Nothing Then` why use a double negative?? – alowflyingpig Sep 01 '19 at 23:54
  • @alowflyingpig So as to avoid using `GoTo` to get the next iteration of the loop. – Ron Rosenfeld Sep 02 '19 at 01:35
  • @alowflyingpig - that is the way to test if a range object has been set in vba. you have to test if the object is not nothing, meaning it's been assigned to a specific range. – Scott Holtzman Sep 03 '19 at 18:49

0 Answers0