Macro to Copy Values and fill colors.

[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.


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

[Aussiebear]

Welcome to VBAX Kairivera.

[jdelano]

I tossed this together


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

[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 thi (https://fmwhat.download/)s 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.