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

Thread: Macro to copy sheet, then delete one column

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

    Default

    I recorded a macro to copy a sheet and delete column B.
    When I execute the macro, it copy the sheet and deletes column B but it ALSO eliminates column A. Not good
    Here is the code that I use, help is appreciated
    --------------------
    Sub Copy1()

    ' copy sheet
    Windows("price_Autocad-May-06-02.xls").Activate
    Sheets(" US ASC").Select
    Sheets(" US ASC").Copy Before:=Workbooks("price_S.xls").Sheets(2)
    ' delete column B
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    End Sub
    -----------------

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    G'day

    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    That piece of code seems to work on my machine ok. Is there any more code you left out that might get involved? A Loop perhaps?

    Adam

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy Before:=Sheets(1)
    Columns("B:B").Select
    Selection.Delete Shift:=xlToLeft
    Range("B1").Select

    edit as needed


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

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

    Default

    To Asala42:

    I actually did NOT delete any code, but added some comments.

    If I delete C or D all is well, but the minute I remove B it also removes A

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

    Default

    To Jack in the UK

    Added your suggestion, did not work for me. Truly bizarre

  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hmm...funky

    I was able to create a couple test files to test your code - seems ok here. column A stays intact.

    Do you by any chance have a merged cell in those columns? Anything weird in either Column A or B in your current spreadsheet.

    For the hell of it, see if you can make the code work on a couple test files.

    Sorry I can't be more of a help,
    Adam

  7. #7
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No need to select or shift to left (it inherantly does so). Try:

    Code:
    Sub Copy1()
    ' copy sheet
    Sheets(" US ASC").Copy Before:=Workbooks("price_S.xls").Sheets(2)
    ' delete column B
    [B:B].Delete
    End Sub
    If this code deletes column A, it's time to right click on the sheet in question, click view code and see what's happening there (or in the 'thisworkbook' module).

    _________________
    Cheers, NateO

    [ This Message was edited by: NateO on 2002-05-06 14:08 ]

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    these niothing in my code ok you must change the sheet names but ok did A delete as well?


    If so i would say we have alians and need to look at that book, anyother codes...

    something wrong

    suggest make col b into object say
    [B:B].Delete

    so try :

    sub JACK()
    Sheets("Sheet1").Select
    Sheets("Sheet1").Copy Before:=Sheets(1)
    [B:B].Delete

    end sub

    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

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
  •