Copy template header row to another worksheet based on cell value

[Sanoj]

Hi, apologies in advance I know this has been coved a million times but I cant seem to get any of the code to work for me.

I have to work on a two sheets named as "Text" and "Template" where the header row that is Sheet Text's A1 to AB1 needs to be changed on the basis of cell value of Text Sheet A2.

If Text Sheet A2 is "FC" then from the "Template" sheet A6:AB6 should be copied to the Header that is of Text sheet A1:AB1 Similarly for A2 is FP should copy the Template Sheet A2:AA2 to Text sheet A1:AA1

For reference I have attached sample workbook.

Thanks Guys for your time and patients.

 

[Asheesh]

Hi - You can use the below snippet

Dim TextSh As Worksheet, TemplateSh As Worksheet

Sub CopyHeader()

Set TextSh = Worksheets("Text")
Set TemplateSh = Worksheets("Template")


TextSh.Select

If Range("A2").Value = "FC" Then
  
  TemplateSh.Range("A6:AB6").Copy Destination:=TextSh.Range("A1")
  
ElseIf Range("A2").Value = "FP" Then

  TemplateSh.Range("A2:AA2").Copy Destination:=TextSh.Range("A1")

End If

End Sub

 

[Marc L]

Hi !

Next code works without any mod even in case of new headers :​

Sub Demo()
       Dim Rd As Range, Rg As Range
       Set Rd = Range("Text!A1")
With Worksheets("Template").UsedRange
       Set Rg = .Columns(1).Find(Rd(2).Value, LookAt:=xlWhole)
    If Not Rg Is Nothing Then
        With .Rows(Rg(2).Row)
             .Copy Rd
           Rd.RowHeight = .RowHeight
        End With
       Set Rg = Nothing
    End If
End With
       Set Rd = Nothing
End Sub

Do you like it ? So thanks to click on bottom right Like !
​

 

[Marc L]

Hi - You can use the below snippet

Dim TextSh As Worksheet, TemplateSh As Worksheet

Sub CopyHeader()

Set TextSh = Worksheets("Text")
Set TemplateSh = Worksheets("Template")


TextSh.Select

If Range("A2").Value = "FC" Then

  TemplateSh.Range("A6:AB6").Copy Destination:=TextSh.Range("A1")

ElseIf Range("A2").Value = "FP" Then

  TemplateSh.Range("A2:AA2").Copy Destination:=TextSh.Range("A1")

End If

End Sub

Asheesh, you forgot to clear Worksheet object variables
and I do not see why you declare them outside the procedure …

Your code revisited without any object variable :​

Sub CopyHeader()
    Dim S As String
    Worksheets("Text").Activate
Select Case Range("A2").Value
       Case "FC":  S = "A6:AB6"
       Case "FP":  S = "A2:AA2"
End Select
    If S > "" Then Range("Template!" & S).Copy Cells(1)
End Sub

Regards !
​

 

[shahin]

Hi Marc L, every time I see your code I feel jealous not to be able to write the same way. It's really aesthetically pleasing. Sometimes difficult for me to understand though!

 

[Marc L]

Thanks shahin and Monty !

But the better is the post #3 code !
Both codes use general VBA, nothing special …
​

 

[Sanoj]

Hello Marc, Shahin, and Aneesh, Thank you all for your response But finally I took the code sent by Marc.
But I really appreciate you all for the efforts and help.

 

[Sanoj]

Hello All, I got one question for the above code. Actually I am running macro code keeping it open to another excel file.
So, the above header is not getting copied from the Macro source file template sheet to the working active sheet.
That is the template sheet is in Macro file and should get copied from macro file to the current running another excel file.

 

[Marc L]

I got one question for the above code.

Which post # code ?!
​

 

[Sanoj]

Which post # code ?!
​

Postcode #4

used the following code:

Sub CopyHeader()
    Dim S As String
    Worksheets("Text").Activate
Select Case Range("A2").Value
        Case "FC":  S = "A6:AB6"
        Case "FP":  S = "A2:AA2"
End Select
    If S > "" Then Range("Template!" & S).Copy Cells(1)
End Sub

 

[Marc L]

• Remove codeline Worksheets("Text").Activate …

• Mod Range("Template!" & S) as ThisWorkbook.Range("Template!" & S) …
​

 

[Sanoj]

@Marc L No I am getting Compile error: Method or data member not found.

Sub CopyHeader()
    Dim S As String
Select Case Range("A2").Value
        Case "FC":  S = "A6:AB6"
        Case "FP":  S = "A2:AA2"
End Select
    If S > "" Then ThisWorkbook.Range("Template!" & S).Copy Cells(1)
End Sub

 

[Marc L]

Sorry !

If S > "" Then ThisWorkbook.Worksheets("Template").Range(S).Copy Cells(1)​

 

[Sanoj]

@Marc L, Thanks just as needed

 

[Asheesh]

Asheesh, you forgot to clear Worksheet object variables
and I do not see why you declare them outside the procedure …

Your code revisited without any object variable :​

Sub CopyHeader()
    Dim S As String
    Worksheets("Text").Activate
Select Case Range("A2").Value
       Case "FC":  S = "A6:AB6"
       Case "FP":  S = "A2:AA2"
End Select
    If S > "" Then Range("Template!" & S).Copy Cells(1)
End Sub

Regards !
​

Thanks for highlighting Mark, Mentioning variables outside the procedure is something that I usually follow but wasn't required here..
But Yes, your snippet is neat..!!