title: I need a VBA code to count the number rows, which varies from ss to ss, return that number and copy and paste that row and all other columns
body: <p>I have vba question I have been trying to find the answer for for a long time. I have numerous spreadsheets from numerous clients that I run macro's on, I'm new to coding and have been able to mostly figure out what I need to do. My clients send us data monthly and every month the number of rows change. The columns don't change but the amount of data does. My previous macro's I have just chosen the entire column to copy and paste onto our companies template. This worked fine for must things but has created some really long code and macros take a long time. I would like to write a code that counts how many rows are in a certain column and then from there copies and pastes that however many rows it counted in each column. Only a few columns contain data in every row, so I need it to count the rows in one specific column and apply to that every column. Any help would be appreciated.
  Thanks
  Tony </p>
  
  <p>Hi Guys,
  Still having issues with this, below I pasted the code I'm using if anyone can see why it won't run please help.</p>
  
  <pre><code>Windows("mmuworking2.xlsx").Activate
   Workbooks.Open Filename:= _
          "C:\Users\I53014\Desktop\QC DOCS\Sample_Data_Import_Template.xlsx"
   Windows("mmuworking2.xlsx").Activate
      Dim COL As Integer
      COL = Range("A:DB").Columns.Select
      **Range(Cells(2, COL), Cells(Range("E" &amp; Rows.Count).End(xlUp).Row, COL)).Copy Destination:=Windows("Sample_Data_Import_Template.xlsx").Range("A2")**
      Range("A2").Paste
      Range("A5000").Formula = "='C:\Users\I53014\Desktop\[Import_Creator.xlsm]sheet1'!$B$2"
      ActiveWorkbook.SaveAs Filename:="Range (A5000)", _
          FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
  </code></pre>
  
  <p>I bolded where it keeps stopping.</p>