none
I want to collect all sheets in one vba RRS feed

  • Question

  • i have the following code to clear contents from one sheet but I want to expand the area to add another defined names from another sheets

    Sub Clear()
      Dim sh As Worksheet
        Dim yourPassword As String
        yourPassword = "P@$$w0rd"

        For Each sh In ActiveWorkbook.Worksheets
            sh.Unprotect Password:=yourPassword
        Next sh

    Range("hany,hany1,hany2,hany9").ClearContents
             
     
         For Each sh In ActiveWorkbook.Worksheets
            sh.Protect Password:=yourPassword
        Next sh
         MsgBox "All Food Recieving are Cleared Completely"
     
     End Sub

    Hany Samoil

    Wednesday, March 22, 2017 8:51 AM

All replies

  • Hi Hany Samoil,

    if we see your code then we can understand that it is just unprotecting the sheet first, then clear some ranges and then again protect the sheets.

    from the title of the thread , we can see that you want to merge the contents of all the sheets in to one sheet.

    please refer code below.

    Sub Combine()
    Dim J As Integer
    On Error Resume Next
    Sheets(1).Select
    Worksheets.Add
    Sheets(1).Name = "Combined"
    Sheets(2).Activate
    Range("A1").EntireRow.Select
    Selection.Copy Destination:=Sheets(1).Range("A1")
    For J = 2 To Sheets.Count
    Sheets(J).Activate
    Range("A1").Select
    Selection.CurrentRegion.Select
    Selection.Offset(1, 0).Resize(Selection.Rows.Count - 1).Select
    Selection.Copy Destination:=Sheets(1).Range("A65536").End(xlUp)(2)
    Next
    End Sub
    

    Reference:

    How to merge worksheets / workbooks into one worksheet?

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, March 23, 2017 1:00 AM
    Moderator