Copy Entire Row (Values not formulas) VBA

Question

I need to copy rows between multiple worksheets and while I managed to do that it only copies the formulas and not the values. I haven't used VBA for years and I just can't think of how to do it.

My existing code is:

Workbooks.Open filename:=NewFN
    filename = Mid(NewFN, InStrRev(NewFN, "\") + 1)


        For i = 1 To 14
            Workbooks(filename).Sheets("sheet1").Rows(i).Copy ThisWorkbook.Sheets("BD Raw Data").Rows(insertRow)

            insertRow = insertRow + 1
        Next i

        Workbooks(filename).Close SaveChanges:=False

Two things (1) why use a loop when you could copy the 14 rows in one shot. (2) You don't need to use Copy, you can replicate values by using rng1.Value = rng2.Value where rng2 is a source range and rng1 is a destination 9ranges of equal size). To copy formula rng1.Formula' = rng2.Formula` — brettdj
– brettdj, Commented Jan 25, 2012 at 23:50

Conrad Frix · Accepted Answer · 2012-01-25 16:54:52Z

3

You need to use the PasteSpecial method using the XlPasteType.xlPasteValues enumeration instead of Copy Destination

e.g.

Workbooks(filename).Sheets("sheet1").Rows(i).Copy 
ThisWorkbook.Sheets("BD Raw Data").Rows(insertRow).PasteSpecial Operation:=xlPasteValues

answered Jan 25, 2012 at 16:54

Conrad Frix

52.8k13 gold badges99 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff_Hd Over a year ago

Unfortunately this produces the error code 1004 PasteSpecial method of Range class failed

Mohsen Nosratinia Over a year ago

Should change Operation:= to Paste:= in second line. Worked for me.

Steve Robillard · Accepted Answer · 2012-01-25 17:26:11Z

1

The choice between values and formulas is made in the paste half of the process. Have a look at the PasteSpecial method specifically the xlPasteValues argument.

http://msdn.microsoft.com/en-us/library/aa195818(v=office.11).aspx

edited Jan 25, 2012 at 17:26

answered Jan 25, 2012 at 16:54

Steve Robillard

13.5k3 gold badges38 silver badges33 bronze badges

Comments

Bruno Leite · Accepted Answer · 2012-01-25 16:55:13Z

-2

Try it

 Sub CopyFormulas()
'http://officevb.com

Dim sht1 As Worksheet, sht2 As Worksheet

Set sht1 = Sheets("Sheet1")
Set sht2 = Sheets("sheet2")

'copy columns C from sht1 formulas to sht2

sht1.Range("C:C").Copy
sht2.Range("C:C").PasteSpecial xlPasteFormulas

Application.CutCopyMode = False

End Sub

This sub copy all formulas in column C from sht1 to column C in sht2

[]´s

answered Jan 25, 2012 at 16:55

Bruno Leite

1,47713 silver badges17 bronze badges

1 Comment

Jeff_Hd Over a year ago

Unfortunately the number of columns needed to be copied varies depending on which worksheet is opened meaning a column copying solution wouldn't be the right fit for me.

Collectives™ on Stack Overflow

Copy Entire Row (Values not formulas) VBA

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related