ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Am referencing data want to place actual value (https://www.excelbanter.com/excel-programming/422293-am-referencing-data-want-place-actual-value.html)

Bud
Am referencing data want to place actual value
 
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
Am referencing data want to place actual value
 
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" wrote:

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

Bud
Am referencing data want to place actual value
 
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


"Mike H" wrote:

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" wrote:

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

Bud
Am referencing data want to place actual value
 
Thanks again Mike. This was perfect!

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

"Mike H" wrote:

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" wrote:

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


All times are GMT +1. The time now is 04:23 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com