Paste but keep Destination formatting

[zgivod]

hi heres my code but its keeping the sheet 1 text format when i want it to be like sheet 2 text and cell format


Private Sub CommandButton1_Click()
Sheets("sheet1").Range("A1:A4").SpecialCells(xlConstants).copy _
Sheets("sheet2").Range("A1:A4").End(xlUp)
End Sub

 

[MARK858]

Not knowing why you are using SpecialCells(xlConstants) as you haven't stated it then try (it is also unusual to use End(xlUp) with such a small range)...

Private Sub CommandButton1_Click()
    With Sheets("sheet1").Range("A1:A4")
        Sheets("sheet2").Range("A1:A4").End(xlUp).Value = .SpecialCells(xlConstants).Value
    End With
End Sub

or

Private Sub CommandButton1_Click()
    With Sheets("sheet1").Range("A1:A4")
        Sheets("sheet2").Range("A1:A4").End(xlUp).Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With
End Sub

You might need to provide more details.

 

[davesexcel]

If you just pastespeciel the values, if should not impact the formatting.

Or you can just make one range value equal another range value

Sheets("Sheet2").Range("A1:A4").Value = Sheets("Sheet1").Range("A1:A4").Value

 

[zgivod]

the small range was just to test the code. im trying to get all the text on a range with no blanks sent to sheet2 where it will keep the sheet 2 format

 

[MARK858]

If your values are constants and not from formulas why can't you just do ....

    Sheets("sheet1").Range("A1:A4").SpecialCells(xlCellTypeConstants, 23).Copy
    Sheets("sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

 

[zgivod]

If your values are constants and not from formulas why can't you just do ....

    Sheets("sheet1").Range("A1:A4").SpecialCells(xlCellTypeConstants, 23).Copy
    Sheets("sheet2").Range("A1").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False

because only A1 is getting changed to the sheet2 format all other ones are keeping sheet1 format

 

[MARK858]

because only A1 is getting changed to the sheet2 format all other ones are keeping sheet1 format

Pardon, how does pasting as xlValues change any of the the Sheet2 formats?... not possible that I have ever seen!

Have you actually ran the code as posted?
If you have and that is the result you get please upload your workbook to www.box.com, mark it for sharing and post the link it provides in the thread please so we can look at it.


...and can you confirm you want to retain the Sheet2 format.

 

[zgivod]

@MARK858 thats what i was wondering but now it works so thanks so much

 

[MARK858]

Happy you have got it working.