VBA Copy Value not Formula again

RasGhul
Jul 15th 2016
Thread is marked as Resolved.

Jul 15th 2016

#1

Hi All,

If someone can help thanks, I just got this code working although the cells that are copied from are created from formulas. I also need a line that copies the range F2:F32 and pastes over B2:B32 in Sheet4.

Could this be written to do exactly the same but for values only thanks in advance!!;

Code

Sub copyrows()
 Dim lastrow As Long, erow As Long




 lastrow = Sheet4.Cells(Rows.Count, 1).End(xlUp).Row
 For i = 1 To lastrow


 If Sheet4.Cells(i, 11) = "SOLD" Then
 Sheet4.Cells(i, 10).Copy
 erow = Sheet8.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
 Sheet4.Paste Destination:=Worksheets("TRANSACTIONS").Cells(erow, 1)




 Sheet4.Cells(i, 1).Copy
 Sheet4.Paste Destination:=Worksheets("TRANSACTIONS").Cells(erow, 2)


 Sheet4.Cells(i, 5).Copy
 Sheet4.Paste Destination:=Worksheets("TRANSACTIONS").Cells(erow, 3)


 Sheet4.Cells(i, 8).Copy
 Sheet9.Paste Destination:=Worksheets("TRANSACTIONS").Cells(erow, 4)


 Sheet4.Cells(i, 9).Copy
 Sheet4.Paste Destination:=Worksheets("TRANSACTIONS").Cells(erow, 5)
 End If
 Next i


 Application.CutCopyMode = False
 Sheet8.Columns.AutoFit
 Range("A1").Select




 End Sub

Display More

alansidman

Mountain Moderator

Reactions Received

75

Points

11,750

Posts

2,273
Jul 15th 2016
- #2
Re: VBA Copy Value not Formula again

Instead of Paste, use PasteSpecial. Here is the syntax for the VBA

http://analysistabs.com/vba-code/range/m/pastespecial/
Alan

Did you debug your code first? http://www.cpearson.com/excel/DebuggingVBA.aspx
FORUM RULES----->http://www.ozgrid.com/forum/announcement.php?f=8
RasGhul

Beginner

Points

20

Posts

2
Jul 15th 2016
- #3
Re: VBA Copy Value not Formula again

Thanks Alan very helpful link.

Jul 15th 2016

#4

Re: VBA Copy Value not Formula again

You don't actually need to Copy. Try something like this

Code

Option Explicit


Sub copyrows()
    Dim lastrow As Long, erow As Long


    With Sheet4
        lastrow = .Cells(.Rows.Count, 1).End(xlUp).Row
        For i = 1 To lastrow


            If .Cells(i, 11) = "SOLD" Then
                erow = Sheet8.Cells(Rows.Count, 1).End(xlUp).Row + 1
                Worksheets("TRANSACTIONS").Cells(erow, 1).Value = .Cells(i, 10).Value
                Worksheets("TRANSACTIONS").Cells(erow, 2).Value = .Cells(i, 1).Value
                Worksheets("TRANSACTIONS").Cells(erow, 3).Value = .Cells(i, 8).Value
                Worksheets("TRANSACTIONS").Cells(erow, 5).Value = .Cells(i, 9).Value
            End If
        Next i


    End With


    Sheet8.Columns.AutoFit
    Range("A1").Select


End Sub

Display More

gjenniskens

Beginner

Points

5

Posts

1
Jul 18th 2016
- #5
Re: VBA Copy Value not Formula again

Any tips on how to remove all the formulas of a Workbook without using Special Paste? I haven't a clue as to how to use the above code in excel.... Help!
royUK

Super Moderator

Reactions Received

809

Points

133,284

Posts

26,393

Operating System

Windows (x64)

Excel Version

Office 365
Jul 18th 2016
- #6
Re: VBA Copy Value not Formula again

gjenniskens, please do not hijack other member's posts. This is covered in the Forum Rules that you agreed to when joining. Start your own thread or even search the Forum, there is an excel way posted several years ago by Aaron Blood. I'll help when you ask in your own Thread.
Hope that Helps

Roy

New users should read the Forum Rules before posting

For free Excel tools & articles visit my web site

Check out my new web site.

royUK's Database Form

Where to paste code from the Forum

About me.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login

Share

This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close

Your browser has JavaScript disabled. If you would like to use all features of this site, it is mandatory to enable JavaScript.