Solved vba code to find particular column and past

September 3, 2011 at 11:08:56
Specs: Windows 7


Hi,

Need your help, hope you wont mind.

I want a macro code that will allow me to copy certain columns form one worksheet to other. I understand that i can simply do it by recording macro but the thing is columns keep on changing but the column name remain the same so recording macro some time past the incorrect data ...So i would require macro code that will copy data on the bases of column name past the entire column in other work sheet.

Example.

Ctrl F name column from sheet1 and past the name column in sheet2 and so on .... so that if the name heading comes in the column 2 instead of column of column 1 it could still be able to past the correct data..


i would be glad if you will help me ou.

thanks:)


See More: vba code to find particular column and past

Report •


✔ Best Answer
September 5, 2011 at 09:45:49
It should be evident by the comments I've included that you can basically just duplicate the code, changing the Search string and the location where it gets pasted.

I will show you how to do it for one more column. After that you should try the rest on your own and post back if you have problems.

One last note: When you post a question, you should include all of your requirements instead of asking part of a question and then adding on to that. Sometimes it easy to include the new requirements and sometimes it requires a re-write of everything to make it as efficient as possible. Since many of us test our code and formulas before we post them, when you add requirements in pieces, we end up having to repeat a lot of the work we did before. That's when it stops being fun.

Sub CopyColumnByTitle()
'Find "Name" in Row 1
  With Sheets(1).Rows(1)
   Set t = .Find("Name", lookat:=xlpart)
'If found, copy the column to Sheet 2, Column A
'If not found, present a message
     If Not t Is Nothing Then
        Columns(t.Column).EntireColumn.Copy _
          Destination:=Sheets(2).Range("A1")
       Else: MsgBox "Name Not Found"
     End If
  End With
'Find "Age" in Row 1
  With Sheets(1).Rows(1)
   Set t = .Find("Age", lookat:=xlpart)
'If found, copy the column to Sheet 2, Column A
'If not found, present a message
     If Not t Is Nothing Then
        Columns(t.Column).EntireColumn.Copy _
          Destination:=Sheets(2).Range("B1")
       Else: MsgBox "Age Not Found"
     End If
  End With
End Sub

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



#1
September 3, 2011 at 18:55:50
I'm just a tad confused...

Are you always trying to copy a column with the same specific title in Row 1, but the issue is that the column with that title isn't always in the same place or do you want to choose the column to copy each time by entering a string in a pop-up input box?

Whichever of those 2 it is, where in Sheet 2 should the column be pasted?

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


Report •

#2
September 4, 2011 at 01:06:55
yes you are right ...."Are you always trying to copy a column with the same specific title in Row 1, but the issue is that the column with that title isn't always in the same place"


column heading always remain same say column A with heading "Name" and B as "Age" C as Address but some times it happiness the "Name" comes in the column B instead of Column A while exporting it from database. So for that reason i would like to have a macro code that will always search for the particular column heading "Name" no matter in which column it appears in the excel sheet and copy the entire column in the other excel sheet of the same workbook.


Report •

#3
September 4, 2011 at 01:18:33
You didn't answer my second question:

Where in Sheet 2 should the column be pasted?

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


Report •

Related Solutions

#4
September 4, 2011 at 01:26:13
please provide me your email id i will send you the excel workbook for your reference it will be easy

thanks


Report •

#5
September 4, 2011 at 01:29:37
in column A itself.

Report •

#6
September 4, 2011 at 07:07:46
If Name isn't the real title of the column, replace it with the actual title you are searching for. Leave the quotation marks.

Note: Copying data from a website often brings along extraneous characters that might not be visible in the cell: extra spaces, hidden characters, etc. For that reason I used xlpart (partial) as the search setting for the Find method. Therefore it should find the first cell in Row 1 that contains Name anywhere in the cell.

Sub CopyColumnByTitle()
'Find "Name" in Row 1
  With Sheets(1).Rows(1)
   Set t = .Find("Name", lookat:=xlpart)
'If found, copy the column to Sheet 2, Column A
'If not found, present a message
     If Not t Is Nothing Then
        Columns(t.Column).EntireColumn.Copy _
          Destination:=Sheets(2).Range("A1")
       Else: MsgBox "Title Not Found"
     End If
  End With
