Macro to paste on the cell that you have it on

[squeakums]

I cannot remember how to do it but instead of telling a macro to paste special text into a range or field - what the the vba used to paste into where it is selected:

Need to change - Range("m5").Select

Also, for future pastes, I want it to paste (0,1) over from where it is sitting.

Here is part of the macro

Cells.Find(What:="ACD Calls", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Selection.Copy
Windows("June '12 - All In 1st + Transfers.xlsx").Activate
Sheets("CAR").Select
Range("m5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Please help

 

[JLGWhiz]

I cannot remember how to do it but instead of telling a macro to paste special text into a range or field - what the the vba used to paste into where it is selected:

Need to change - Range("m5").Select

Also, for future pastes, I want it to paste (0,1) over from where it is sitting.

Here is part of the macro

Cells.Find(What:="ACD Calls", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Selection.Copy
Windows("June '12 - All In 1st + Transfers.xlsx").Activate
Sheets("CAR").Select
Range("m5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Please help

I am not sure I understand what you want but I think it is something like this. You would need to add the Dim statements to your declarations.
The code sets your other workbook and worksheet to object variables. It uses the last column variable to post future copy actions to the next availabel cell on row 5 once cell M5 has data added to it. You can try it and see if this is what you want. It replaces the code in the OP entirely.

Code:
Dim desWB As Workbook, desSh As Worksheet, lc As Long
Set desWB = Workbooks("June '12 - A;; Om 1st + Transfers.xlsx")
Set desSh = desWB.Sheets("CAR")
lc = desSh.Cells(5, Columns.Count).End(xlToLeft).Column + 1
Cells.Find(What:="ACD Calls", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
If desSh.Range("M5") <> "" Then
ActiveCell.Offset(1, 0).Copy desSh.Cells(5, lc)
Else
ActiveCell.Offset(1, 0).Copy desSh.Range("M5")
End If
Code:

 

[HalfAce]

How about reversing the logic on this to both shorten up the code and eliminate the need for copying (and therefore pasting special).
I don't know the name of your sheet that contains the search term ("ACD Calls") so I just used 'Sheet1'. This example gets run from the sheet you want the entry going into instead of starting from the sheet containing the search term.
(You can replace Sheet1 in my example with the sheet's real name and try something like this.)

WhatImLookingFor = Sheets("Sheet1").Cells.Find(What:="ACD Calls", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)(2)
ActiveCell.Value = WhatImLookingFor

It will find the search term on sheet1 and return the value of the cell below it on the active sheet, in the active cell.

Does that help?

 

[squeakums]

How about reversing the logic on this to both shorten up the code and eliminate the need for copying (and therefore pasting special).
I don't know the name of your sheet that contains the search term ("ACD Calls") so I just used 'Sheet1'. This example gets run from the sheet you want the entry going into instead of starting from the sheet containing the search term.
(You can replace Sheet1 in my example with the sheet's real name and try something like this.)

WhatImLookingFor = Sheets("Sheet1").Cells.Find(What:="ACD Calls", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)(2)
ActiveCell.Value = WhatImLookingFor

It will find the search term on sheet1 and return the value of the cell below it on the active sheet, in the active cell.

Does that help?

That might work. But, how would I get it to continue by pasting into the following cell such as move over one and paste after first run.

 

[HalfAce]

how would I get it to continue by pasting into the following cell such as move over one and paste after first run

Why? - are you running this in a loop routine?
If so, can we see your loop code? (It will be much quicker & easier to answer your question)

 

[squeakums]

That might work. But, how would I get it to continue by pasting into the following cell such as move over one and paste after first run.

Okay, I am not sure if there is a better way to do this. But, the point of the macro is to search the total field and then move one down from that and take that information and go back to "operations"' excel spreadsheet which is a different spreadsheet. This spreadsheet has dates starting from the left hand side for the entire month. Each day it will be the previous dates run. So, instead of writing a macro for each date (or if there is a better way) I am trying to get it to put this data into the excel document for each of the 6 fields that I pull from the other document. In total it is pulling about 50 things and putting it into this document. Does that make since? So, I am trying to write a macro that will just start putting in on the line that I have it on - for example June 24th - I clicked on that line and when I run the macro it will start posting on that line, moving one over and posting on that line from the information it has copied.

 

[HalfAce]

Can you use the macro recorder to record all of your actions going through this manually and post that code?
Simply 'moving over to the next cell' is easy enough with something like:
ActiveCell.Offset(, 1).Select
or
ActiveCell(, 2).Paste
or something like that, but again, it would be much easier to go right to a workable solution if we could see exactly what you're doing.

 

[squeakums]

Like the following. But, instead of pasting special into R5 - I want it to pastespecial into the active cell that was already selected on that sheet. Then, move on to copy next item on other sheet and move (0,1) to paste into the next cell over.

""Sub CAR_19()
'

'

'
Windows("CAR-billing.xls").Activate
Sheets("CAR-billing").Select
Range("A1").Select



Cells.Find(What:="ACD Calls", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, 0).Select
Selection.Copy
Windows("June '12 - All In 1st + Transfers.xlsx").Activate
Sheets("CAR").Select
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Windows("CAR-billing.xls").Activate
Sheets("CAR-billing").Select
Cells.Find(What:="ACD Calls", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Activate
ActiveCell.Offset(1, 1).Select
Selection.Copy
Windows("June '12 - All In 1st + Transfers.xlsx").Activate
Sheets("CAR").Select
Range("d5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False""