Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: copy paste range, values or formats?

  1. #1
    New Member
    Join Date
    Feb 2017
    Posts
    36
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default copy paste range, values or formats?

    So I've got this string of code that finds a sheet by name (source), then copies its data onto a combined sheet (destination) after the last row.

    I pulled this from google, and not quite sure yet how the syntax works w/ the copyrng and vlaues vs formats.

    How do I adjust this to preserve the formats of the source sheets?

    Code:
      
    Dim CopyRng As Range
    Dim Destws As Worksheet
    Dim ws As Worksheet
    Dim Last As Long
    
    Set Destws = ActiveWorkbook.Sheets("Sheet1")
    
    For Each ws In ActiveWorkbook.Worksheets
         If LCase(ws.Name) Like LCase("source") Then
              Last = Lastrow(Destws) 'uses LastRow() sub listed above
    
              Set CopyRng = ws.Range("B1").CurrentRegion 'uses the current block of data
              
              CopyRng.Copy    'This statement copies values and formats.            
              With CopyRng    'paste values only, one row down from last row
                   Destws.Cells(Last + 1, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value
                   Application.CutCopyMode = False
              End With
         End If
    Next

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    14,061
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    6 Thread(s)

    Default Re: copy paste range, values or formats?

    Assuming all the other lines of code works the way you want it to.

    Change this line of code:
    Code:
    Destws.Cells(Last + 1, "A").Resize(.Rows.Count, .Columns.Count).Value = .Value


    To this:
    Code:
    Destws.Cells(Last + 1, "A").Resize(.Rows.Count, .Columns.Count).PasteSpecial
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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