Search code examples
excelvbacopy-paste

How to copy and transpose headers to another sheet?


I'm trying to copy and transpose the headers from my source worksheet into my target sheet to use as mappings.

My code copies the row below the one I want (Row 1).

Sub Create_Mappings()
Dim source_sht As Worksheet
Dim target_sht As Worksheet

Dim src_raw_rng As Range 'Ranges for headings from raw_data
Dim trg_raw_rng As Range

Dim src_map_rng As Range 'Ranges for mapping headings
Dim trg_map_rng As Range

Dim last_row As Long
Dim last_column As Long

Set source_sht = ThisWorkbook.Worksheets(6)
Set target_sht = ThisWorkbook.Worksheets(4)

'Determine last row of data in Mappings sheet and last column in first row of Raw_Data
last_row = target_sht.Cells(target_sht.Rows.Count, "C").End(xlUp).Row
last_column = source_sht.Cells(source_sht.Range("A1"), source_sht.Columns.Count).End(xlToLeft).Column

'Clear mappings

Set src_raw_rng = source_sht.Range(source_sht.Cells(1, 1), source_sht.Cells(1, last_column))

Set trg_raw_rng = target_sht.Range(Range("InpVarStart"), target_sht.Cells(last_row + 1, 3))
   
trg_raw_rng.Clear

src_raw_rng.Copy
trg_raw_rng.PasteSpecial Transpose:=True

End Sub

Solution

  • Try this. Please note the comments starting with '*

    Sub Create_Mappings()
      Dim source_sht As Worksheet
      Dim target_sht As Worksheet
      
      Dim src_raw_rng As Range 'Ranges for headings from raw_data
      Dim trg_raw_rng As Range
      
      Dim src_map_rng As Range 'Ranges for mapping headings
      Dim trg_map_rng As Range
      
      Dim last_row As Long
      Dim last_column As Long
      
      Set source_sht = Sheet6 ' ThisWorkbook.Worksheets(6)
      Set target_sht = Sheet4 ' ThisWorkbook.Worksheets(4)
      
      'Determine last row of data in Mappings sheet and last column in first row of Raw_Data
      last_row = target_sht.Cells(target_sht.Rows.Count, "C").End(xlUp).Row
      
      '* changed source_sht.Range("A1") to 1
      '* you can use source_sht.Range("A1").Row, but 1 is better since you are hard-coding "A1"
      last_column = source_sht.Cells(1, source_sht.Columns.Count).End(xlToLeft).Column
      
      'Clear mappings
      
      Set src_raw_rng = source_sht.Range(source_sht.Cells(1, 1), source_sht.Cells(1, last_column))
      
      Set trg_raw_rng = target_sht.Range(Range("InpVarStart"), target_sht.Cells(last_row + 1, 3))
         
      trg_raw_rng.Clear
      
      src_raw_rng.Copy
      '* Use first cell of target range
      trg_raw_rng.Cells(1, 1).PasteSpecial Transpose:=True
      trg_raw_rng.Select
    End Sub