Senin, 17 November 2014

RE: [ExcelVBA] Help with Syntax for copying visible data as value only

 

Hi Graham

 

I like your code, by the way. Very elegant.

 

I would just record a macro to see the syntax of Paste Special as values only. Since you have not cleared the clipboard you can leave your code as is, and just paste special as values only in a next line. That should do the trick.

 

Regards

 

 

 

 

Paul

 

From: ExcelVBA@yahoogroups.com [mailto:ExcelVBA@yahoogroups.com]
Sent: Tuesday, 18 November 2014 5:42 AM
To: ExcelVBA@yahoogroups.com
Subject: [ExcelVBA] Help with Syntax for copying visible data as value only

 

 

Hello Everyone,

The following code works OK for me but I would prefer the data to be copied over as value only.  Could anyone start me off with an idea of how to do this please? Thanks in anticipation, Graham

Dim src As Worksheet

Dim tgt As Worksheet

Dim filterRange As Range

Dim copyRange As Range

Dim lastRow As Long

 

Set src = Sheets("JOBS")

Set tgt = Sheets("NEW INVOICE")

With src

AutoFilterMode = False

End With

lastRow = src.Range("A" & src.Rows.Count).End(xlUp).Row

Set filterRange = src.Range("A1:m" & lastRow)

Set copyRange = src.Range("B2:m" & lastRow)

filterRange.AutoFilter field:=1, Criteria1:=Sheets("JOBS").Range("K1").Value

copyRange.SpecialCells(xlCellTypeVisible).Copy tgt.Range("A7:L7")

End Sub

 

 

__._,_.___

Posted by: Paul Vermeulen <paul.vermeulen@vulcantech.com.au>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)
----------------------------------
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.

----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA

----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com

----------------------------------

.

__,_._,___

Tidak ada komentar:

Poskan Komentar