|
macro paste by matching dates
HERES THE SITUATION I HAVE SHEET1!B6 THAT HAS A DATE. I WANT TO RUN A MARCO
THAT GETS THAT DATE AND LOOKS THREW SHEET4!A:A AND MATCH THAT DATE THEN PASTE SPECIAL VALUES, IN THE FIRST THREE CELLS TO THE RIGHT. EXAMPLE: SHEET 1 A B C D E 1 2 3 4 5 6 9/6/2006 FOR 9/6/2006 I HAVE THREE TOTALS 25 (WHICH WOULD GO IN COLUMN B), 45(COLUMN C) , 15 (COLUMN D). SHEET 2 A B C D 4 1/1/2006 5 1/2/2006 251 9/5/2006 252 9/6/2006 25 45 15 253 9/7/2006 DO YOU HAVE ANY IDEAS? THANK YOU SO MUCH FOR YOUR HELP -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200609/1 |
macro paste by matching dates
where do you get the total 25,45, and 15 from?
arepemko via OfficeKB.com wrote: HERES THE SITUATION I HAVE SHEET1!B6 THAT HAS A DATE. I WANT TO RUN A MARCO THAT GETS THAT DATE AND LOOKS THREW SHEET4!A:A AND MATCH THAT DATE THEN PASTE SPECIAL VALUES, IN THE FIRST THREE CELLS TO THE RIGHT. EXAMPLE: SHEET 1 A B C D E 1 2 3 4 5 6 9/6/2006 FOR 9/6/2006 I HAVE THREE TOTALS 25 (WHICH WOULD GO IN COLUMN B), 45(COLUMN C) , 15 (COLUMN D). SHEET 2 A B C D 4 1/1/2006 5 1/2/2006 251 9/5/2006 252 9/6/2006 25 45 15 253 9/7/2006 DO YOU HAVE ANY IDEAS? THANK YOU SO MUCH FOR YOUR HELP -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200609/1 |
macro paste by matching dates
There just points that are added up when i make a selection from my drop down
combo box. stevebriz wrote: where do you get the total 25,45, and 15 from? HERES THE SITUATION I HAVE SHEET1!B6 THAT HAS A DATE. I WANT TO RUN A MARCO THAT GETS THAT DATE AND LOOKS THREW SHEET4!A:A AND MATCH THAT DATE THEN PASTE [quoted text clipped - 28 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200609/1 -- Message posted via http://www.officekb.com |
macro paste by matching dates
So let me clarify this with you this for a minute .. to see If I
undestand you correctly with the date in Sheet1 B6 you want to search sheet 4 column A for a matching the date from Sht1 B6 Then you beside the matching date in sheet 4 you want to paste the values from sheet2 columns B C D that corresponds to the same date ( sheet1 B6)?? |
macro paste by matching dates
yes exactly, Here is what I have tried so far I recorded a macro, and used
FIND( ctrl+F) to search the workbook for the date I needed and when It found it in Sheet 4 I moved the active cell over from column A to Column B and then I pasted my totals from sheet 1 25,45,15. Sheet 2 is all my formulas which are then transfered over to sheet 1. Im sorry if I confused you im super confused my self. heres an example of what my macro that I recorded looks like. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 9/7/2006 by wtemp2 ' ' Keyboard Shortcut: Ctrl+Shift+Z ' Range("B6").Select Selection.Copy Cells.Find(What:="8/13/2006", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate Sheets("Monthly Sup Perf 1st qtr ").Select Cells.FindNext(After:=ActiveCell).Activate Range("B228").Select Sheets("EVAL.").Select Range("B14").Select Application.CutCopyMode = False Selection.Copy Sheets("Monthly Sup Perf 1st qtr ").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C228").Select Sheets("EVAL.").Select Range("B19").Select Application.CutCopyMode = False Selection.Copy Sheets("Monthly Sup Perf 1st qtr ").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("D228").Select Sheets("EVAL.").Select Range("B23").Select Application.CutCopyMode = False Selection.Copy Sheets("Monthly Sup Perf 1st qtr ").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B229").Select End Sub stevebriz wrote: So let me clarify this with you this for a minute .. to see If I undestand you correctly with the date in Sheet1 B6 you want to search sheet 4 column A for a matching the date from Sht1 B6 Then you beside the matching date in sheet 4 you want to paste the values from sheet2 columns B C D that corresponds to the same date ( sheet1 B6)?? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200609/1 |
macro paste by matching dates
I forgot to add in the macro I posted is there anyway to just change where it
says "8/13/2006" to sheet 1 B6? arepemko wrote: yes exactly, Here is what I have tried so far I recorded a macro, and used FIND( ctrl+F) to search the workbook for the date I needed and when It found it in Sheet 4 I moved the active cell over from column A to Column B and then I pasted my totals from sheet 1 25,45,15. Sheet 2 is all my formulas which are then transfered over to sheet 1. Im sorry if I confused you im super confused my self. heres an example of what my macro that I recorded looks like. Sub Macro1() ' ' Macro1 Macro ' Macro recorded 9/7/2006 by wtemp2 ' ' Keyboard Shortcut: Ctrl+Shift+Z ' Range("B6").Select Selection.Copy Cells.Find(What:="8/13/2006", After:=ActiveCell, LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ MatchCase:=False, SearchFormat:=False).Activate Cells.FindNext(After:=ActiveCell).Activate Sheets("Monthly Sup Perf 1st qtr ").Select Cells.FindNext(After:=ActiveCell).Activate Range("B228").Select Sheets("EVAL.").Select Range("B14").Select Application.CutCopyMode = False Selection.Copy Sheets("Monthly Sup Perf 1st qtr ").Select ActiveSheet.Paste Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("C228").Select Sheets("EVAL.").Select Range("B19").Select Application.CutCopyMode = False Selection.Copy Sheets("Monthly Sup Perf 1st qtr ").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("D228").Select Sheets("EVAL.").Select Range("B23").Select Application.CutCopyMode = False Selection.Copy Sheets("Monthly Sup Perf 1st qtr ").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("B229").Select End Sub So let me clarify this with you this for a minute .. to see If I undestand you correctly [quoted text clipped - 3 lines] values from sheet2 columns B C D that corresponds to the same date ( sheet1 B6)?? -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200609/1 |
macro paste by matching dates
Try this :
I just used sheet1, sheet2 and sheet4. and used col A rows 1-500 on sheets 2 & 4 for the serach ranges...you can change this as needed. Sub CPYACROSS() Application.ScreenUpdating = False ' ==== Get date to use===== Sheet1.Select Dim Sdate As String Sdate = Sheet1.Range("B6").Value ' ====Search and copy section========== Sheet2.Activate With Sheet2.Range("A1:A500") ' looking in column sheet2 column A rows 1-500 Set c = .Find(Sdate, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Sheets(2).Range(Cells(c.Row, 2), Cells(c.Row, 5)).Select Selection.Copy Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With ' ====Search and Paste section========== Sheet4.Select With Sheet4.Range("A1:A500") 'looking in column sheet4 column A rows 1-500 Set d = .Find(Sdate, LookIn:=xlValues) If Not d Is Nothing Then firstAddress = d.Address Do Sheet4.Range(Cells(d.Row, 2), Cells(d.Row, 4)).Select Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone Set d = .FindNext(d) Loop While Not d Is Nothing And d.Address < firstAddress End If End With Sheet1.Select Application.ScreenUpdating = True End Sub |
macro paste by matching dates
Im new to writing macros so I dont know exactly what to change is there any
way I can email you the workbook that im working on so that you can what I am trying to do and maybe I can have a better idea of what im doing. thank you very much for your help. stevebriz wrote: Try this : I just used sheet1, sheet2 and sheet4. and used col A rows 1-500 on sheets 2 & 4 for the serach ranges...you can change this as needed. Sub CPYACROSS() Application.ScreenUpdating = False ' ==== Get date to use===== Sheet1.Select Dim Sdate As String Sdate = Sheet1.Range("B6").Value ' ====Search and copy section========== Sheet2.Activate With Sheet2.Range("A1:A500") ' looking in column sheet2 column A rows 1-500 Set c = .Find(Sdate, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Sheets(2).Range(Cells(c.Row, 2), Cells(c.Row, 5)).Select Selection.Copy Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With ' ====Search and Paste section========== Sheet4.Select With Sheet4.Range("A1:A500") 'looking in column sheet4 column A rows 1-500 Set d = .Find(Sdate, LookIn:=xlValues) If Not d Is Nothing Then firstAddress = d.Address Do Sheet4.Range(Cells(d.Row, 2), Cells(d.Row, 4)).Select Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone Set d = .FindNext(d) Loop While Not d Is Nothing And d.Address < firstAddress End If End With Sheet1.Select Application.ScreenUpdating = True End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200609/1 |
macro paste by matching dates
yes email it to me ..email as shown in this forum
tell me what range you want to search through...and I can fix the sheet names etc. arepemko via OfficeKB.com wrote: Im new to writing macros so I dont know exactly what to change is there any way I can email you the workbook that im working on so that you can what I am trying to do and maybe I can have a better idea of what im doing. thank you very much for your help. stevebriz wrote: Try this : I just used sheet1, sheet2 and sheet4. and used col A rows 1-500 on sheets 2 & 4 for the serach ranges...you can change this as needed. Sub CPYACROSS() Application.ScreenUpdating = False ' ==== Get date to use===== Sheet1.Select Dim Sdate As String Sdate = Sheet1.Range("B6").Value ' ====Search and copy section========== Sheet2.Activate With Sheet2.Range("A1:A500") ' looking in column sheet2 column A rows 1-500 Set c = .Find(Sdate, LookIn:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do Sheets(2).Range(Cells(c.Row, 2), Cells(c.Row, 5)).Select Selection.Copy Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With ' ====Search and Paste section========== Sheet4.Select With Sheet4.Range("A1:A500") 'looking in column sheet4 column A rows 1-500 Set d = .Find(Sdate, LookIn:=xlValues) If Not d Is Nothing Then firstAddress = d.Address Do Sheet4.Range(Cells(d.Row, 2), Cells(d.Row, 4)).Select Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone Set d = .FindNext(d) Loop While Not d Is Nothing And d.Address < firstAddress End If End With Sheet1.Select Application.ScreenUpdating = True End Sub -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200609/1 |
macro paste by matching dates
so its stevebrizAT?DOTcom
stevebriz wrote: yes email it to me ..email as shown in this forum tell me what range you want to search through...and I can fix the sheet names etc. Im new to writing macros so I dont know exactly what to change is there any way I can email you the workbook that im working on so that you can what I am [quoted text clipped - 50 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200609/1 -- Message posted via http://www.officekb.com |
macro paste by matching dates
Check your email...i sent you one from steve_briz at hotmail
|
| All times are GMT +1. The time now is 02:53 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com