0

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.

Set sourcewb = ActiveWorkbook
Application.ScreenUpdating = False
Range("E48:AK145").Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("B:B").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:Y").Select
Columns("B:Y").EntireColumn.AutoFit

Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove    

SheetNameText = ("NAME1")
Sheets("Sheet1").Name = SheetNameText
Sheets.Add.Name = "NAME2"

Workbooks(“OriginalBook.xlsm”).Activate
Sheets(“Original 2”).Select
Range("A36:AF136").Select
Selection.Copy
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("B:B").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:Y").Select
Columns("B:Y").EntireColumn.AutoFit

Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

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 Sheets("NAME2").Activate that @vbasic2008 suggested. The test code that works is below.

Sub update()

Dim rng1 As Range
Set rng1 = ThisWorkbook.Worksheets("Original 1").Range("A1:K28")
Dim rng2 As Range
Set rng2 = ThisWorkbook.Worksheets("Original 2").Range("A1:K29")

Application.ScreenUpdating = False
rng1.Copy
Workbooks.Add
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("B:B").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:Y").Select
Columns("B:Y").EntireColumn.AutoFit

Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

SheetNameText = ("NAME1")
Sheets("Sheet1").Name = SheetNameText
Sheets.Add.Name = "NAME2"

rng2.Copy
Sheets("NAME2").Activate
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Columns("B:B").Select
Columns("A:A").EntireColumn.AutoFit
Columns("B:Y").Select
Columns("B:Y").EntireColumn.AutoFit

Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Rows("1:1").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Application.ScreenUpdating = True
    
    ActiveWorkbook.SaveAs ("C:\Downloads" & Format(Now(), "_MMMDD_YYYY") & ".xlsx")

    Set DestinWB = Workbooks.Open("C:\Downloads" & Format(Now(), "_MMMDD_YYYY") & ".xlsx")
            
End Sub
Jacob Pursley
  • 1
  • 2
  • Where is the code located, in `SourceWb`? What is the name of the worksheet containing the range `E48:AK145`? Are you using `Selection.PasteSpecial...` to convert formulas to values? Are you inserting a first empty row and a first empty column? Is there a known location for the `OriginalBook.xlsm` file e.g. `C:\Test\`? Where is (should be) the *today's date* part? Please share this and any other relevant information by [editing your post](https://stackoverflow.com/posts/71072649/edit). – VBasic2008 Feb 10 '22 at 22:13
  • 1
    You should start by [not relying on Select/Selection/Active*](https://stackoverflow.com/q/10714251) – chris neilsen Feb 10 '22 at 22:16
  • The immediate mistake is the missing `Sheets("NAME2").Activate` right above the second `ActiveSheet.Paste` which is actually also wrong because you need the workbook where it is in, the one you added and now have lost the 'connection' to. In a nutshell, you would be better off using object variables (for workbooks, worksheets and ranges) . And those double quotes in both of the occurrences of `“Original...` are wrong. – VBasic2008 Feb 10 '22 at 22:29

0 Answers0