Am referencing data want to place actual value

[Bud]

Hello

I am pulling data from another worksheet and have referenced the data. I
would rather pull the data straight over instead of having the reference
formula show up.

How can I change the following formula to do this

Dim br As Long
br = Cells(Rows.Count, "b").End(xlUp).Row

'SWIM WBSE Details worksheet
'Start of pasting to the SWIM WBSE Details worksheet
'Select SWIMTimeDataSav as we are going to build the UPLOAD file
Sheets("SWIM WBSE Details").Select
Cells.Select
' First Clear all contents of worksheet
ActiveSheet.Cells.ClearContents
' Second Add a line that describes the data fields
' Also add the formulas for obtaining the data

Cells(1, "a") = "WBSE Number"
Cells(1, "b") = "WBSE Description"
Cells(1, "c") = "Project Cost Centre"

Cells(2, "a") = "='SWIM Time Data'!F2" 'WBSE Number
Cells(2, "b") = "='SWIM Time Data'!I2" 'Project name
' Thirdly Auto fill down for the number rows we obtained from the
SAP-Simulation
Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a"))
Cells(2, "b").AutoFill Destination:=Range(Cells(2, "b"), Cells(br, "b"))
Columns("A:A").ColumnWidth = 24.75
Columns("B:B").ColumnWidth = 20.7
Range("A2").Select
Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

 

[Mike H]

Hi,

I've made an attempt to tidy your code up. To answer your quaestion after
you've copied the data over copy it again in place and paste special|paste
values.


Dim br As Long
br = Cells(Rows.Count, "b").End(xlUp).Row
Sheets("SWIM WBSE Details").Activate

With Sheets("SWIM WBSE Details")
.UsedRange.ClearContents
.Cells(1, "b") = "WBSE Description"
.Cells(1, "c") = "Project Cost Centre"
.Cells(2, "a") = "='SWIM Time Data'!F2" 'WBSE Number
.Cells(2, "b") = "='SWIM Time Data'!I2" 'Project name
End With

' Thirdly Auto fill down for the number rows we obtained from theSAP
-Simulation
Cells(2, "a").AutoFill Destination:=Range(Cells(2, "a"), Cells(br, "a"))
Cells(2, "b").AutoFill Destination:=Range(Cells(2, "b"), Cells(br, "b"))
'paste values

Range("A2:b" & br).Copy
Range("A2").PasteSpecial Paste:=xlPasteValues

Columns("A:A").ColumnWidth = 24.75
Columns("B:B").ColumnWidth = 20.7
Range("A2").Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Mike

 

[Bud]

Thanks Mike. Worked perfect.

I had another question

Is there some logic that once that was built could delete the duplicates
using column a to determine the duplicates. Project cost column has nothing
in it.

Here is the data

WBSE Number WBSE Description Project Cost
Centre
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000002673-970728-NHO9 ASFOA_US_Healthcare3
K-0000007526-000500-BSMG ASFOA_US_Healthcare1
K-0000007526-000705-MAIN ASFOA_US_Healthcare1
K-0000007526-000705-MAIN ASFOA_US_Healthcare1
K-0000007526-000705-MAIN ASFOA_US_Healthcare1
K-0000007526-000705-MINC ASFOA_US_Healthcare1
K-0000007526-000705-MINC ASFOA_US_Healthcare1
K-0000007526-000705-MINC ASFOA_US_Healthcare1
K-0000007526-000705-MSOO ASFOA_US_Healthcare1
K-0000007526-000705-MSOO ASFOA_US_Healthcare1
K-0000007526-000705-MSOO ASFOA_US_Healthcare1
K-0000007526-000705-PROB ASFOA_US_Healthcare1
K-0000007526-000705-PROB ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-5677-000701-CONS ASFOA_US_Healthcare1
K-6191-000500-PMGT ASFOA_US_Healthcare1
K-6191-000500-PMGT ASFOA_US_Healthcare1
K-6191-000500-PMGT ASFOA_US_Healthcare1
K-6191-000500-PMGT ASFOA_US_Healthcare1
K-6191-000500-PMGT ASFOA_US_Healthcare1
K-6191-000500-PMGT ASFOA_US_Healthcare1
K-6191-000600-PTRN ASFOA_US_Healthcare1
K-6191-000600-PTRN ASFOA_US_Healthcare1
K-6191-000705-MAIN ASFOA_US_Healthcare1
K-6191-000705-MAIN ASFOA_US_Healthcare1
K-6191-000705-MAIN ASFOA_US_Healthcare1
K-6191-000705-MINC ASFOA_US_Healthcare1
K-6191-000705-MINC ASFOA_US_Healthcare1
K-6191-000705-MINC ASFOA_US_Healthcare1
K-6191-000705-MINC ASFOA_US_Healthcare1
K-6191-000705-MINC ASFOA_US_Healthcare1
K-6191-000705-MINC ASFOA_US_Healthcare1
K-6191-000705-MINC ASFOA_US_Healthcare1
K-6191-000705-MINC ASFOA_US_Healthcare1
K-6191-000705-MSOO ASFOA_US_Healthcare1
K-6191-000705-PROB ASFOA_US_Healthcare1
K-6191-000705-PROB ASFOA_US_Healthcare1

 

[Bud]

Thanks again Mike. This was perfect!

Please disregard the note regarding duplicates. I have the answer I need