CODEWITHSUNDEEP
vbaexcelexcel-2007
Nupur
Nupur

Reputation: 357

Copy Headers from an excel file (Excel VBA code)

Good Morning!

I am working on an excel file in which I want to copy headers from one sheet and paste it after headers of another sheet. An example is:

The sheet 1 has data headers as: enter image description here

Sheet 2 has data headers as: enter image description here

The issue is that the number of headers in both doesnt remian constant each month. So I want thye code (VBA) to copy headers starting from A1 to last used cell of sheet2 and paste it after last used cell of sheet1. Here is the code that I have written to the best of my knowledge about VBA:

Sub LastColumnInOneRow()
'Find the last used column in a Row: row 1 in this example
    Dim LastCol As Integer
    Dim LastCell As String
    Dim last As Long
    Dim rng As Range
    With Sheets("Data")
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set rng = Sheets("Data").Cells
LastCell = last(3, rng)
Range("A1" & LastCell).Select
 Selection.Copy
End With
End Sub

I know there is something wrong with this code as it doesn run. Can anyone help with with this problem.

Thanks a ton!

Upvotes: 0

Views: 6658

Answers (1)

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

try the below:

Sub LastColumnInOneRow()

Dim rngSource As Range
Dim rngDestination As Range

sheets("Sheet2").select
Set rngSource = Range(Cells(1, 1), Cells(1, Range("A1").End(xlToRight).Column))
rngsource.copy

sheets("Sheet1").select
Set rngDestination = Range("A1").End(xlToRight).Offset(0, 1)

' rngSource.Copy Destination:=rngDestination
'    rngDestination.pastespecial xlpasteall
rngDestination.select
activesheet.paste
End Sub

that should get you started

Philip

Upvotes: 2

Related Questions