| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
paste special macro
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! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
paste special macro
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" wrote: 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! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
paste special macro
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 "Mike H" wrote: 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" wrote: 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! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
paste special macro
Nevermind, I typed it wrong. Sorry. Thanks for the quick response!
"aileen" wrote: 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 "Mike H" wrote: 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" wrote: 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! |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
paste special macro
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" wrote: 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 "Mike H" wrote: 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" wrote: 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! |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
paste special macro
That worked great and simplified my code. Thanks so much.
"Mike H" wrote: 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" wrote: 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 "Mike H" wrote: 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" wrote: 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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Paste special macro | Excel Programming | |||
| Macro Special Paste - Value | Excel Programming | |||
| Cut and Paste using Macro gives paste special method error | Excel Programming | |||
| Dynamic Copy/Paste Special Formulas/Paste Special Values | Excel Programming | |||
| Macro to Paste Special | Excel Programming | |||
Hybrid Mode
