Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Selecting the row below

  Asked By: Gracie    Date: Sep 21    Category: MS Office    Views: 906
  

I want to use a macro to copy a
column of values, the Range is F6:F25 useing special paste to get the
values(not the formulas that are in the cells)and transpose them then
into a row(in this case row AJ51:Bc51). This I can do easily enough
with the Macro recorder my problem is next time I run the macro I want
to put the values in the row below in Row Aj52: Bc52. Now Ok I can
alter the code in the V.B.E but is there an easier way of doing this ?
I would want to run this macro a number of times each time wanting to
deposit the values in the row below the previous entry.

Share: 

 

8 Answers Found

 
Answer #1    Answered By: Terry Williams     Answered On: Sep 21

If there is nothing in column AJ below the last row  of data you pasted, you
could sue something like this:

Sub AAAA()
Dim NextRow As Long
NextRow& = Range("AJ" & Rows.Count).End(xlUp).Row + 1
Range("F6:F25").Copy
Range("AJ" & NextRow&).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, _
Transpose:=True
End Sub

 
Answer #2    Answered By: Casey Montgomery     Answered On: Sep 21

I tried your code  which unfortunatly did'nt work, Iwas getting an error
message I tried leaving out the & in the (NextRow&) piece of code as the
debugger was pointing to that line , tried running it again and it told me it
failed on Pastespecial so now I'm lost, but thanks for trying

 
Answer #3    Answered By: Jonathan Brown     Answered On: Sep 21

When I copied the code  from your
reply below into the VBE (because I hadn't saved it), several of the lines had
picked up extra spaces.

On the NextRow& line, there was an illegal space between Count). and End, then
another one between (xlUp) and .Row

On the PasteSpecial line, there was an illegal space between PasteSpec and
ial, and another between xlPasteValue and s,

When I removed the extra spaces it worked fine. I will post it again (with
slight revisions). Try again, and watch for any extra spaces the forum is adding
on its own.
Otherwise, I can email you a file directly with the code.

Sub AAAA()
Dim NextRow As Long
NextRow& = Range("AJ" & Rows.Count).End(xlUp).Row + 1
If NextRow& < 51 Then NextRow& = 51
Range("F6:F25").Copy
Range("AJ" & NextRow&).Select
Selection.PasteSpecial Paste:=xlPasteValues, _
Transpose:=True
End Sub

 
Answer #4    Answered By: Zobebah Mizrachi     Answered On: Sep 21

Your code  worked fine this time  around though again there was some
spaces in the code once I had eliminated them it worked ok. Thanks very much for
all your help,

 
Answer #5    Answered By: Chuong Tran     Answered On: Sep 21

If you were using this in one session you could use a static variable for the
row number. You would need a method for setting it the first time  it was used
then increment it each time it was used after that.

However if you close Excel, I think, you would lose the static. Not so good.

If the row  you want to paste  to is always the next row after the last used row
you could use


Range("AJ51").Select
Selection.End(xlDown).Select
Selection.Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Transpose:=True

 
Answer #6    Answered By: Indie Williams     Answered On: Sep 21

I'm Getting same Failed message with your code  Paste special  Failed

 
Answer #7    Answered By: Kuhlbert Schmidt     Answered On: Sep 21

Here too, just take out the extra spaces in "PasteSpecial" and "xlPasteValues"

 
Answer #8    Answered By: Maria Hughes     Answered On: Sep 21

I'm sorted now I
got everything to work properly once the spaces were taken out of the code.

 
Didn't find what you were looking for? Find more on Selecting the row below Or get search suggestion and latest updates.




Tagged: