Hi there

I am trying to carry out a relatively simply copy/paste type function between two sheets based on the following criteria:
- range to be copied is from sheet 1A, column G, used range only (from row 6 onwards)
- data is to be "pasted" onto sheet HA2, same row numbers as the data on sheet 1A. The destination column is based on looking up and finding the value "1" in row 6 between columns 9 and 213.

I've tried the following code, using a function defined below, but it doesn't seem to work...

For the lookup of the destination column, I've just tried to find the last (only) column with data....

Please bear in mind, I'm a VBA absolute beginner, and the code I've hatched together is from other code a friend has prepared for me!

Thanks in advance

Sub PostProgressWBS01()
 
Dim cel As Range
Dim rng As Range
'Dim PutCol As Long
Dim PutCol As Long
Dim LastRow As Long
    
    'Get column where data is to be copied to on Sheet HA2
    Set rng = Sheets("HA2").Range(Cells(6, 9).Address, Cells(6, 213).Address)
    PutCol = Find_LastCol_Data(rng)
    
    'Get last Row with DATA on Sheet 1A
    Set rng = Sheets("1A").Range(Cells(18, 7).Address, Cells(9999, 7).Address)
    LastRow = Find_LastRow_Data(rng)
     
    'do the thing
    For Each cel In rng
        If cel.Value <> "" Then
        Sheets("HA2").Cells(cel.Row, PutCol) = cel
        End If
    Next cel

    Set cel = Nothing
    Set rng = Nothing
    
    Application.CutCopyMode = False
End Sub



Function Find_LastCol_Data(rng As Range)

    ' Find the last column
    On Error Resume Next
    Find_LastCol_Data = rng.Find(What:="*", _
                After:=rng.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlValues, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Column
    On Error GoTo 0
    
End Function

Function Find_LastRow_Data(rng As Range)

    ' Find the last row
    On Error Resume Next
    Find_LastRow_Data = rng.Find(What:="*", _
                After:=rng.Cells(1), _
                Lookat:=xlPart, _
                LookIn:=xlValues, _
                SearchOrder:=xlByRows, _
                SearchDirection:=xlPrevious, _
                MatchCase:=False).Row
    On Error GoTo 0
    
End Function