Hide cells with Macro not working properly.

  • I have this code which is working fine coping from one workbook to another, but on the sheet where the data is posted I want to hide or delete cells D9:D67 & F9:F67.


    I recorded some code to alter my original code but it also hides the data on the sheet where the data is copied from, any ideas how to fix this?


    This is what I had


    Code
    Sub Copy()
        Dim RngCopy As Range
        Dim RngPaste As Range
        Set RngCopy = Workbooks("Parts.xls").Sheets("Spare Parts").Range("A1:H67")
        Set RngPaste = Workbooks("Parts Email.xls").Sheets("Spare Parts").Range("A1:H67")
      End Sub


    This is what I changed it to


  • Re: Hide cells with Macro not working properly.


    [COLOR="Purple"]Eternity is a terrible thought. I mean, where's it going to end??[/COLOR]

  • Re: Hide cells with Macro not working properly.


    as norie said....

    [COLOR="Purple"]Eternity is a terrible thought. I mean, where's it going to end??[/COLOR]

  • Re: Hide cells with Macro not working properly.


    I tried this but it still effects the original workbook sheet even though I have specified the for this to happen on the workbook where the data is pasted.


    [Code]Sub Copy()
    Dim RngCopy As Range
    Dim RngPaste As Range

    Set RngCopy = Workbooks("Parts.xls").Sheets("Spare Parts").Range("A1:I67")
    Set RngPaste = Workbooks("Parts Email.xls").Sheets("Spare Parts").Range("A1:I67")
    RngCopy.Copy RngPaste
    With Workbooks("Spare Parts Email.xls").Sheets("Handicapps")
    Range("D9:D67").Select
    Selection.Font.ColorIndex = 2
    Range("I9:I67").Select
    Selection.Font.ColorIndex = 2
    End With
    End Sub[Code]

  • Re: Hide cells with Macro not working properly.


    Barryj,


    Firstly, at the end of the code you need to use / in front of the word 'code' to denote the end of the code tags.


    If you use With Workbooks(... you need to precede the ranges referred to with . so that your code becomes


    Note that I have removed the code to select these ranges before working on them. Not only is this superfluous, it will take longer to process and in this case might not work correctly unless the workbook and worksheet you refer to are themselves already active.


    Hope this helps.


    Regards,
    Batman.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!