Macro to Copy Values and fill colors.

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.

Code:

---

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

---

Welcome to VBAX Kairivera.

I tossed this together

Code:

---

Private Sub btnCreateNewSheet_Click()
   
    Dim templateRange As Range
    Dim newSheet As Worksheet
    Dim newSheetName As String
   
    ' define what range to copy from
    Set templateRange = Worksheets("Template").Range("A1:D10")
   
    ' create a new sheet and name it today's date
    newSheetName = Format(Date, "MM-DD-YYYY")
    Set newSheet = ThisWorkbook.Sheets.Add
    newSheet.Name = newSheetName
   
    ' copy the template range to the new sheet
    templateRange.Copy
    newSheet.Range("A1:D10").PasteSpecial xlPasteValues
    newSheet.Range("A1:D10").PasteSpecial xlPasteFormats
   
End Sub

---

Quote:

---

Originally Posted by Kairivera

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.

Code:

---

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:

Code:

---

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.