VBA Question - Deselect after Paste - Excel 2010

[Johnny Thunder]

Hi guys,

Stuck on a problem with my code, I am creating a new sheet and then doing a copy paste function to get the data onto my newly created workbook/Sheet but for some reasons I can't deselect the pasted cells. Maybe someone can see a problem or a solution for my issue.

My code:

With nW.Sheets("By-Account").Range("A1")
  .PasteSpecial xlPasteValues
      .PasteSpecial xlPasteFormats
         Application.CutCopyMode = False ' Clears Clipboard
         Range("A1").Select   'Where I am trying to deselct the pasted sheet
            ActiveSheet.Outline.ShowLevels ColumnLevels:=1
                ActiveSheet.Outline.ShowLevels RowLevels:=1
                    Columns("A").Hidden = True ' Hide Columns and rows with lookup criteria

 

[Rick Rothstein]

Hi guys,

Stuck on a problem with my code, I am creating a new sheet and then doing a copy paste function to get the data onto my newly created workbook/Sheet but for some reasons I can't deselect the pasted cells. Maybe someone can see a problem or a solution for my issue.

My code:

With nW.Sheets("By-Account").Range("A1")
  .PasteSpecial xlPasteValues
      .PasteSpecial xlPasteFormats
         Application.CutCopyMode = False ' Clears Clipboard
         Range("A1").Select   'Where I am trying to deselct the pasted sheet
            ActiveSheet.Outline.ShowLevels ColumnLevels:=1
                ActiveSheet.Outline.ShowLevels RowLevels:=1
                    Columns("A").Hidden = True ' Hide Columns and rows with lookup criteria

Does changing the highlighted line of code to this work for you...

.Select

 

[Johnny Thunder]

Unfortunetely, I tried that already and it didn't do the trick.

I am posting the full code here to see if there is something I am doing to cause the issue.

The weird thing that I am seeing is that this is occuring on the second round of pasting. I do the same thing to another sheet and it works fine within the same workbook, but when I go to the second sheet of the newly created workbook/sheet it will not allow me to deselect the newly pasted cells.

Sub SaveSheet()


Dim Sh As Worksheet
Dim nW As Workbook
Dim dt As String
Dim CCenter As String


'Const csPath As String = "\\Wb\acctg\shared\RPTOHP\FP&A\Budgeting & Forecasting\Monthly Management Reports\Testing"
Const csPath As String = "\\Wb\acctg\shared\RPTBUD\Financial Systems Group\Training\Jonathan Projects\Theresa\Dump\"


Application.DisplayAlerts = False
Application.ScreenUpdating = False


Sheets("WBEI Consolidated").Cells.Copy


'Creates new copy of the sheet to a new workbook
Set nW = Workbooks.Add


With nW
    .Sheets("Sheet1").Name = "WBEI Consolidated"
        .Sheets("Sheet2").Name = "By-Account"
End With


With nW.Sheets("WBEI Consolidated").Range("A1")
    .PasteSpecial xlPasteValues
         .PasteSpecial xlPasteFormats
            ActiveSheet.Outline.ShowLevels RowLevels:=1
            Columns("A").Hidden = True ' Hide Columns and rows with lookup criteria
                Rows("10:10").EntireRow.Hidden = True
                  .Range("a1").Select
                      Application.CutCopyMode = False ' Clears Clipboard
                End With
              
Call PrintArea
              
With nW
    .Sheets("sheet3").Delete
End With


ThisWorkbook.Sheets("By-Account").Cells.Copy
    
With nW.Sheets("By-Account").Range("A1")
  .PasteSpecial xlPasteValues
      .PasteSpecial xlPasteFormats
         Application.CutCopyMode = False ' Clears Clipboard
         Range("A1").Select   ' Where the issue happens
            ActiveSheet.Outline.ShowLevels ColumnLevels:=1
                ActiveSheet.Outline.ShowLevels RowLevels:=1
                    Columns("A").Hidden = True ' Hide Columns and rows with lookup criteria
              
        End With


Call PrintAreaByACT


wbNam = "OH Reporting - " & Range("A8").Value
dt = Format(CStr(Now), " - yyyymmdd")


 ActiveWorkbook.SaveAs Filename:= _
     csPath & wbNam & dt & ".xlsx"
     
     ActiveWorkbook.Close True




End Sub

 

[Johnny Thunder]

Anyone? Still can't get this figured out.