Solved how to copy and paste entire column in excel using.

August 17, 2016 at 10:21:26
Specs: Windows 7
hello Dear Champs,

How to copy and paste entire column after a specific header column in excel using VBA Micro ,please help with code it very urgent

Thanks a lot

Regards
Anil


See More: how to copy and paste entire column in excel using.

Report •

#1
August 17, 2016 at 11:13:51
Without more specifics regarding your requirements it's kind of hard to provide a complete solution. Just as examples...

We don't know what sheet you are copying from.
We don't know what sheet/column you are copying to.
We don't know what row your headers are in.
We don't know what "specific header" you are keying off of.
etc.

This generic code might get you started.

It assumes that you want to copy from Sheet1.
It assumes that you want to copy to Sheet2!Column A.
It assumes your headers are in Sheet1!Row 1.
It assumes that your "specific header" is Specific Header.

That's the best we can do at this point.

Sub CopyAfterColumnHeader()
With Sheets(1).Rows(1)
 Set h = .Find("Specific Header", lookat:=xlWhole)
  If Not h Is Nothing Then
   Sheets(1).Columns(h.Column + 1).EntireColumn.Copy _
    Sheets(2).Cells(1, 1)
  Else: MsgBox "Header Not Found"
  End If
 End With
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 27, 2016 at 05:42:30
thanks for reply ..

actually i have some column (contain formulas) and i want it copy and past before that column which contain "STN_QTY" header ...my header are in row 5 ...its all on the sheet1 (same sheet).

pl provide ur email ID , i can share u excel


Report •

#3
August 27, 2016 at 06:25:27
✔ Best Answer
You haven't told us what column you want to copy.
You haven't told us if you want to overwrite the existing data or do an insert-copy.

This code will copy Column 1, which is Column A.

Change the Columns() number in the instruction after the comment line that starts with * to the number that matches the column you want to copy.

There are 2 versions of the macro. The first will overwrite the data in the column before the header, the second will insert the copied column.

Sub CopyBeforeHeader_Overwrite()
With Sheets(1).Rows(5)
'Find the Header
 Set h = .Find("STN_QTY", lookat:=xlWhole)
'*Copy Column 1 (A), Overwrite existing data
   Sheets(1).Columns(1).EntireColumn.Copy _
    Sheets(1).Cells(1, h.Column - 1)
 End With
End Sub

Sub CopyBeforeHeader_Insert()
With Sheets(1).Rows(5)
'Find the Header
 Set h = .Find("STN_QTY", lookat:=xlWhole)
'*Copy Column 1 (A), Insert before header column
   Sheets(1).Columns(1).EntireColumn.Copy
   Sheets(1).Cells(1, h.Column - 1).Insert shift:=xlToRight
 End With

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question