Add a sheet after the last one and copy the last one values not formula

[paulaks]

I'm trying to copy only values (not formulas) of the last sheet and name the new sheet "Games". I'm using this:

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">Sub CreateColumn()ActiveSheet.CopyCells.CopyRange("A1").PasteSpecial Paste:=xlPasteValuesApplication.CutCopyMode = False ActiveSheet.Name = "Games" Application.ScreenUpdating = FalseEnd Sub</code></pre>

But it's creating a new Workbook (file), not a new sheet after the last one. Plus, the last sheet is not the one active all the time... Could someone help me? Thanks

 

[oldbrewer]

Sheets.Add
Sheets("Sheet1").Select
Range("A2:C4").Select
Selection.Copy
Sheets("Sheet4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C4").Select
End Sub

I just recorded this macro. I started in sheet 1, (I had put a few formulas in ), inserted a new sheet, went back to sheet 1, highlighted the relevant cells, edit copy, went to sheet 4, edit paste special values.

 

[oldbrewer]

Sheets("Sheet4").Select
Sheets("Sheet4").Name = "games"
End Sub

likewise this renames sheet 4 as games

 

[paulaks]

The thing is.. the last sheet of my workbook has a different name depending on the file (it's more than 100 files).. I have to copy the last sheet everytime. It doesn't have the name "Sheet1"

 

[oldbrewer]

what does - it's more than 100 files mean? 100 plus sheets in workbook?

 

[paulaks]

No, it's 100 plus workbooks. That means in each workbook I have a last sheet with a name I don't even know.

 

[oldbrewer]

Say you only have 2 workbooks. you would open the first one, go to last sheet, edit copy, create new sheet, name the sheet games, paste special values, close the workbook, open the second workbook and repeat. Is this what you want. Can the workbooks have similar names eg name1 name2?

 

[paulaks]

I'll open manually the workbook and it has a lot of sheets (not a fixed number for every workbook), i need it to copy the last one independent on the sheet it's active

 

[kevatarvind]

what you want to do with last sheet ?
below code will change last whatever name to Last Sheet Name

Sub Test()
my = ThisWorkbook.Sheets.Count
Sheets(my).name = "Last Sheet"
End Sub

if you need something else pls let me know i m not getting what you need actually

 

[oldbrewer]

I need to check how to go to last sheet - later