Need help copying and pasting values

[amert19]

Hello. I need help with my code. i'm trying to create a macro were a user clicks a button and have contents of sheet("Pick") be copied and paste values only into a new worksheet that gets created with previous date as name. Below is my code. Can't get past the error message.

Sub CommandButton1_Click()
Dim OrgWs As Worksheet
Dim szToday As String
Set OrgWs = ThisWorkbook.Worksheets("Pick")



If (WeekdayName(Weekday(Date) - 1) = "Tuesday") Then
szToday = Format(Date - 2, "mmm-dd-yy")
ElseIf (WeekdayName(Weekday(Date) - 1) = "Saturday") Then
szToday = Format(Date - 2, "mmm-dd-yy")
Else
szToday = Format(Date - 1, "mmm-dd-yy")
End If

OrgWs.Copy After:=ThisWorkbook.Sheets(Sheets.Count)


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

ActiveSheet.Name = szToday




End Sub

 

[Jeffrey Mahoney]

I think this may be what you were trying to do.

Sub TestWSCopy()

  Dim OrgWs As Worksheet
  Dim szToday As String
  Dim NewWS As Worksheet
  
  Set OrgWs = ThisWorkbook.Worksheets("Pick")


  If Weekday(Date, 2) = 2 Then    'Tuesday
    szToday = Format(Date - 2, "mmm-dd-yy")
  ElseIf Weekday(Date, 2) = 6 Then    'Saturday
    szToday = Format(Date - 2, "mmm-dd-yy")
  Else
    szToday = Format(Date - 1, "mmm-dd-yy")
  End If
  
  OrgWs.Copy After:=ThisWorkbook.Sheets(Sheets.Count)
  
  
  ActiveSheet.Name = szToday
  Set NewWS = Sheets(szToday)
  Range(NewWS.Range("A1"), NewWS.Cells.SpecialCells(xlCellTypeLastCell)).Copy
  
  NewWS.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
  :=False, Transpose:=False




End Sub

Jeff

 

[amert19]

It's still giving me an error. It stops and points to the following line:

Range(NewWS.Range("A1"), NewWS.Cells.SpecialCells(xlCellTypeLastCell)).Copy


Almost as if I'm missing a line of code

 

[Jeffrey Mahoney]

I tried that code several times on my version of Excel (2010). It didn't fail. Gotta go to work.