Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Copy Only Select Data?
I'm looking for a macro that will copy data from a "master" sheet to
individual sheets based on the sheet name. What I have is a master sheet containing 4 columns of data (one of which is a group id) that I manually sort by group and then manually copy that groups information into their own sheet. There are approximately 20 sheets (groups). What I need is a macro that will look at the sheet name (which is the group id) and then go back to the master sheet and pull all the rows of data that match that sheet name. Oh, I do have some sheets in the workbook that would be excluded from this. I know nothing about writing macros so any help will be greatly appreciated! Thanks! Randy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Copy Only Select Data?
http://www.rondebruin.nl/copy5.htm
-- Regards, Tom Ogilvy "Randy" wrote in message ... I'm looking for a macro that will copy data from a "master" sheet to individual sheets based on the sheet name. What I have is a master sheet containing 4 columns of data (one of which is a group id) that I manually sort by group and then manually copy that groups information into their own sheet. There are approximately 20 sheets (groups). What I need is a macro that will look at the sheet name (which is the group id) and then go back to the master sheet and pull all the rows of data that match that sheet name. Oh, I do have some sheets in the workbook that would be excluded from this. I know nothing about writing macros so any help will be greatly appreciated! Thanks! Randy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Copy Only Select Data?
You start learning macros by turning on the macro recorder. Then do your copy.
Stop the macro recorder. Now take a look at your code. No one is going to write the full macro for you, unless you want to hire them. However, as you flesh our your macro, posting specific questions will get you quick, accurate answers. -- Regards, Fred "Randy" wrote in message ... I'm looking for a macro that will copy data from a "master" sheet to individual sheets based on the sheet name. What I have is a master sheet containing 4 columns of data (one of which is a group id) that I manually sort by group and then manually copy that groups information into their own sheet. There are approximately 20 sheets (groups). What I need is a macro that will look at the sheet name (which is the group id) and then go back to the master sheet and pull all the rows of data that match that sheet name. Oh, I do have some sheets in the workbook that would be excluded from this. I know nothing about writing macros so any help will be greatly appreciated! Thanks! Randy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Copy Only Select Data?
Try this. One proviso - your sheets are named exactly as the group id's,
and you may run into problems if these are numbers. For example Thisworkbook.worksheets("5") seems to also try Thisworkbook.worksheets(5) ie. if there is no sheet named "5" it will instead return the fifth sheet (if there is one). Tim '******************** Option Explicit Sub Tester() Const ID_COL As Integer = 2 'col with group id Dim r As Range Dim s As Worksheet Dim v As String For Each r In ThisWorkbook.Sheets("Master").Range("A2:D500").Row s v = r.Cells(ID_COL).Value If v < "" Then On Error Resume Next Set s = ThisWorkbook.Worksheets(v) On Error GoTo 0 If Not s Is Nothing Then r.Copy s.Cells(s.Rows.Count, 1).End(xlUp).Offset(1, 0) r.Interior.Color = vbGreen Else r.Interior.Color = vbRed End If End If Next r End Sub "Randy" wrote in message ... I'm looking for a macro that will copy data from a "master" sheet to individual sheets based on the sheet name. What I have is a master sheet containing 4 columns of data (one of which is a group id) that I manually sort by group and then manually copy that groups information into their own sheet. There are approximately 20 sheets (groups). What I need is a macro that will look at the sheet name (which is the group id) and then go back to the master sheet and pull all the rows of data that match that sheet name. Oh, I do have some sheets in the workbook that would be excluded from this. I know nothing about writing macros so any help will be greatly appreciated! Thanks! Randy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How Do I Copy Only Select Data?
Tim,
I really appreciate your help on this (unlike the person who told me to figure it out myself). Most of what I do is very time critical and you just saved me a lot of time! There seems to be one minor glitch that I cannot figure out. If I have a Group ID in the master sheet that does not have it's own corresponding sheet, then the macro copies that data into the current sheet it's working on. Not all of the IDs will have a sheet and that's by design. Seems like it's not going to the error handling or something. Any ideas how to correct this? Thanks again! Randy "Tim Williams" wrote: Try this. One proviso - your sheets are named exactly as the group id's, and you may run into problems if these are numbers. For example Thisworkbook.worksheets("5") seems to also try Thisworkbook.worksheets(5) ie. if there is no sheet named "5" it will instead return the fifth sheet (if there is one). Tim '******************** Option Explicit Sub Tester() Const ID_COL As Integer = 2 'col with group id Dim r As Range Dim s As Worksheet Dim v As String For Each r In ThisWorkbook.Sheets("Master").Range("A2:D500").Row s v = r.Cells(ID_COL).Value If v < "" Then On Error Resume Next Set s = ThisWorkbook.Worksheets(v) On Error GoTo 0 If Not s Is Nothing Then r.Copy s.Cells(s.Rows.Count, 1).End(xlUp).Offset(1, 0) r.Interior.Color = vbGreen Else r.Interior.Color = vbRed End If End If Next r End Sub "Randy" wrote in message ... I'm looking for a macro that will copy data from a "master" sheet to individual sheets based on the sheet name. What I have is a master sheet containing 4 columns of data (one of which is a group id) that I manually sort by group and then manually copy that groups information into their own sheet. There are approximately 20 sheets (groups). What I need is a macro that will look at the sheet name (which is the group id) and then go back to the master sheet and pull all the rows of data that match that sheet name. Oh, I do have some sheets in the workbook that would be excluded from this. I know nothing about writing macros so any help will be greatly appreciated! Thanks! Randy |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to select and copy rows only containing data | Excel Discussion (Misc queries) | |||
Select a file, copy it and change some data | Excel Programming | |||
Copy and Paste select data to another workbook. | Excel Programming | |||
Select certain data from a Pivot and copy this into Powerpoint | Excel Programming | |||
find specific data in row and select and copy entirerow | Excel Programming |