macro paste by matching dates

[arepemko via OfficeKB.com]

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

 

[stevebriz]

where do you get the total 25,45, and 15 from?

 

[arepemko via OfficeKB.com]

There just points that are added up when i make a selection from my drop down
combo box.

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.aspx/ms-excel/200609/1

 

[stevebriz]

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)??

 

[arepemko via OfficeKB.com]

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

 

[arepemko via OfficeKB.com]

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?

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)??

 

[stevebriz]

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

 

[arepemko via OfficeKB.com]

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]

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]

so its stevebrizAT?DOTcom

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.aspx/ms-excel/200609/1

 

[stevebriz]

Check your email...i sent you one from steve_briz at hotmail