Moving rows to worksheets based upon a cell value

[Derrigan]

Hi there,

I'm hoping someone might help.

I need to automatically move data from the main worksheet (Detail) to various others, based upon a value in column c. C contains business functions.

e.g
1) Finance
2) I.T
3) Marketing
....and so on....

There is a worksheet for each business function - and therefore each will need to move to it's namesake worksheet. I would like to keep the data in the main worksheet and essentially, duplicate it into other worksheets. There are 6 business functions in total.

The data will be copied and pasted from a CSV downloaded from a system where the data lives. The format of the download and the format of the spreadsheet is exactly the same.

Thanks so much for any guidance you can provide!
Derrigan

 

[My Aswer Is This]

So you want to copy the entire row of data to another sheet based off the value in column C of the sheet named Detail

So if George is in column C then this row gets copied to a sheet named George

Is that what you want?

But now lets talk about automatically.

You said:

I need to automatically move data from the main worksheet (Detail) to various others, based upon a value in column c. C contains business functions.

Nearly nothing happens in Excel Automatically.

So you need to tell me what you want to happen to activate the script.

You can click a button or double click a cell or several other ways.

But just pasting a whole lot of data into your sheet would not be a good way to try to activate a script.


​

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub CopyFltr()
   Dim Cl As Range
   Dim ws As Worksheet
   Dim Msg As String
   
   Application.ScreenUpdating = False
   Set ws = Sheets("[COLOR=#ff0000]Details[/COLOR]")
   
   If ws.AutoFilterMode Then ws.AutoFilterMode = False
   With CreateObject("scripting.dictionary")
      For Each Cl In ws.Range("C2", ws.Range("C" & Rows.Count).End(xlUp))
         If Not .Exists(Cl.Value) Then
            .Add Cl.Value, Nothing
            If Evaluate("isref('" & Cl.Value & "'!a1)") Then
               ws.Range("[COLOR=#ff0000]A1:Z1[/COLOR]").AutoFilter 3, Cl.Value
               ws.AutoFilter.Range.Offset(1).Copy Sheets(Cl.Value).Range("A" & Rows.Count).End(xlUp).Offset(1)
            Else
               If Msg = "" Then Msg = "Sheets" & vbLf & Cl.Value Else Msg = Msg & vbLf & Cl.Value
            End If
         End If
      Next Cl
   End With
   ws.AutoFilterMode = False
   If Msg <> "" Then MsgBox Msg & vbLf & "Not found"
End Sub

Change values in red to suit.
This code assumes that the col C values exactly match the sheet names & that col A will always have data

 

[Derrigan]

Hi There,

Thanks so much! Unfortunately, noting happens when i run the Macro - it seems that it has completed the action, but without a result/error messages. Any ideas what might cause that?

 

[Derrigan]

Thank you for providing some clarity. I have no preference on what activates the script, as the data will quite literally be dropped into the "detail" work sheet (Columns A through S will hold data) - any action would be fine. Clicking a button seems the logical way - for clarity to users.
Thanks again and sorry for the delay in responding to you

 

[Fluff]

Does col A have data for every row?
Did you get a message saying that certain sheets were not found?