PDA

View Full Version : Using a cell reference/column value to copy rows to a new worksheet



wham
06-13-2006, 04:27 PM
Hi there - was a bit tongue tied with the thread title so I hope it adequately describes my problem. I'm basic to medium knowledge on excel, and have only dabbled in macros before and I am presuming it's a pretty complex one i need to solve my problem, although I am happy to be told otherwise!

I've been tasked with redoing a revenue report at work, and in my head I've conjured up what could potentially be a huge time saving way of doing things in the future as I'll be using and updating this workbook on a daily basis, although have searched help files and looked around online and can't find the formula/macro (if it exists) to enable me to do this.


The problem is this:

Sheet 1 of the workbook is a large input sheet. Every row contained in that sheet will, in column A, be titled 'red', 'yellow' or 'green' (the title is irrelevant really, they could just as easily be titled 1, 2 or 3). Columns B onwards contain other data which (at the moment) is irrelevant to the problem.

Now what I want excel to do after I've put the raw data into the input sheet (sheet 1), is to read the text in column A for each row, then automatically copy ALL data in that row over to the next empty row on another worksheet.

ie Sheet 2 will have all rows that have 'Red' in column A on the input sheet, Sheet 3 will have all those titled 'Yellow' and Sheet 4 will have all those titled 'Red'.

Is this something that is possible?

I know I can use a filter on the input sheet to just show the data I want, but each colour labelled row will contain different data to other colours, and if there is a formula/macro setup i can use to do the above, then I can set the subsequent worksheets up to hide the superflous columns from the input sheet.

Hope that makes sense, and hope one of you guys is able to help.

Thanks

Wayne

acw
06-13-2006, 05:48 PM
Wayne

See if the attached file will get you going.

Tony

wham
06-14-2006, 04:35 AM
Wayne

See if the attached file will get you going.

Tony

That's an excellent start, thanks very much. Have incorporated it into the main sheet I was using and tinkered the range part of the macro to a more suitable column (there is data in column L).

One question though - at the moment it's only copying across selected columns to the other sheets... is there a way i can tinker with it to copy ALL columns on the data sheet over?

Thanks again for your help.

Wayne

acw
06-14-2006, 03:42 PM
Wayne

Put all the column headings required in the output sheets. I only did it this way to show you that you don't have to bring in all the columns then hide them, but you could only bring in the columns relevant to each particular grouping from column A. Refer to your post


I can set the subsequent worksheets up to hide the superflous columns from the input sheet




Tony

lucas
06-15-2006, 08:30 AM
See this post for details.....I think this is what your looking for. Its set up to look at column C right now but its well commented in the code to change. Copies all rows with GK in col C to sheet GK..creates the sheet. Same for other options...eg. MF, FW, etc.
http://vbaexpress.com/forum/showthread.php?t=8411

Option Explicit
Sub CopyDataToNewWorksheets()
Dim CLL As Range, PlayersWS As Worksheet, DestWS As Worksheet

'Turn off ScreenUpdating for faster macro runtime so screen won't flash while running
Application.ScreenUpdating = False

'Set variables
Set PlayersWS = Sheets("2006")

' Add worksheets GK, etc.
Worksheets.Add(After:=PlayersWS).Name = "MF"
Worksheets.Add(After:=PlayersWS).Name = "FW"
Worksheets.Add(After:=PlayersWS).Name = "DF"
Worksheets.Add(After:=PlayersWS).Name = "GK"

'Copy header row from FARES to each of the new worksheets
With PlayersWS.Rows(1)
.Copy Sheets("GK").Rows(1) ' Paste to worksheet GK
.Copy Sheets("DF").Rows(1)
.Copy Sheets("FW").Rows(1)
.Copy Sheets("MF").Rows(1)
End With

'Look at each used cell in column C of 2006, starting with c3
' examine text in each cell, and set destination worksheet based on cell text
' If there is a destination sheet (valid, non-blank entries), row is copied
For Each CLL In PlayersWS.Range("C3", PlayersWS.Cells(PlayersWS.Rows.Count, 1).End(xlUp))

'Examine cell text, set destination worksheet accordingly
Select Case Trim(UCase(CLL.Text))
Case "GK": Set DestWS = Sheets("GK")
Case "DF": Set DestWS = Sheets("DF")
Case "FW": Set DestWS = Sheets("FW")
Case "MF": Set DestWS = Sheets("MF")
Case Else: Set DestWS = Nothing
End Select

'Copy data row to destination sheet if it exists
If Not DestWS Is Nothing Then
CLL.EntireRow.Copy DestWS.Rows(DestWS.UsedRange.Rows.Count + 1)
End If

Next 'CLL

'Turn ScreenUpdating back on
Application.ScreenUpdating = True

'Release memory reserved for variables
Set CLL = Nothing
Set PlayersWS = Nothing
Set DestWS = Nothing
End Sub