CODEWITHSUNDEEP
vbaexcel
SDK Engineering
SDK Engineering

Reputation: 81

I'm trying to copy the active worksheet to the end of the workbook

All is working well, but I need the values copied, not the formulas. Also the formatting of the worksheet. This is what I have:

Dim strName As String, strSht As String
strName = "DMR" & " " & Range("S1").Value
strSht = ActiveSheet.Name

Application.ScreenUpdating = False
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = strName
Sheets(strSht).Range("A1:Y89").Copy
Sheets(strName).Range("A1").PasteSpecial Paste:=xlPasteValues

This doesn't do what I am trying to accomplish. Some cells in the active worksheet being copied, have formulas in them. I need to copy the values of the formulas, along with the formatting of the sheet. Can anyone help? I'm using Excel 2013. TIA...

Upvotes: 2

Views: 32

Answers (1)

user4039065
user4039065

Reputation:

There is a known peculiarity when pasting Unions of discontiguous cells that you end up pasting Values and Formats. Excel cannot resolve the relational cell addresses of a discontiguous range of formulas so it opts for the values and carries the formats along for the ride.

Dim strName As String, strSht As String

strName = "DMR" & " " & Range("S1").Value
strSht = ActiveSheet.Name

Application.ScreenUpdating = False
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = strName
Sheets(strSht).Range("A1:Y89, Y91").Copy Destination:=Sheets(strName).Range("A1")

The above adds (what I beieve to be a blank, unused cell) Y91 to the original copy range to create a discontiguous union of cells. You will end up with the values and formats with no formulas.

Upvotes: 1

Related Questions