Macro to Copy Values and fill colors.

[deger]

Having issues with this code. I have a spreadsheet template that I need to copy to a new sheet with just the values then copy the fill colors to this new sheet.
If it is passible also name the new sheet the date.

Sub CopyDataToNewSheet()
' Create a new worksheet.
Dim newSheet As Worksheet
Set newSheet = ThisWorkbook.Worksheets.Add
' Set the source range.
Set sourceRange = activeSheet.UsedRange
' Set the destination range.
Dim destinationRange As Range
Set destinationRange = newSheet.Range("A1")
' Copy all data from the active worksheet to the new worksheet.
activeSheet.UsedRange.Copy newSheet.Range("A1")
' Copy the values and fill colors from the source range to the destination range.
destinationRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
' Copy the fill patterns from the source range to the destination range.
For Each cell In destinationRange
    cell.Interior.Color = sourceRange(cell.Row, cell.Column).Interior.Color
    cell.Interior.Fill.Pattern = sourceRange(cell.Row, cell.Column).Interior.Fill.Pattern
Next cell
' Rename the new worksheet.
newSheet.Name = "New Data Sheet"
End Sub

The code you provided appears to have some errors and can be improved. Here's an updated version of the code that should achieve the desired functionality:

Sub CopyDataToNewSheet()
' Create a new worksheet.
Dim newSheet As Worksheet
Set newSheet = ThisWorkbook.Worksheets.Add
' Set the source range.
Dim sourceRange As Range
Set sourceRange = ActiveSheet.UsedRange
' Set the destination range.
Dim destinationRange As Range    
Set destinationRange = newSheet.Range("A1")        
' Copy all data from the active worksheet to the new worksheet. 
sourceRange.Copy destinationRange        
' Copy the values and number formats from the source range to the destination range.
destinationRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats        
' Copy the fill colors from the source range to the destination range.
destinationRange.Interior.Color = sourceRange.Interior.Color        
' Rename the new worksheet with the current date.
newSheet.Name = Format(Date, "yyyy-mm-dd")        
' Clear the clipboard.
Application.CutCopyMode = False
End Sub

Here are the changes made to the code:

Added the declaration for the sourceRange variable.
Replaced instances of activeSheet with ActiveSheet to ensure proper referencing.
Simplified the process of copying values and number formats by using the xlPasteValuesAndNumberFormats option directly.
Updated the loop to copy fill colors to use the entire destinationRange instead of looping through individual cells.
Renamed the new sheet with the current date using the Format(Date, "yyyy-mm-dd") function.
Added a line to clear the clipboard after the copy operation is complete.