title: Copy data from two sheets in one workbook and paste to two new sheets in a new workbook
body: <p>I am trying to consolidate 2 files in to 1. I have previously been able to take the data from one sheet in a workbook, create a new workbook and paste this info into this new workbook to be emailed. I have two of these files and I would like to consolidate. The problem that I am running in to is that the file name is always changing to todays' date. I can't seem to figure out how to get the second set of data from the workbook to the new sheet in the new workbook. I get a new sheet but it's blank. Here is what I have so far. This doesn't work but I have tried activating the original workbook and the sheet, I've tried just activating the original sheet. I'm clearly lost on how to copy/paste data ranges from a workbook with 2 sheets to a new workbook with 2 sheets.</p>
  <pre><code>Set sourcewb = ActiveWorkbook
  Application.ScreenUpdating = False
  Range(&quot;E48:AK145&quot;).Select
  Selection.Copy
  Workbooks.Add
  ActiveSheet.Paste
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Columns(&quot;B:B&quot;).Select
  Columns(&quot;A:A&quot;).EntireColumn.AutoFit
  Columns(&quot;B:Y&quot;).Select
  Columns(&quot;B:Y&quot;).EntireColumn.AutoFit
  
  Columns(&quot;A:A&quot;).Select
  Application.CutCopyMode = False
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Rows(&quot;1:1&quot;).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove    
  
  SheetNameText = (&quot;NAME1&quot;)
  Sheets(&quot;Sheet1&quot;).Name = SheetNameText
  Sheets.Add.Name = &quot;NAME2&quot;
  
  Workbooks(“OriginalBook.xlsm”).Activate
  Sheets(“Original 2”).Select
  Range(&quot;A36:AF136&quot;).Select
  Selection.Copy
  ActiveSheet.Paste
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Columns(&quot;B:B&quot;).Select
  Columns(&quot;A:A&quot;).EntireColumn.AutoFit
  Columns(&quot;B:Y&quot;).Select
  Columns(&quot;B:Y&quot;).EntireColumn.AutoFit
  
  Columns(&quot;A:A&quot;).Select
  Application.CutCopyMode = False
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Rows(&quot;1:1&quot;).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
  </code></pre>
  <p>Here is what worked for me. Thank you for the replies. I used Dim'd variables that @Chris suggested and also had to add the <code>Sheets(&quot;NAME2&quot;).Activate</code> that @vbasic2008 suggested. The test code that works is below.</p>
  <pre><code>Sub update()
  
  Dim rng1 As Range
  Set rng1 = ThisWorkbook.Worksheets(&quot;Original 1&quot;).Range(&quot;A1:K28&quot;)
  Dim rng2 As Range
  Set rng2 = ThisWorkbook.Worksheets(&quot;Original 2&quot;).Range(&quot;A1:K29&quot;)
  
  Application.ScreenUpdating = False
  rng1.Copy
  Workbooks.Add
  ActiveSheet.Paste
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Columns(&quot;B:B&quot;).Select
  Columns(&quot;A:A&quot;).EntireColumn.AutoFit
  Columns(&quot;B:Y&quot;).Select
  Columns(&quot;B:Y&quot;).EntireColumn.AutoFit
  
  Columns(&quot;A:A&quot;).Select
  Application.CutCopyMode = False
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Rows(&quot;1:1&quot;).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
  
  SheetNameText = (&quot;NAME1&quot;)
  Sheets(&quot;Sheet1&quot;).Name = SheetNameText
  Sheets.Add.Name = &quot;NAME2&quot;
  
  rng2.Copy
  Sheets(&quot;NAME2&quot;).Activate
  ActiveSheet.Paste
  Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  Columns(&quot;B:B&quot;).Select
  Columns(&quot;A:A&quot;).EntireColumn.AutoFit
  Columns(&quot;B:Y&quot;).Select
  Columns(&quot;B:Y&quot;).EntireColumn.AutoFit
  
  Columns(&quot;A:A&quot;).Select
  Application.CutCopyMode = False
  Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
  Rows(&quot;1:1&quot;).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
      Application.ScreenUpdating = True
      
      ActiveWorkbook.SaveAs (&quot;C:\Downloads&quot; &amp; Format(Now(), &quot;_MMMDD_YYYY&quot;) &amp; &quot;.xlsx&quot;)
  
      Set DestinWB = Workbooks.Open(&quot;C:\Downloads&quot; &amp; Format(Now(), &quot;_MMMDD_YYYY&quot;) &amp; &quot;.xlsx&quot;)
              
  End Sub
  </code></pre>