Copy Entire row when one cell equals something

[TangleRidge]

hey guys....I am very new to programming in Excel.

Here is the scoop...I have lots of data results that I need to organize monthly. I need a Macro or VBA so when a cell says something it copies the whole row over to a new sheet.

Here is the kicker. I have 54 separate sheets based on 54 different cell criteria that the rows need to be copied into. I am able to write a VBA to do one but can't figure out how to make multiple copies to a new sheet..Can anyone help me out?

eg) If cell D2 under brand says X, the whole row needs to move to sheet X. There are 54 different brands so 54 different sheets.

[TABLE="width: 388"]
<colgroup><col><col span="2"><col><col></colgroup><tbody>[TR]
[TD]ID[/TD]
[TD]Report Date[/TD]
[TD]Modified Date[/TD]
[TD]Brand[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

Re: Help!! Copy Entire row when one cell equals something...Lots of Data

My last script assumes the sheet name is in column F in some way.

Like if the sheet names are One and Two

dddOnehyr would work

Or:

asweTwokuy would work

But aseOndffe) would not work

 

[TangleRidge]

Re: Help!! Copy Entire row when one cell equals something...Lots of Data

Yes..the sheet name is in column F. It is still not recognizing any of the name...even when I have a sheet name that matches a word exactly

 

[My Aswer Is This]

Re: Help!! Copy Entire row when one cell equals something...Lots of Data

There have been about 4 different solutions provided here​

:

Which script is not working for you?
Or are you saying all 4 scripts are not working for you

 

[My Aswer Is This]

Re: Help!! Copy Entire row when one cell equals something...Lots of Data

Why are you allowing users to enter non exact sheet names in column F ?
So if a user enters John is my friend into column F is John the sheet name or is friend the sheet name?

I would think you need a DataValidation list in column F that would only allow proper sheet names.

And my original looping script would not work when you do not have exact sheet names in column F you would get a error

And is Sheet(1) the sheet with all your data? Sheet(1) is the sheet on the far left on your Tab bar.
And then the next 50 or so sheets are the sheets we need to be looking at.

 

[vcoolio]

Re: Help!! Copy Entire row when one cell equals something...Lots of Data

Hello TangleRidge,

From what you have said in previous posts, it would infer that the sheet names may change or only be similar at best with each data dump. The ideal is, as M.A.I.T. has already stated, is to have consistency with the sheet names/column F references. This would make things very much simpler and thus any of the codes he has supplied would work just fine.

However, if you don't have complete control of the data dump, then the following work-around may suit your needs:-

Option Explicit
Sub TransferData()

        Dim ar As Variant, shName As Variant, i As Long
        Dim ws As Worksheet, sht As Worksheet
        Dim rng As Range
        Set ws = Sheets("Master")
        
Application.ScreenUpdating = False

With ws
        Set rng = .Range("F2", .Range("F" & .Rows.Count).End(xlUp))
        shName = Application.Transpose(rng)
        i = LBound(shName)
End With

For Each sht In Worksheets
      If sht.Name <> "Master" Then
          sht.Name = shName(i)
            i = i + 1
      End If
Next sht

ar = ws.Range("F2", ws.Range("F" & ws.Rows.Count).End(xlUp))

For i = 1 To UBound(ar)

Sheets(ar(i, 1)).UsedRange.Offset(1).ClearContents

With ws.[A1].CurrentRegion
           .AutoFilter 6, ar(i, 1)
           .Offset(1).EntireRow.Copy Sheets(ar(i, 1)).Range("A" & Rows.Count).End(3)(2)
           .AutoFilter
     End With
Next i

Application.CutCopyMode = False
Application.ScreenUpdating = True
MsgBox "Data transfer completed!", vbExclamation, "Status"

End Sub

This code basically changes the sheet names to match those in Column F with each data dump and then transfers the relevant rows of data to each individual sheet.
The code assumes that you have headings in row1 and data starts in row2, Column A.
It also assumes that the number of sheets is constant.

I hope that this helps.

Cheerio,
vcoolio.