Consulting

Results 1 to 4 of 4

Thread: Macro to Copy Values and fill colors.

  1. #1

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

    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
    Last edited by Aussiebear; 10-26-2023 at 12:52 PM. Reason: Added code tags to supplied code

  2. #2
    Moderator VBAX Wizard Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    5,180
    Location
    Welcome to VBAX Kairivera.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  3. #3
    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
    Attached Images Attached Images

  4. #4
    VBAX Newbie
    Joined
    Sep 2023
    Posts
    2
    Location
    Quote Originally Posted by Kairivera View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •