Selecting and copying a dynamic range of cells
 

I need to select and copy a row of headers in a spreadsheet starting from A1.
The number of columns with headers is not always the same. I would like to
paste them alongside the original headers but offset by one column.

Thank you for you help,

Esther

Selecting and copying a dynamic range of cells
 

I am not sure if this is exactly what you want... It is a little odd but it
is exactly what you asked for...

Public Sub CopyHeaders()
Dim rngToCopy As Range
Dim wks As Worksheet

Set wks = ActiveSheet
Set rngToCopy = Range(wks.Range("A1"), wks.Range("IV1").End(xlToLeft))

rngToCopy.Copy wks.Range("B1")

End Sub

If all you are trying to do is to shift your headings one cell to the left
then it is a lot easier than Copying and Pasting. Just insert a cell similar
to this

Public Sub ShiftHeaders()
ActiveSheet.Range("A1").Insert xlToRight
End Sub
--
HTH...

Jim Thomlinson


"EstherJ" wrote:

I need to select and copy a row of headers in a spreadsheet starting from A1.
The number of columns with headers is not always the same. I would like to
paste them alongside the original headers but offset by one column.

Thank you for you help,

Esther

Selecting and copying a dynamic range of cells
 

Esther,

Sub CopyHeaders()
Range("A1", Range("A1").End(xlToRight)).Copy _
Range("A1").End(xlToRight)(1, 3)
End Sub

That said, it is a BAD idea to use more than one data table with the same headers. Better by far to
add another column into your existing table to enter a value that would differentiate the new from
the old, and then your data is better formatted for filtering, use in pivot tables, etc.

HTH,
Bernie
MS Excel MVP


"EstherJ" wrote in message
...
I need to select and copy a row of headers in a spreadsheet starting from A1.
The number of columns with headers is not always the same. I would like to
paste them alongside the original headers but offset by one column.

Thank you for you help,

Esther