Try this:

Private Sub CreateSheet()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "Tempo"
End Sub

Or use a With clause to avoid repeatedly calling out your object

Private Sub CreateSheet()
    Dim ws As Worksheet
    With ThisWorkbook
        Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        ws.Name = "Tempo"
    End With
End Sub

Above can be further simplified if you don't need to call out on the same worksheet in the rest of the code.

Sub CreateSheet()
    With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
    End With
End Sub

You can use VLOOKUP function for that. First column of your Spreadsheet 2 will show first column of Spreadsheet 1 , same for the 2nd column

This is your first sheet you can change content of A and B column however you want.

For the Second Sheet add following functions

Column A - =IFERROR(VLOOKUP(Sheet1!$A$1:$A$1000, Sheet1!$A$1:$B$1000, 1, false), "")

Column B - =IFERROR(VLOOKUP(Sheet1!$A$1:$A$1000, Sheet1!$A$1:$B$1000, 2, false), "")

Now all changes from Sheet 1 will be shown in Sheet 2

You are not calling the .Add() method correctly. You are missing the parenthesis at the end of it. To fix it you should be able to simply modify the line to this:

$WS = $WB.worksheets.add()

Also, the cells have properties that you should refer to, so I would also modify the part that sets your cell values to something like this:

# Enter stat labels 
$clientws.cells.item(1,1).value2 = "CPU Count" 
$clientws.cells.item(2,1).value2 =  "RAM"
$clientws.cells.item(3,1).value2 =  "Reserved CPU"
$clientws.cells.item(4,1).value2 =  "Reserved RAM"


### Put in Values in the next column ###
$clientws.cells.item(1,2).value2 =  [int]($cstats.cpuAllocationGHz/2)
$clientws.cells.item(2,2).value2 = [decimal]$cstats.memoryLimitGB
$clientws.cells.item(3,2).value2 = [int]($cstats.rescpuAllocationGHz/2)
$clientws.cells.item(4,2).value2 = [decimal]$cstats.resmemoryLimitGB

I'm fairly sure that defining the type is pointless, since to Excel they're all strings until you set the cell's formatting settings to something else. I could be wrong, but that is the behavior that I have observed.

Now, for other critiques that you didn't ask for... Don't launch Excel, open the book, save the book, and close Excel for each client. Open Excel once at the beginning, open the book, make your updates for each client, and then save, and close.

Test to see if the client has a sheet, and add it if needed, then select the client's sheet afterwords. Right now there's nothing there to set $clientws if you have to add one for that client.

Adding a worksheet by default places it before the active worksheet. This was a poor choice in design in my opinion, but it is what it is. If it were me I'd add new sheets specifying the last worksheet in the workbook, which will add the new worksheet before the last one, making it the second to the last worksheet. Then I'd move the last worksheet up in front of the new one, effectively adding the new worksheet as the last one listed. Is it possible to add the new worksheet as the last one when you make it? Yes, but it's was too complicated for my taste. See here if you are interested in doing that.

When testing for an existing client worksheet to make one if it is missing, do that, don't tell it to test for something, and do nothing, and put everything you want in an Else statement. That just complicates things. All that said, here's some of those suggestions put into practice:

function Excelclientstatstemplate ($clients) {

#### Check if Clients worksheet exists, if no then make one with client name ###
if (($clients -notin $($WB.worksheets).Name)){
    #Find the current last sheet
    $LastSheet = $WB.Worksheets|Select -Last 1
    #Make a new sheet before the current last sheet so it's near the end
    $WS = $WB.worksheets.add($LastSheet)
    #Name it
    $WS.name = "$clients"
    #Move the last sheet up one spot, making the new sheet the new effective last sheet
    $LastSheet.Move($WS)
} 

#Find the current client sheet regardless of if it existed before or not
$clientws = $WB.worksheets | where {$_.name -like "*$clients*"}

# Enter stat labels 
$clientws.cells.item(1,1).value2 = "CPU Count" 
$clientws.cells.item(2,1).value2 =  "RAM"
$clientws.cells.item(3,1).value2 =  "Reserved CPU"
$clientws.cells.item(4,1).value2 =  "Reserved RAM"

### Put in Values in the next column ###
$clientws.cells.item(1,2).value2 =  [int]($cstats.cpuAllocationGHz/2)
$clientws.cells.item(2,2).value2 = [decimal]$cstats.memoryLimitGB
$clientws.cells.item(3,2).value2 = [int]($cstats.rescpuAllocationGHz/2)
$clientws.cells.item(4,2).value2 = [decimal]$cstats.resmemoryLimitGB 

Start-Sleep -Seconds 1

Echo "$clients excel sheet in monthly summary is done.."
}

$Exl = New-Object -ComObject "Excel.Application"
$Exl.Visible = $false
$Exl.DisplayAlerts = $false
$WB = $Exl.Workbooks.Open($excelmonthlysummary) 

$clientxlmonthlywrite = Foreach ($client in $clientlist){
    $cstats = $Combinedstats | Where {$_.Group -eq "$client"}
    Excelclientstatstemplate -clients $client
} 

$WB.save
$Exl.quit()
Stop-Process -processname EXCEL