End Sub

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


Report •

#7
September 5, 2011 at 06:51:29
Thank you so much .... Will check and let you know ..... :-)

Report •

#8
September 5, 2011 at 08:50:33
Yes it is working ......

How to add more column heading to copy more columns? .... Age , Address etc from a table which contains Name Age Address Nationality


Report •

#9
September 5, 2011 at 09:45:49
✔ Best Answer
It should be evident by the comments I've included that you can basically just duplicate the code, changing the Search string and the location where it gets pasted.

I will show you how to do it for one more column. After that you should try the rest on your own and post back if you have problems.

One last note: When you post a question, you should include all of your requirements instead of asking part of a question and then adding on to that. Sometimes it easy to include the new requirements and sometimes it requires a re-write of everything to make it as efficient as possible. Since many of us test our code and formulas before we post them, when you add requirements in pieces, we end up having to repeat a lot of the work we did before. That's when it stops being fun.

Sub CopyColumnByTitle()
'Find "Name" in Row 1
  With Sheets(1).Rows(1)
   Set t = .Find("Name", lookat:=xlpart)
'If found, copy the column to Sheet 2, Column A
'If not found, present a message
     If Not t Is Nothing Then
        Columns(t.Column).EntireColumn.Copy _
          Destination:=Sheets(2).Range("A1")
       Else: MsgBox "Name Not Found"
     End If
  End With
'Find "Age" in Row 1
  With Sheets(1).Rows(1)
   Set t = .Find("Age", lookat:=xlpart)
'If found, copy the column to Sheet 2, Column A
'If not found, present a message
     If Not t Is Nothing Then
        Columns(t.Column).EntireColumn.Copy _
          Destination:=Sheets(2).Range("B1")
       Else: MsgBox "Age Not Found"
     End If
  End With
End Sub

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


Report •

#10
September 6, 2011 at 07:40:26
Thanks ... i would be careful while posting my queries in future...

Thanks a lot once again .....


Report •

#11
September 7, 2011 at 04:15:41
thank you so much .. the above code is working fine ... but i am very sorry i have some more queries around this .... i would be grateful if you will help me out ....i know this is bit annoying to not post my queries at once ...

after all data is pasted in the sheet 2 ....


Sheet 2
Name Age Address Date of Birth
John 25 US 1-Sep-11
Hary 26 US 1-Sep-11
John 27 UK 1-Sep-11
Hary 28 US 2-Sep-11
King 29 UK 3-Sep-11
Peter 30 US 3-Sep-11

i need set filters as below:

1: I need set filter criteria on sheet 2 which helps me to see "Names" that are equal to John or Hary and copy and past the entire data into sheet 3
2:I need to set another filter where Name is equal to john and date of birth is equal to 1-Sep-11 (note the date should be always yesterday).copy and past the entire data into sheet 4.
3. third time i need set filter where name is equal to King copy the and past the entire date into sheet 5

thanks in anticipation ... i you will help with this..



Report •

#12
September 8, 2011 at 04:56:40
Sir,
I expect a help from you....
would be greatness from you if help me out with above.
the code should run with your perversion reply. I need to compile it together.

Report •

#13
September 9, 2011 at 10:01:09
really appreciate.... if you help me ... i really facing problem .....

Report •

#14
September 9, 2011 at 11:49:38
...and I am facing a heavy workload in my real life.

Keep in mind they we are all volunteers here. My guess is that you are getting comepensated for the work you are doing related to your questions. I am not.

If this need is critical to you (or your company) perhaps you should consider hiring a programmer who might (for the right amount of money) be able to meet your timeframes.

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


Report •

#15
September 9, 2011 at 13:14:47
Dear i was just opting for your help because you are the best to answer and i believe this page is for answer yes, i was not able to place my query at once the reason is i am new to this site so in future i would be careful while posting my query. i have completed the code writing as you advised but the the code gives me an error which i am not able to rectify ...this would be the last thing in which i need your help...

Report •

#16
September 9, 2011 at 13:22:53
i am a student ... and i need this to complete my project.

Report •

Ask Question