0

The below macro will allow me to find a name in the heading in sheet 1 and copy the entire column to sheet 2. Now I want to continue the code, but am facing a problem, which I will try explain.

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

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

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 to set filters as shown below and copy the filtered data to sheet 3 as above code does:

  1. I need to set filter criteria on sheet 2 which helps me to see Names that are equal to "John" or "Hary" and copy and paste 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 always be yesterday). Copy and paste the entire data into sheet 4.
  3. On the third time, I need to set a filter where Name is equal to "King" and copy the and past the entire data into sheet 5.

Update

Thanks a lot John for your reply, the reply you gave is effective but I have already designed my code.

I am pasting a part of code as it is not possible to paste the entire code.

The code allows me to copy data from one workbook to another, but while copying data, I need to copy an entire column because there are some blank cells in it. So if I don't use .EntireColumn, the macro does not copy cells after the blank cell. Also now, while pasting the data into the other work book, I need to paste it without the heading.

Windows("macro2.xlsm").Activate
Range(Range("M2"), Range("N2").End(xlDown)).EntireColumn.Select
Application.CutCopyMode = False
Selection.Copy
Windows("formula.xls").Activate
Range(Range("I2"), Range("J2").End(xlDown)).EntireColumn.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
halfer
  • 19,824
  • 17
  • 99
  • 186
jimz
  • 21
  • 1
  • 1
  • 1
  • i don't think your first code will work well (there are some syntax errors). How the data are supposed to be filtered on the third and fourth sheets? do we set the filter *John* in the code? – JMax Sep 08 '11 at 13:01

1 Answers1

2

Task 1:

 thisworkbook.sheets(2).activate
 activesheet.range("A:A").select 'set column you filter for probable names here
 Selection.AutoFilter Field:=1, Criteria1:="=John", Operator:=xlOr, _
 Criteria2:="=Hary" ' filters  only for hary or john 
 activate.usedrange.select ' now select the filtered sheet to copy  
 selection.copy 
 ActiveSheet.ShowAllData ' now retain back the data so that you get your original file
 thisworkbook.sheets(3).activate  'select your sheet3 and paste it
 activate.range("A1").select
 activesheet.paste

Task 2:

 thisworkbook.sheets(2).activate
 activesheet.range("A:A").select \\'set column you filter for probable names here
 Selection.AutoFilter Field:=1, Criteria1:="John" \\ filters for john
 Selection.AutoFilter Field:=2, Criteria1:="1-sep-2011"  \\ filters for date only for john rows 
 activate.usedrange.select ' now select the filtered sheet to copy  
 selection.copy 
 ActiveSheet.ShowAllData ' now retain back the data so that you get your original file
 thisworkbook.sheets(4).activate  'select your sheet3 and paste it
 activate.range("A1").select
 activesheet.paste

Task 3

 thisworkbook.sheets(2).activate
 activesheet.range("A:A").select 'set column you filter for probable names here
 Selection.AutoFilter Field:=1, Criteria1:="=King" ' filters  only king 
 activate.usedrange.select ' now select the filtered sheet to copy  
 selection.copy 
 ActiveSheet.ShowAllData ' now retain back the data so that you get your original file
 thisworkbook.sheets(5).activate  'select your sheet3 and paste it
 activate.range("A1").select
 activesheet.paste

Probably It might give you some idea how to do it. any more doubt feel free to ask me.

Thanks! You could probably go for copy destination:= and many more way to do it . actually I have to go now so i just gave you a sample piece to work on.

niko
  • 9,285
  • 27
  • 84
  • 131