creating a macro that will paste a value and then move

  • Thread starter Thread starter davidgary.taylor
  • Start date Start date
D

davidgary.taylor

Please, help!

I am trying to recreate a macro that opens a workbook, goes to Sheet
1, pastes clipboard info into A1, then moves to B1, then saves and
closes the file.

I know how to make the file automatic and how to get it to open and
close (Auto_Open), but I cannot remember how to get the macro to move
from cell to cell (left-to right). What happens is that when I do a
recorded macro and then try to rerun it; it will paste into A1 and
then will tab over to B1. Then upon the next time that I call up the
spreadsheet, it will even paste to B1, but will not move further from
that spot.

I then look at the macro and it shows the following:
***********************************************
Sub auto_open()
'
' pasteagain Macro
' Macro recorded 08/03/2007 by A100132
'
'
Worksheets("Sheet1").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.Range ("A1")
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub
*************************************************

It looks as though it is hanging up at the Selection.Range ("A1")
but, by golly, I just can't remember what I did to get this to work in
the past. Can anyone help me?

Thanks,
David
 
David,

You can use

Selection.Range("B1").Select

or

Selection.Offset(0, 1).Select

Be careful with pasting text. If there are commas, spaces, tabs, etc., in the data, and the
last text-to-columns or text file open or import happened to use commas or spaces or tabs,
it may automatically invoke text-to-columns when it pastes, spreading your data over several
cells to the right, overwriting any existing data, and really confound you with the
unintended consequences of Excels over-achieving nature. If none of those processes has
been done since Excel was opened, then you should be OK.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
David,

Here are a couple more thoughts. If anyone opens the workbook, then changes the selection
and seves the file, it'll cause it to start pasting to right of the newly selected place.
If that's a possibility, and there's always something pasted (never empty), you could
instead use:

Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1).Select

That says "The cell in row 1, last column, then left until you find something, then one to
the right."

If there could be emptiness pasted, and you wanted to keep it empty, and I'm on the right
track as to what you're trying to do in the first place, post back.
--
Earl Kiosterud
www.smokeylake.com

Note: Top-posting has been the norm here.
Some folks prefer bottom-posting.
But if you bottom-post to a reply that's
already top-posted, the thread gets messy.
When in Rome...
 
Back
Top