Macro to copy paste a whole line into another sheet

[Roman12]

Hi
I have 4 sheets called Data, xxx, www and yyy. The sheet names xxx, www and yyy are also words that are existing in the sheet Data in Row C. Am I able to copy paste all the lines existing in the sheet Data including i.e. xxx in Row C into the sheet xxx? This should also work for the words www and yyy.

 

[Peter_SSs]

Hi,
Thanks Peter for all your help in this thread. The code works perfectly. However, I was wondering if there is a way, it can search for a word in a cell that has many words. For an example: Cell C3 has "xxx,yyy,zzz". I would want it to be able to copy to row to all three sheets: xxx, yyy, and zzz. or if Cell c4 has "xxx, zzz", to copy the row to only sheet xxx and sheet zzz. Is this possible? I hope this makes sense, thanks for your help in advance.

Welcome to the MrExcel board!

I assumed you were referring to the code in post #4.
Also assuming sheets "xxx", "yyy" etc already exist and that any data on them is to be removed, try this in a copy of your workbook.

Sub ExtractData()
  Dim lr As Long, i As Long
  Dim mysheet
  
  mysheet = Array("xxx", "yyy", "zzz")
  Application.EnableEvents = False
  Application.ScreenUpdating = False
  lr = Sheets("Data").Range("C" & Rows.Count).End(xlUp).Row
  With Sheets("Data").Range("A1:L" & lr)
    For i = LBound(mysheet) To UBound(mysheet)
      Sheets(mysheet(i)).UsedRange.ClearContents
      .AutoFilter Field:=3, Criteria1:="*" & mysheet(i) & "*"
      .Copy Destination:=Sheets(mysheet(i)).Range("A1")
    Next i
    .AutoFilter
  End With
  Application.EnableEvents = True
  Application.ScreenUpdating = True
End Sub

 

[abozza]

Hi,

I am trying to copy a row of data to a completely separate workbook, the 'Master List' (located in the same folder) based on two conditions. The contents of column S have a "$" in it, though I am using "*$*" because it is part of a dollar amount (I want it to skip over any row that has no dollar value in it), and has "#NA" in column T. I would like it to populate all of the rows from the monthly workbook that match these criteria in the 'master list' workbook.

I will be doing the same function from 6 workbooks and compiling all of these hits on one sheet in the master workbook. I was thinking that I would make the macro, put it in each of the 6 workbooks and have them all send to the centralized workbook. I also need it to not write over what has been placed in the book previously.

I have looked at a lot of different codes and none of them really line up with what I am trying to do. I am also pretty new to VBA.

Thanks for your help.

 

[andreas_mj]

Hi!

Thanks a lot for the info already provided.
I am working on a very similar problem as the original thread creator.

The difference is that the ‘xxx’ and ‘yyy’ terms in the ‘data’ sheet are only the beginnings of the cell contents appearing in my ‘data’ sheet, e.g. ‘xxx-1234’ or ‘yyy-5678’.

If I use the code provided it would obviously copy no data into the ‘xxx’ and ‘yyy’ sheets, since these terms specifically don’t exist in my ‘data’ sheet.

Is it possible to implement a code that includes the numbers after ‘xxx’ or ‘yyy’ into the copy as well?

I know that in Excel’s advanced filters you would add a * to make this work, but this doesn’t seem to work within the code.

Thanks a lot in advance!

 

[jskasango]

Hi,
I have a similar situation to andreas_mj . I have the first sheet (List) containing two columns (A and B) of a list of 11 alphanumeric codes eg CKL102, CML388 etc. Next sheet is a master sheet with over 10,000 "Pedigree" in column C and D "Name, Pedigree". These name and Pedigrees have the alphanumeric code in the format CKL102/xxx/b-B-BB-A OR CML388-BB-B- and so on and so forth. I need the code to search the Master (column C and D) and copy the whole row of the information into a new sheet named Name1, Name2, Name3 etc then move to the next item on the llist and do the same until the end.

Any assistance will be highly appreciated.

Thanks, Kasango