Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 195
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 195
Default 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)??

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 195
Default 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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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

  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 195
Default 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


  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default 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



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 195
Default macro paste by matching dates

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I email amacro? leo Excel Worksheet Functions 24 August 9th 06 02:47 PM
Copy and Paste Macro for front totals sheet [email protected] Excel Discussion (Misc queries) 0 May 5th 06 05:12 PM
Macro copy and paste = blank worksheet efface Excel Discussion (Misc queries) 1 April 27th 06 09:52 PM
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
RECORDED MACRO PASTE'S DATE DIFFERENTLY TO MANUAL PASTE Pauldecan Excel Worksheet Functions 0 June 23rd 05 05:45 PM


All times are GMT +1. The time now is 01:58 PM.

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

About Us

"It's about Microsoft Excel"