PDA

View Full Version : [SOLVED] Using Copy Destination for values and number formatting



Viktor
11-04-2013, 04:57 AM
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?


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



Thanks a lot, Viktor

astranberg
11-04-2013, 09:21 AM
Record the action in excel pasting as values and formatting as numbers.

astranberg
11-04-2013, 09:23 AM
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
11-05-2013, 02:10 AM
You can't do that using destination
ws.Range("2:2").Copy
Sheets("Master Data sheet").Range("A" & LR1).PasteSpecial xlPasteValuesAndNumberFormats

Viktor
11-05-2013, 04:09 AM
You can't do that using destination
ws.Range("2:2").Copy
Sheets("Master Data sheet").Range("A" & LR1).PasteSpecial xlPasteValuesAndNumberFormats


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