Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Hi all,
I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2=€¯Closed€¯), ROW(),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem€¦.I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Womens Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Do you want the destination columns to be the same columns as the Source
columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Let me explain what I mean. Let's say you want to copy 15 columns, or 30
columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Hi Otto, thanks for replying, I would want to paste them in sequential
columns on the new spreadsheet. "Otto Moehrbach" wrote: Let me explain what I mean. Let's say you want to copy 15 columns, or 30 columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Here is a macro to do what you want. This macro is just for the "Women's
Health" sheet and for the condition of column Y being "Yes". Note that your file must have a sheet named "Master" and another sheet named "Women's Health". You said that you need to do 5 other sheets using different criteria. That can all be done with just one macro. Tell me what the sheet names are and what the criteria is. Do you want the same columns copied or does that change also? What version of Excel are you using? You said that you don't know macros (VBA). David McRitchie has some notes for getting started with macros at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sub CopyYes() Dim rRowRng As Range Dim rColY As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:W1,AG1,AJ1,AL1,AN1,AP1,AR1,AT1") Set rColY = Range("Y2", Range("Y" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColY If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub "Doehead" wrote in message ... Hi Otto, thanks for replying, I would want to paste them in sequential columns on the new spreadsheet. "Otto Moehrbach" wrote: Let me explain what I mean. Let's say you want to copy 15 columns, or 30 columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Hi Otto, thank you for all your work on this Macro but it is not doing
anything. I copied the macro under sheet Women's Health nothing... deleted then copied under sheet Master nothing...... then deleted and copied under the workbook. Is it because on the Master sheet column A has a unique record number that I did not want to copy over to Women's Health? I did change the macro columns as I was off one column after I added the URN column(A): Sub CopyYes() Dim rRowRng As Range Dim rColZ As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:X1,AH1,AK1,AM1,AO1,AQ1,AS1,AU1") Set rColZ = Range("Z2", Range("Z" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColZ If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub Thanks for the website .......... looks scary but will start to look through it. Janice "Otto Moehrbach" wrote: Here is a macro to do what you want. This macro is just for the "Women's Health" sheet and for the condition of column Y being "Yes". Note that your file must have a sheet named "Master" and another sheet named "Women's Health". You said that you need to do 5 other sheets using different criteria. That can all be done with just one macro. Tell me what the sheet names are and what the criteria is. Do you want the same columns copied or does that change also? What version of Excel are you using? You said that you don't know macros (VBA). David McRitchie has some notes for getting started with macros at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sub CopyYes() Dim rRowRng As Range Dim rColY As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:W1,AG1,AJ1,AL1,AN1,AP1,AR1,AT1") Set rColY = Range("Y2", Range("Y" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColY If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub "Doehead" wrote in message ... Hi Otto, thanks for replying, I would want to paste them in sequential columns on the new spreadsheet. "Otto Moehrbach" wrote: Let me explain what I mean. Let's say you want to copy 15 columns, or 30 columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
What do you mean when you say "copied the macro under sheet Women's Health"?
What exactly did you do? Give me a step-by-step. Did you run the macro? Otto "Doehead" wrote in message ... Hi Otto, thank you for all your work on this Macro but it is not doing anything. I copied the macro under sheet Women's Health nothing... deleted then copied under sheet Master nothing...... then deleted and copied under the workbook. Is it because on the Master sheet column A has a unique record number that I did not want to copy over to Women's Health? I did change the macro columns as I was off one column after I added the URN column(A): Sub CopyYes() Dim rRowRng As Range Dim rColZ As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:X1,AH1,AK1,AM1,AO1,AQ1,AS1,AU1") Set rColZ = Range("Z2", Range("Z" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColZ If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub Thanks for the website .......... looks scary but will start to look through it. Janice "Otto Moehrbach" wrote: Here is a macro to do what you want. This macro is just for the "Women's Health" sheet and for the condition of column Y being "Yes". Note that your file must have a sheet named "Master" and another sheet named "Women's Health". You said that you need to do 5 other sheets using different criteria. That can all be done with just one macro. Tell me what the sheet names are and what the criteria is. Do you want the same columns copied or does that change also? What version of Excel are you using? You said that you don't know macros (VBA). David McRitchie has some notes for getting started with macros at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sub CopyYes() Dim rRowRng As Range Dim rColY As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:W1,AG1,AJ1,AL1,AN1,AP1,AR1,AT1") Set rColY = Range("Y2", Range("Y" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColY If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub "Doehead" wrote in message ... Hi Otto, thanks for replying, I would want to paste them in sequential columns on the new spreadsheet. "Otto Moehrbach" wrote: Let me explain what I mean. Let's say you want to copy 15 columns, or 30 columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Hi Otto,
I right mouse clicked on the Women's Health tab / clicked view code / pasted your calculation / closed the workbook / reopened and enabled macros when asked...I am assuming the macro ran. Also I am using Excel 2003. Did I do it right? Janice "Otto Moehrbach" wrote: What do you mean when you say "copied the macro under sheet Women's Health"? What exactly did you do? Give me a step-by-step. Did you run the macro? Otto "Doehead" wrote in message ... Hi Otto, thank you for all your work on this Macro but it is not doing anything. I copied the macro under sheet Women's Health nothing... deleted then copied under sheet Master nothing...... then deleted and copied under the workbook. Is it because on the Master sheet column A has a unique record number that I did not want to copy over to Women's Health? I did change the macro columns as I was off one column after I added the URN column(A): Sub CopyYes() Dim rRowRng As Range Dim rColZ As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:X1,AH1,AK1,AM1,AO1,AQ1,AS1,AU1") Set rColZ = Range("Z2", Range("Z" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColZ If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub Thanks for the website .......... looks scary but will start to look through it. Janice "Otto Moehrbach" wrote: Here is a macro to do what you want. This macro is just for the "Women's Health" sheet and for the condition of column Y being "Yes". Note that your file must have a sheet named "Master" and another sheet named "Women's Health". You said that you need to do 5 other sheets using different criteria. That can all be done with just one macro. Tell me what the sheet names are and what the criteria is. Do you want the same columns copied or does that change also? What version of Excel are you using? You said that you don't know macros (VBA). David McRitchie has some notes for getting started with macros at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sub CopyYes() Dim rRowRng As Range Dim rColY As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:W1,AG1,AJ1,AL1,AN1,AP1,AR1,AT1") Set rColY = Range("Y2", Range("Y" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColY If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub "Doehead" wrote in message ... Hi Otto, thanks for replying, I would want to paste them in sequential columns on the new spreadsheet. "Otto Moehrbach" wrote: Let me explain what I mean. Let's say you want to copy 15 columns, or 30 columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Otto's macro should be stored in a general module and is run manually by
ToolsMacroMacros. Select and run. It is not event code so won't run automatically. Gord Dibben MS Excel MVP On Tue, 14 Oct 2008 20:01:00 -0700, Doehead wrote: Hi Otto, I right mouse clicked on the Women's Health tab / clicked view code / pasted your calculation / closed the workbook / reopened and enabled macros when asked...I am assuming the macro ran. Also I am using Excel 2003. Did I do it right? Janice "Otto Moehrbach" wrote: What do you mean when you say "copied the macro under sheet Women's Health"? What exactly did you do? Give me a step-by-step. Did you run the macro? Otto "Doehead" wrote in message ... Hi Otto, thank you for all your work on this Macro but it is not doing anything. I copied the macro under sheet Women's Health nothing... deleted then copied under sheet Master nothing...... then deleted and copied under the workbook. Is it because on the Master sheet column A has a unique record number that I did not want to copy over to Women's Health? I did change the macro columns as I was off one column after I added the URN column(A): Sub CopyYes() Dim rRowRng As Range Dim rColZ As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:X1,AH1,AK1,AM1,AO1,AQ1,AS1,AU1") Set rColZ = Range("Z2", Range("Z" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColZ If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub Thanks for the website .......... looks scary but will start to look through it. Janice "Otto Moehrbach" wrote: Here is a macro to do what you want. This macro is just for the "Women's Health" sheet and for the condition of column Y being "Yes". Note that your file must have a sheet named "Master" and another sheet named "Women's Health". You said that you need to do 5 other sheets using different criteria. That can all be done with just one macro. Tell me what the sheet names are and what the criteria is. Do you want the same columns copied or does that change also? What version of Excel are you using? You said that you don't know macros (VBA). David McRitchie has some notes for getting started with macros at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sub CopyYes() Dim rRowRng As Range Dim rColY As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:W1,AG1,AJ1,AL1,AN1,AP1,AR1,AT1") Set rColY = Range("Y2", Range("Y" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColY If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub "Doehead" wrote in message ... Hi Otto, thanks for replying, I would want to paste them in sequential columns on the new spreadsheet. "Otto Moehrbach" wrote: Let me explain what I mean. Let's say you want to copy 15 columns, or 30 columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Gord is right. Come back if you need more. Otto
"Gord Dibben" <gorddibbATshawDOTca wrote in message ... Otto's macro should be stored in a general module and is run manually by ToolsMacroMacros. Select and run. It is not event code so won't run automatically. Gord Dibben MS Excel MVP On Tue, 14 Oct 2008 20:01:00 -0700, Doehead wrote: Hi Otto, I right mouse clicked on the Women's Health tab / clicked view code / pasted your calculation / closed the workbook / reopened and enabled macros when asked...I am assuming the macro ran. Also I am using Excel 2003. Did I do it right? Janice "Otto Moehrbach" wrote: What do you mean when you say "copied the macro under sheet Women's Health"? What exactly did you do? Give me a step-by-step. Did you run the macro? Otto "Doehead" wrote in message ... Hi Otto, thank you for all your work on this Macro but it is not doing anything. I copied the macro under sheet Women's Health nothing... deleted then copied under sheet Master nothing...... then deleted and copied under the workbook. Is it because on the Master sheet column A has a unique record number that I did not want to copy over to Women's Health? I did change the macro columns as I was off one column after I added the URN column(A): Sub CopyYes() Dim rRowRng As Range Dim rColZ As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:X1,AH1,AK1,AM1,AO1,AQ1,AS1,AU1") Set rColZ = Range("Z2", Range("Z" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColZ If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub Thanks for the website .......... looks scary but will start to look through it. Janice "Otto Moehrbach" wrote: Here is a macro to do what you want. This macro is just for the "Women's Health" sheet and for the condition of column Y being "Yes". Note that your file must have a sheet named "Master" and another sheet named "Women's Health". You said that you need to do 5 other sheets using different criteria. That can all be done with just one macro. Tell me what the sheet names are and what the criteria is. Do you want the same columns copied or does that change also? What version of Excel are you using? You said that you don't know macros (VBA). David McRitchie has some notes for getting started with macros at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sub CopyYes() Dim rRowRng As Range Dim rColY As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:W1,AG1,AJ1,AL1,AN1,AP1,AR1,AT1") Set rColY = Range("Y2", Range("Y" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColY If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub "Doehead" wrote in message ... Hi Otto, thanks for replying, I would want to paste them in sequential columns on the new spreadsheet. "Otto Moehrbach" wrote: Let me explain what I mean. Let's say you want to copy 15 columns, or 30 columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Thanks Gary I did it!!!!! With a bit of searching on 'how to'.
Otto, I have a few more questions before I leave you alone: What this gal is trying to do is enter all information on a Master sheet. From there she wanted me to create separate sheets for other end users (basically viewing and printing purposes only) plus a "closed" sheet; that way she can autofilter on "Open" and only see in the Master what is outstanding (I have done the closed sheet using the IF statement). 1. I am assuming that I can use your macro (with minor changes to column names) to create the same results in Women's Health for the other worksheets correct? 2. I have tested the workbook and unless you run the macro everytime it does not automatically update the Women's Health sheet. Can we automate it? (if you think am slow, wait til I have to explain to the end users or the gal what they have to do to see updated info in the spreadsheets!). 3. Also I am curious from my searches........ to leave basically all formating as 'general', which I have except for date field. Why is that? Thanks again for your assistance. Janice "Otto Moehrbach" wrote: Gord is right. Come back if you need more. Otto "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Otto's macro should be stored in a general module and is run manually by ToolsMacroMacros. Select and run. It is not event code so won't run automatically. Gord Dibben MS Excel MVP On Tue, 14 Oct 2008 20:01:00 -0700, Doehead wrote: Hi Otto, I right mouse clicked on the Women's Health tab / clicked view code / pasted your calculation / closed the workbook / reopened and enabled macros when asked...I am assuming the macro ran. Also I am using Excel 2003. Did I do it right? Janice "Otto Moehrbach" wrote: What do you mean when you say "copied the macro under sheet Women's Health"? What exactly did you do? Give me a step-by-step. Did you run the macro? Otto "Doehead" wrote in message ... Hi Otto, thank you for all your work on this Macro but it is not doing anything. I copied the macro under sheet Women's Health nothing... deleted then copied under sheet Master nothing...... then deleted and copied under the workbook. Is it because on the Master sheet column A has a unique record number that I did not want to copy over to Women's Health? I did change the macro columns as I was off one column after I added the URN column(A): Sub CopyYes() Dim rRowRng As Range Dim rColZ As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:X1,AH1,AK1,AM1,AO1,AQ1,AS1,AU1") Set rColZ = Range("Z2", Range("Z" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColZ If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub Thanks for the website .......... looks scary but will start to look through it. Janice "Otto Moehrbach" wrote: Here is a macro to do what you want. This macro is just for the "Women's Health" sheet and for the condition of column Y being "Yes". Note that your file must have a sheet named "Master" and another sheet named "Women's Health". You said that you need to do 5 other sheets using different criteria. That can all be done with just one macro. Tell me what the sheet names are and what the criteria is. Do you want the same columns copied or does that change also? What version of Excel are you using? You said that you don't know macros (VBA). David McRitchie has some notes for getting started with macros at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sub CopyYes() Dim rRowRng As Range Dim rColY As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:W1,AG1,AJ1,AL1,AN1,AP1,AR1,AT1") Set rColY = Range("Y2", Range("Y" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColY If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub "Doehead" wrote in message ... Hi Otto, thanks for replying, I would want to paste them in sequential columns on the new spreadsheet. "Otto Moehrbach" wrote: Let me explain what I mean. Let's say you want to copy 15 columns, or 30 columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Janice
You say: Also I am curious from my searches........ to leave basically all formatting as 'general', which I have except for date field. Why is that? I don't know what you were reading. Basically, you should format whatever cells/columns/rows/sheets the way you want. Only if you don't have a specific format in mind should you leave it in General. You say: .. I have tested the workbook and unless you run the macro every time it does not automatically update the Women's Health sheet. Can we automate it? (if you think am slow, wait til I have to explain to the end users or the gal what they have to do to see updated info in the spreadsheets!). Yes, you can automate it, but you must tie the running of the macro to some event that Excel recognizes. Excel recognizes many events. Some examples a Opening the file Closing the file Saving the file Printing something from the file Selecting a cell Making a cell entry Selecting a sheet Many more Excel will do anything you want (run the macro) when an event of your choosing occurs. So the ball is back in your court: When do you want this macro to run? You say: I am assuming that I can use your macro (with minor changes to column names) to create the same results in Women's Health for the other worksheets correct? Yes, you can, as long as you make the necessary changes. But why not make Excel do the work for you? I can write the macro to do all the sheets at one time. If you want this, are the columns to be copied the same in each case? If not, what are the columns for each sheet? What are the other sheet names? Otto "Doehead" wrote in message ... Thanks Gary I did it!!!!! With a bit of searching on 'how to'. Otto, I have a few more questions before I leave you alone: What this gal is trying to do is enter all information on a Master sheet. From there she wanted me to create separate sheets for other end users (basically viewing and printing purposes only) plus a "closed" sheet; that way she can autofilter on "Open" and only see in the Master what is outstanding (I have done the closed sheet using the IF statement). 1. I am assuming that I can use your macro (with minor changes to column names) to create the same results in Women's Health for the other worksheets correct? 2. I have tested the workbook and unless you run the macro everytime it does not automatically update the Women's Health sheet. Can we automate it? (if you think am slow, wait til I have to explain to the end users or the gal what they have to do to see updated info in the spreadsheets!). 3. Also I am curious from my searches........ to leave basically all formating as 'general', which I have except for date field. Why is that? Thanks again for your assistance. Janice "Otto Moehrbach" wrote: Gord is right. Come back if you need more. Otto "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Otto's macro should be stored in a general module and is run manually by ToolsMacroMacros. Select and run. It is not event code so won't run automatically. Gord Dibben MS Excel MVP On Tue, 14 Oct 2008 20:01:00 -0700, Doehead wrote: Hi Otto, I right mouse clicked on the Women's Health tab / clicked view code / pasted your calculation / closed the workbook / reopened and enabled macros when asked...I am assuming the macro ran. Also I am using Excel 2003. Did I do it right? Janice "Otto Moehrbach" wrote: What do you mean when you say "copied the macro under sheet Women's Health"? What exactly did you do? Give me a step-by-step. Did you run the macro? Otto "Doehead" wrote in message ... Hi Otto, thank you for all your work on this Macro but it is not doing anything. I copied the macro under sheet Women's Health nothing... deleted then copied under sheet Master nothing...... then deleted and copied under the workbook. Is it because on the Master sheet column A has a unique record number that I did not want to copy over to Women's Health? I did change the macro columns as I was off one column after I added the URN column(A): Sub CopyYes() Dim rRowRng As Range Dim rColZ As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:X1,AH1,AK1,AM1,AO1,AQ1,AS1,AU1") Set rColZ = Range("Z2", Range("Z" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColZ If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub Thanks for the website .......... looks scary but will start to look through it. Janice "Otto Moehrbach" wrote: Here is a macro to do what you want. This macro is just for the "Women's Health" sheet and for the condition of column Y being "Yes". Note that your file must have a sheet named "Master" and another sheet named "Women's Health". You said that you need to do 5 other sheets using different criteria. That can all be done with just one macro. Tell me what the sheet names are and what the criteria is. Do you want the same columns copied or does that change also? What version of Excel are you using? You said that you don't know macros (VBA). David McRitchie has some notes for getting started with macros at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sub CopyYes() Dim rRowRng As Range Dim rColY As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:W1,AG1,AJ1,AL1,AN1,AP1,AR1,AT1") Set rColY = Range("Y2", Range("Y" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColY If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub "Doehead" wrote in message ... Hi Otto, thanks for replying, I would want to paste them in sequential columns on the new spreadsheet. "Otto Moehrbach" wrote: Let me explain what I mean. Let's say you want to copy 15 columns, or 30 columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, data from row 2 down, with key col X & criteria value: Closed On new sheet: Closed Placed in A2: =IF(Master!X2="","",IF(OR(Master!F2="Closed"),ROW( ),"")) Left A1 blank In B2: =IF(ROWS($1:1)COUNT($A:$A),"",INDEX(x!A:A,SMALL($ A:$A,ROWS($1:1)))) This formula has worked for me in the past for another workbook when I have had to copy over a complete row to another sheet. Bigger problem..I have been asked to copy only certain columns to a different sheet in the same workbook. Again source data is in sheet: Master, cols A to AU Data from row 2 down, with key col Y & criteria value: Yes On sheet: Women's Health Need to copy only columns B through W plus AG,AJ,AL,AN,AP,AR and AT when the column Y criteria is Yes. There is actually 5 other sheets they want me to create using different criteria, can Excel do this? I do not how to do macros or VB, please help. Thanks |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Thanks for your response Otto, I have given this information to the gal so
she can make the decision on what columns to show on each sheet and what event she wants to trigger updating the sheets (she has already changed her mine on the Women's Health sheet). I will respond once she has decided. Until then thanks for all your help, I have learned a lot in this past week. Janice "Otto Moehrbach" wrote: Janice You say: Also I am curious from my searches........ to leave basically all formatting as 'general', which I have except for date field. Why is that? I don't know what you were reading. Basically, you should format whatever cells/columns/rows/sheets the way you want. Only if you don't have a specific format in mind should you leave it in General. You say: .. I have tested the workbook and unless you run the macro every time it does not automatically update the Women's Health sheet. Can we automate it? (if you think am slow, wait til I have to explain to the end users or the gal what they have to do to see updated info in the spreadsheets!). Yes, you can automate it, but you must tie the running of the macro to some event that Excel recognizes. Excel recognizes many events. Some examples a Opening the file Closing the file Saving the file Printing something from the file Selecting a cell Making a cell entry Selecting a sheet Many more Excel will do anything you want (run the macro) when an event of your choosing occurs. So the ball is back in your court: When do you want this macro to run? You say: I am assuming that I can use your macro (with minor changes to column names) to create the same results in Women's Health for the other worksheets correct? Yes, you can, as long as you make the necessary changes. But why not make Excel do the work for you? I can write the macro to do all the sheets at one time. If you want this, are the columns to be copied the same in each case? If not, what are the columns for each sheet? What are the other sheet names? Otto "Doehead" wrote in message ... Thanks Gary I did it!!!!! With a bit of searching on 'how to'. Otto, I have a few more questions before I leave you alone: What this gal is trying to do is enter all information on a Master sheet. From there she wanted me to create separate sheets for other end users (basically viewing and printing purposes only) plus a "closed" sheet; that way she can autofilter on "Open" and only see in the Master what is outstanding (I have done the closed sheet using the IF statement). 1. I am assuming that I can use your macro (with minor changes to column names) to create the same results in Women's Health for the other worksheets correct? 2. I have tested the workbook and unless you run the macro everytime it does not automatically update the Women's Health sheet. Can we automate it? (if you think am slow, wait til I have to explain to the end users or the gal what they have to do to see updated info in the spreadsheets!). 3. Also I am curious from my searches........ to leave basically all formating as 'general', which I have except for date field. Why is that? Thanks again for your assistance. Janice "Otto Moehrbach" wrote: Gord is right. Come back if you need more. Otto "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Otto's macro should be stored in a general module and is run manually by ToolsMacroMacros. Select and run. It is not event code so won't run automatically. Gord Dibben MS Excel MVP On Tue, 14 Oct 2008 20:01:00 -0700, Doehead wrote: Hi Otto, I right mouse clicked on the Women's Health tab / clicked view code / pasted your calculation / closed the workbook / reopened and enabled macros when asked...I am assuming the macro ran. Also I am using Excel 2003. Did I do it right? Janice "Otto Moehrbach" wrote: What do you mean when you say "copied the macro under sheet Women's Health"? What exactly did you do? Give me a step-by-step. Did you run the macro? Otto "Doehead" wrote in message ... Hi Otto, thank you for all your work on this Macro but it is not doing anything. I copied the macro under sheet Women's Health nothing... deleted then copied under sheet Master nothing...... then deleted and copied under the workbook. Is it because on the Master sheet column A has a unique record number that I did not want to copy over to Women's Health? I did change the macro columns as I was off one column after I added the URN column(A): Sub CopyYes() Dim rRowRng As Range Dim rColZ As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:X1,AH1,AK1,AM1,AO1,AQ1,AS1,AU1") Set rColZ = Range("Z2", Range("Z" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColZ If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub Thanks for the website .......... looks scary but will start to look through it. Janice "Otto Moehrbach" wrote: Here is a macro to do what you want. This macro is just for the "Women's Health" sheet and for the condition of column Y being "Yes". Note that your file must have a sheet named "Master" and another sheet named "Women's Health". You said that you need to do 5 other sheets using different criteria. That can all be done with just one macro. Tell me what the sheet names are and what the criteria is. Do you want the same columns copied or does that change also? What version of Excel are you using? You said that you don't know macros (VBA). David McRitchie has some notes for getting started with macros at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sub CopyYes() Dim rRowRng As Range Dim rColY As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:W1,AG1,AJ1,AL1,AN1,AP1,AR1,AT1") Set rColY = Range("Y2", Range("Y" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColY If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub "Doehead" wrote in message ... Hi Otto, thanks for replying, I would want to paste them in sequential columns on the new spreadsheet. "Otto Moehrbach" wrote: Let me explain what I mean. Let's say you want to copy 15 columns, or 30 columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... Hi Otto, I want the destination columns to only include specific source columns. Source columns: A through to AU Destination columns: would start at A but would copy over B through W plus AG,AJ,AL,AN,AP,AR and AT from the source spreadsheet. I have created the destination header row to match the specific source header titles that I need in the destination spreadsheet. Does this make sense? Thanks, Doehead "Otto Moehrbach" wrote: Do you want the destination columns to be the same columns as the Source columns or do you want to copy the Source columns then paste them in sequential columns? Starting with what Destination column? HTH Otto "Doehead" wrote in message ... Hi all, I am trying to copy a complete row of data to another sheet when it meets the criteria: Source data is in sheet: Master, cols A to AU, |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
2 formulas needed same workbook
Thanks Otto, I should have all the specifications by Tuesday will email then.
Janice "Otto Moehrbach" wrote: Janice If you wish, we can do this with email. My email address is . Remove the "extra" from this address. Otto "Doehead" wrote in message ... Thanks for your response Otto, I have given this information to the gal so she can make the decision on what columns to show on each sheet and what event she wants to trigger updating the sheets (she has already changed her mine on the Women's Health sheet). I will respond once she has decided. Until then thanks for all your help, I have learned a lot in this past week. Janice "Otto Moehrbach" wrote: Janice You say: Also I am curious from my searches........ to leave basically all formatting as 'general', which I have except for date field. Why is that? I don't know what you were reading. Basically, you should format whatever cells/columns/rows/sheets the way you want. Only if you don't have a specific format in mind should you leave it in General. You say: .. I have tested the workbook and unless you run the macro every time it does not automatically update the Women's Health sheet. Can we automate it? (if you think am slow, wait til I have to explain to the end users or the gal what they have to do to see updated info in the spreadsheets!). Yes, you can automate it, but you must tie the running of the macro to some event that Excel recognizes. Excel recognizes many events. Some examples a Opening the file Closing the file Saving the file Printing something from the file Selecting a cell Making a cell entry Selecting a sheet Many more Excel will do anything you want (run the macro) when an event of your choosing occurs. So the ball is back in your court: When do you want this macro to run? You say: I am assuming that I can use your macro (with minor changes to column names) to create the same results in Women's Health for the other worksheets correct? Yes, you can, as long as you make the necessary changes. But why not make Excel do the work for you? I can write the macro to do all the sheets at one time. If you want this, are the columns to be copied the same in each case? If not, what are the columns for each sheet? What are the other sheet names? Otto "Doehead" wrote in message ... Thanks Gary I did it!!!!! With a bit of searching on 'how to'. Otto, I have a few more questions before I leave you alone: What this gal is trying to do is enter all information on a Master sheet. From there she wanted me to create separate sheets for other end users (basically viewing and printing purposes only) plus a "closed" sheet; that way she can autofilter on "Open" and only see in the Master what is outstanding (I have done the closed sheet using the IF statement). 1. I am assuming that I can use your macro (with minor changes to column names) to create the same results in Women's Health for the other worksheets correct? 2. I have tested the workbook and unless you run the macro everytime it does not automatically update the Women's Health sheet. Can we automate it? (if you think am slow, wait til I have to explain to the end users or the gal what they have to do to see updated info in the spreadsheets!). 3. Also I am curious from my searches........ to leave basically all formating as 'general', which I have except for date field. Why is that? Thanks again for your assistance. Janice "Otto Moehrbach" wrote: Gord is right. Come back if you need more. Otto "Gord Dibben" <gorddibbATshawDOTca wrote in message ... Otto's macro should be stored in a general module and is run manually by ToolsMacroMacros. Select and run. It is not event code so won't run automatically. Gord Dibben MS Excel MVP On Tue, 14 Oct 2008 20:01:00 -0700, Doehead wrote: Hi Otto, I right mouse clicked on the Women's Health tab / clicked view code / pasted your calculation / closed the workbook / reopened and enabled macros when asked...I am assuming the macro ran. Also I am using Excel 2003. Did I do it right? Janice "Otto Moehrbach" wrote: What do you mean when you say "copied the macro under sheet Women's Health"? What exactly did you do? Give me a step-by-step. Did you run the macro? Otto "Doehead" wrote in message ... Hi Otto, thank you for all your work on this Macro but it is not doing anything. I copied the macro under sheet Women's Health nothing... deleted then copied under sheet Master nothing...... then deleted and copied under the workbook. Is it because on the Master sheet column A has a unique record number that I did not want to copy over to Women's Health? I did change the macro columns as I was off one column after I added the URN column(A): Sub CopyYes() Dim rRowRng As Range Dim rColZ As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:X1,AH1,AK1,AM1,AO1,AQ1,AS1,AU1") Set rColZ = Range("Z2", Range("Z" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColZ If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub Thanks for the website .......... looks scary but will start to look through it. Janice "Otto Moehrbach" wrote: Here is a macro to do what you want. This macro is just for the "Women's Health" sheet and for the condition of column Y being "Yes". Note that your file must have a sheet named "Master" and another sheet named "Women's Health". You said that you need to do 5 other sheets using different criteria. That can all be done with just one macro. Tell me what the sheet names are and what the criteria is. Do you want the same columns copied or does that change also? What version of Excel are you using? You said that you don't know macros (VBA). David McRitchie has some notes for getting started with macros at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Sub CopyYes() Dim rRowRng As Range Dim rColY As Range Dim i As Range Dim Dest As Range Application.ScreenUpdating = False Sheets("Master").Select Set rRowRng = Range("B1:W1,AG1,AJ1,AL1,AN1,AP1,AR1,AT1") Set rColY = Range("Y2", Range("Y" & Rows.Count).End(xlUp)) With Sheets("Women's Health") Set Dest = .Range("A" & Rows.Count).End(xlUp).Offset(1) End With For Each i In rColY If UCase(i.Value) = "YES" Then rRowRng.Offset(i.Row - 1).Copy Dest Set Dest = Dest.Offset(1) End If Next i Application.ScreenUpdating = True End Sub "Doehead" wrote in message ... Hi Otto, thanks for replying, I would want to paste them in sequential columns on the new spreadsheet. "Otto Moehrbach" wrote: Let me explain what I mean. Let's say you want to copy 15 columns, or 30 columns, or 50, it doesn't matter. They are not sequential columns, maybe some are, and they are scattered all over the place, just like your source columns are. Copying those columns is easy in VBA. OK, we're done with copying. Now let's talk about pasting all this data we just copied. This is the question I have. A pasting question. Do you want to paste these umpteen columns of data into sequential columns? That is easy in VBA. Or do you want them pasted into non-sequential columns? If so, what columns? HTH Otto "Doehead" wrote in message ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formulas needed .. thanks | Excel Worksheet Functions | |||
IF FORMULAS NEEDED | Excel Worksheet Functions | |||
Help needed on Date formulas | Excel Worksheet Functions | |||
Help With Formulas Needed | Excel Worksheet Functions | |||
Help needed on formulas | New Users to Excel |