Using Copy Destination for values and number formatting

[Viktor]

Hey guys,

I have the below code which copies the row B from each worksheet into my Master Data sheet. How can I adjust it so that it pastes only the values and number formatting?

[VBA]
Sub CopyRows2ToDataSheet()

Dim ws As Worksheet, _
LR As Long
Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Master Data sheet" Then

LR = Sheets("Master Data sheet").Range("A" & Rows.Count).End(xlUp).Row + 1
ws.Range("2:2").Copy Destination:=Sheets("Master Data sheet").Range("A" & LR1)
End If

Next ws
Application.ScreenUpdating = True
End Sub
[/VBA]


Thanks a lot, Viktor

[astranberg]

Record the action in excel pasting as values and formatting as numbers.

[astranberg]

            LR = Sheets("Master Data sheet").Range("A" & Rows.Count).End(xlUp).Row + 1 
            ws.Range("2:2").Copy Destination:=Sheets("Master Data sheet").Range("A" & LR1), Paste:=xlPasteValues
            Sheets("Master Data sheet").Range("A" & LR1).NumberFormat:= "0.00"

[Aflatoon]

You can't do that using destination
[vba]ws.Range("2:2").Copy
Sheets("Master Data sheet").Range("A" & LR1).PasteSpecial xlPasteValuesAndNumberFormats
[/vba]

[Viktor]

You can't do that using destination
[vba]ws.Range("2:2").Copy
Sheets("Master Data sheet").Range("A" & LR1).PasteSpecial xlPasteValuesAndNumberFormats
[/vba]

This one worked great!
The issue with the other approach was that different columns of the rows I was copying had different formatting, so I couldn't just apply single format style for all of them.

Thank you both,
Viktor