Copy and paste macro

[Reggie64]

Hi there,

I'm currently trying to create a simple copy and paste macro on excel 2003.

All i want to do is copy a column of data into excel, copy and transpose it into rows and store in a list below, i can get the macro to copy and transpose the data but after that i want each column of data i enter to be stored in the next blank cell.

This is the basic code i have been using for the copying and transposing:

Range("B3:B22").Select
Selection.Copy
Range("A27").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

I know it's very basic but i need a line of code in there to make it select and paste to the next available blank cell.


Any help would be greatly appreciated, Please??

 

[VoG]

Try

Range("B3:B22").Copy
Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll, Transpose:=True

 

[Reggie64]

****, thought that'd work, its still just pasting into the one row.

Thanks for your quick reply though!

 

[VoG]

If column A is empty maybe

Range("B3:B22").Copy
Range("B" & Rows.Count).End(xlUp).Offset(1, -1).PasteSpecial Paste:=xlPasteAll, Transpose:=True

 

[Reggie64]

You my friend, are a life saver.

Thank you so much.

 

[sunwf01]

Hi,
I need some help with editing my macro. I am trying to do a copy & paste value command to a range of cells ("B3:B121). How should I go about doing this? Please help as I have hardly any experience in VBA. All the help I got were from going through these forums. Thanks a million!

 

[mejim]

This tip is going to help me a lot too. Thanks for sharing it.