Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Copy rows of one sheet into mutiple sheets based on column value

I found this macro and modified it a bit. It does a great job coping rows
from a "main" sheet into multiple sheets based off a column value. I am
struggling now to go to a deeper level were it will copy the row based on
one column value looking at all sheets in a workbook. So instead of it
looking a at column value, creating a sheet if that value does not exist
then copying the matching column data to that sheet. I want to compare and
look at the same column of all sheets in my workbook (column B in my case)
and copy the row of matching data from my "main" sheet (column B).

So I want all my rows that have "excel.exe" in my "main" sheet to go to my
"MS Excel" named sheet and all my "winprog.exe" rows to go my "MS Project"
named sheet and etc.

Any assistance would be appreciated!

Thanks

Wesley



Sub CopyRowsToSheets()

'copy rows to worksheets based on value in column A

'assume the worksheet name to paste to is the value in Col A

Dim CurrentCell As Range
Dim SourceRow As Range
Dim Targetsht As Worksheet
Dim TargetRow As Long
Dim CurrentCellValue As String

'start with cell A2 on "Master" sheet
Set CurrentCell = Worksheets("Master").Cells(2, 1) 'row ... column ...

Do While Not IsEmpty(CurrentCell)
CurrentCellValue = CurrentCell.Value
Set SourceRow = CurrentCell.EntireRow

'Check if worksheet exists
On Error Resume Next

Testwksht = Worksheets(CurrentCellValue).Name

If Err.Number = 0 Then

'MsgBox CurrentCellValue & " worksheet Exists"
Else

MsgBox "Adding a new worksheet for " & CurrentCellValue

Worksheets.Add.Name = CurrentCellValue

End If

On Error GoTo 0 'reset on error to trap errors again

Set Targetsht = ActiveWorkbook.Worksheets(CurrentCellValue)

' Find next blank row in Targetsht - check using Column A
TargetRow = Targetsht.Cells(Rows.Count, 1).End(xlUp).Row + 1
SourceRow.Copy Destination:=Targetsht.Cells(TargetRow, 1)

'do the next cell
Set CurrentCell = CurrentCell.Offset(1, 0)

Loop

End Sub


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookup function and compare Student Excel Discussion (Misc queries) 8 April 25th 06 03:11 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Copy an additional row based on another sheet Mindie Excel Discussion (Misc queries) 5 October 19th 05 02:47 AM
Does excel recognise names rather than cells? Sue Excel Worksheet Functions 9 May 22nd 05 04:51 AM
MACRO - copy rows based on value in column to another sheet Michael A Excel Discussion (Misc queries) 1 March 5th 05 03:15 AM


All times are GMT +1. The time now is 10:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"