Macro to copy specific columns to new worksheet

UHsoccer · Apr 29th, 2002, 10:36 AM

I need a macro to copy a range of clolumns and rows (say b2:g80) from one of my sheets to a new spreadsheet

Audiojoe · Apr 29th, 2002, 10:40 AM

Try:

Range("B2:G80").Copy
Workbooks.Add
ActiveSheet.Paste

Audiojoe

Mark O'Brien · Apr 29th, 2002, 10:42 AM

Let's say that we are copying you range "B2:G20" from Sheet1 to Sheet2:



Sheets("Sheet1").Range("B2:G80").Copy Destination:=Sheets("Sheet2").Range("B2:G80")

HTH

EDIT:: Oops, didn't see that you needed a new workbook. Use AudioJoe's.
_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-04-29 09:42 ]

UHsoccer · Apr 29th, 2002, 10:54 AM

Thanks, but additional control needed

1) Column widths are destroyed
2) New book has three (un-named) sheet1, sheet2, sheet3. Need one NAMED sheet called "Quote"

I was using (but do not want to hide, but really not copy those columns

Sheets("Quote Tool").Select
Sheets("Quote Tool").Copy
Columns("G:G").ColumnWidth = 3.86
Columns("G:G").Select
Range("G2").Activate
ActiveWindow.ScrollColumn = 2
Columns("G:ak").Select
Range("G2").Activate
Selection.EntireColumn.Hidden = True
ActiveWindow.ScrollColumn = 1

Apr 29th, 2002, 10:42 AM	#3
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	Let's say that we are copying you range "B2:G20" from Sheet1 to Sheet2: Sheets("Sheet1").Range("B2:G80").Copy Destination:=Sheets("Sheet2").Range("B2:G80") HTH EDIT:: Oops, didn't see that you needed a new workbook. Use AudioJoe's. _________________ [b] Mark O'Brien [ This Message was edited by: Mark O'Brien on 2002-04-29 09:42 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 29th, 2002, 10:36 AM	#1
UHsoccer Board Regular Join Date: Apr 2002 Location: Detroit Posts: 792	I need a macro to copy a range of clolumns and rows (say b2:g80) from one of my sheets to a new spreadsheet

Apr 29th, 2002, 10:40 AM	#2
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	Try: Range("B2:G80").Copy Workbooks.Add ActiveSheet.Paste Audiojoe

Apr 29th, 2002, 10:54 AM	#4
UHsoccer Board Regular Join Date: Apr 2002 Location: Detroit Posts: 792	Thanks, but additional control needed 1) Column widths are destroyed 2) New book has three (un-named) sheet1, sheet2, sheet3. Need one NAMED sheet called "Quote" I was using (but do not want to hide, but really not copy those columns Sheets("Quote Tool").Select Sheets("Quote Tool").Copy Columns("G:G").ColumnWidth = 3.86 Columns("G:G").Select Range("G2").Activate ActiveWindow.ScrollColumn = 2 Columns("G:ak").Select Range("G2").Activate Selection.EntireColumn.Hidden = True ActiveWindow.ScrollColumn = 1