Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Macro to copy specific columns to new worksheet

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    South Bend, IN
    Posts
    996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


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

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Where the wild roses grow
    Posts
    285
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Try:

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

    Audiojoe

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    South Bend, IN
    Posts
    996
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •