none
Using VBA with Hidden Excel worksheets RRS feed

  • Question

  • How do I do .find on a hidden sheet, copy the entire row/ column, and paste it to another sheet?

    I got it to work using

    Application.ScreenUpdating = False

    Sheets("hidden").visible = True

       Sheets("hidden").Range("RangeName").Find(What:="SelectionName").Activate

       Activecell.EntireColumn.Copy Destination:=Sheets("OtherSheet").Range("M:M")

    Then set the sheet back to hidden when the copy is done. Just wondering is there an easier way to do this without hide and unhide?


    Roget Luo

    Tuesday, March 14, 2017 8:11 PM

All replies

  •           

    Hi Roget Luo,

    Why do you unhide and hide Sheets("hidden")?
    I suppose you would think that copy&paste does not work when Sheets("hidden") is not visible.

    You do not have to make the Sheets("hidden") visible before copying.
    i.e. Copy will work as you expect when the Sheets("hidden") is hidden. 

    I have made a sample and confirmed ".Copy Destinate:=" could work when source sheet is not visible. 
      image of a target sheet (named "OtherSheet"):

      image after [Copy]:


    source sheet (named "OtherSheet")
      

    Here's code in Buttuns:

    Private Sub btn_Copy_Click()
        Dim rng As Range
        Set rng = Sheets("hidden").Range("RangeName").Find(What:="copy Target", LookIn:=xlValues)
        ' ---
        If Not (rng Is Nothing) Then
            rng.Copy Destination:=Sheets("OtherSheet").Range("M:M")
        End If
    End Sub
    
    Private Sub btn_DeleteColumn_Click()
        Sheets("OtherSheet").Columns("M").Delete
        Sheets("OtherSheet").Range("M1").Select
    End Sub
    
    Private Sub btn_Hide_Click()
        Sheets("hidden").Visible = xlSheetHidden
    End Sub
    
    Private Sub btn_Visible_Click()
        Sheets("hidden").Visible = xlSheetVisible
    End Sub

    [my environment]
      Windows 10 Pro (64-bit),  version 1607, build 14393
      Excel Pro Plus 2016 (32-bit),  version 1701, build 7766.2060

    Regards,
    Ashidacchi




    • Edited by Ashidacchi Wednesday, March 15, 2017 1:48 PM
    Wednesday, March 15, 2017 12:08 AM
  •           

    I suppose that copy&paste does not work when Sheets("hidden") is not visible.

    Ashidacchi


    I was trying a code using .find parameter on hidden and very hidden works fine.
    you do not need to unhide nothing.


    Oskar Shon, Office System MVP - www.VBATools.pl
    if Helpful; Answer when a problem solved

    Wednesday, March 15, 2017 1:38 PM
    Answerer