title: Excel Vba Copy All excel datas to Worksheet with Auto Filter With Button
body: <p>I am trying to write a vba code that when I click to a button:</p>
  
  <ol>
  <li>Creates a new sheet</li>
  <li>Checks all excel files in a folder(fixed address)</li>
  <li>Applies an autofilter to column(K13:K) for each cell value equal
  to 10 number after copy them ( OR copy them all after apply filter) </li>
  <li>Take them all and copy to a new sheet </li>
  <li>Create a new filter in A1 we have variety product which we want selective list</li>
  <li>Data which come to new sheet apply to autosize
  row&amp;column,VerticalAlignment,HorizontalAlignment and wrap text</li>
  </ol>
  
  <p>Note : all data must come from a folder which we have all excel files.</p>
  
  <p>Also have a error that </p>
  
  <blockquote>
    <p>We can't do that to a merged cell.</p>
  </blockquote>
  
  <pre><code>Sub CopyRangeToAnotherSheet()
  
      '*-*-*-*
  
      '*-*-
      Workbooks.Open ("C:\Users\username\Desktop\foldername\Update\filename.xlsm")
  
      Sheets("PFMEA").Range("A12:AD3377").Copy
      'Activate the destination worksheet
          Sheets("PFMEA").Activate
      'Select the target range
      Range("A2").Select
      'Paste in the target destination
      ActiveSheet.Paste
  
      Application.CutCopyMode = False
      AutoFilterMode = False
  
                  Range("K1:K3377").AutoFilter
      ThisWorkbook.Sheets("critical").Range("K1:K3377").AutoFilter Field:=1, Criteria1:=10
  
      Worksheets("critical").Range("d10").WrapText = True
  
      '*-*-*-*Resize_Columns_And_Rows_No_Header
  
          Dim currentSheet As Worksheet
  
          Set currentSheet = ActiveSheet
  
          Dim Sheet As Worksheet
          For Each Sheet In ActiveWorkbook.Worksheets
              With Sheet
                  With Worksheets("Critical").Range("A:AD").Cells.Rows
                      Worksheets("Critical").Range("A:AD").WrapText = True
                      Worksheets("Critical").Range("A:AD").VerticalAlignment = xlCenter
                      Worksheets("Critical").Range("A:AD").EntireRow.AutoFit
                      Worksheets("Critical").Range("A:AD").HorizontalAlignment = xlCenter
                  End With '.Cells.Rows
                  Worksheets("Critical").Range("A:AD").Columns.EntireColumn.AutoFit
                  Worksheets("Critical").Range("A:AD").EntireRow.AutoFit
              End With 'sheet
          Next Sheet
  
  
      '          *-*- Auto filter for product
  
                ' Range("A1:AD1").AutoFilter
                  ThisWorkbook.Sheets("critical").Range("A1:AD1").AutoFilter Field:=1
  
          currentSheet.Activate
  
      '*******************************
      'Create a Button For Return Back
  
      ActiveSheet.Buttons.Add(30, 1, 60, 20).Select
      Selection.Name = "Return Back"
  
      Selection.OnAction = "CheckTotals"
      ActiveSheet.Shapes("New Button").Select
  
      Selection.Characters.Text = "Return Back"
      '*************************************
  
      '**************************************
      Sub returnback()
      ThisWorkbook.Sheets("RISK_PRIORITY").Activate
      End Sub
  
  
  
  `
  </code></pre>