paste special macro

[aileen]

I am copying data from 3 different columns in 3 different workbooks and
pasting the data into one column in a 4th workbook. The range of data changes
per copied column so I am wondering if it is possible to have a macro that
looks for the first blank space in the 4th workbooks column and pastes from
there down.
e.g.

555 888 111
777 333 1010
999 222 444
1212

The column that I paste to should look like this:

555
777
999
1212
888
333
222
111
1010
444

Any help is always appreciated. Thanks!

 

[Mike H]

Hi,

This finds the row of cell below the last entry in column A of sheet 1 of
book2.xls

lastrow = Workbooks("Book2.xls").Sheets("Sheet1").Cells(Rows.Count,
"A").End(xlUp).Row + 1

this pastes copied data into that cell
Workbooks("Book2.xls").Sheets("Sheet1").Range("A" & lastrow).PasteSpecial

Mike

 

[aileen]

Mike,

This is the code I am using now with your information included, but I am
getting and "Object doesn't support this property or method" error. any
ideas?

Windows("pgvtrades.csv").Activate
Columns("H:H").Select
Selection.Copy
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Windows("doneaway.csv").Activate
Range("O:O").Select
ActiveSheet.Paste
Windows("qictrades.csv").Activate
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Windows("doneaway.csv").Activate
Workbooks("doneaway.csv").Sheets("doneaway").Cells(Rows.Count,
"O").End(xlUp).Row 1
Workbooks("doneaway.csv").Sheets("doneaway").Range("O" &
lastrow).PasteSpecial
Windows("ciqtrades.csv").Activate
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Selection.SpecialCells(xlCellTypeConstants, 23).Select
Windows("doneaway.csv").Activate
Workbooks("doneaway.csv").Sheets("doneaway").Cells(Rows.Count,
"O").End(xlUp).Row 1
Workbooks("doneaway.csv").Sheets("doneaway").Range("O" &
lastrow).PasteSpecial

 

[aileen]

Nevermind, I typed it wrong. Sorry. Thanks for the quick response!

 

[Mike H]

hi,

You don't need to do all the activating and selecting. here I've simplified
the first few lines and you should be able to follow this and do the rest.
Note you cant copy full columns and then try to paste it into a range < 1
full column so I use lastrow befor every copy

Sub test1()
lastrow = Workbooks("pgvtrades.xls").Sheets("Sheet1").Cells(Rows.Count,
"H").End(xlUp).Row
Workbooks("pgvtrades.xls").Sheets("sheet1").Range("H1:H" & lastrow).Copy

Workbooks("doneaway.csv").Sheets("doneaway").Range("O1").PasteSpecial

lastrow = Workbooks("qictrades.csv").Sheets("Sheet1").Cells(Rows.Count,
"H").End(xlUp).Row
Workbooks("qictrades.csv").Sheets("Sheet1").Range("H1:H" & lastrow).Copy

lastrow = Workbooks("doneaway.csv").Sheets("doneaway").Cells(Rows.Count,
"O").End(xlUp).Row


Workbooks("doneaway.csv").Sheets("doneaway").Range("O" & lastrow +
1).PasteSpecial


End Sub

 

[aileen]

That worked great and simplified my code. Thanks so much.