PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Am referencing data want to place actual value

Reply
Thread Tools Rate Thread

Am referencing data want to place actual value

 
 
Bud
Guest
Posts: n/a
 
      11th Jan 2009
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
 
Reply With Quote
 
 
 
 
Mike H
Guest
Posts: n/a
 
      11th Jan 2009
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
 
Reply With Quote
 
Bud
Guest
Posts: n/a
 
      11th Jan 2009
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
 
Reply With Quote
 
Bud
Guest
Posts: n/a
 
      11th Jan 2009
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
 
Reply With Quote
 
 
 
Reply

« Need program logic to sort fieldA & delete duplicates | Maximize Userform »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
referencing the actual row number? How? pete Microsoft Excel Programming 1 23rd May 2008 06:25 PM
Page Reference referencing actual page # not what's in footer jennifer72401 Microsoft Word Document Management 7 27th Feb 2008 08:03 PM
Place actual selection name in table instead of ID #? =?Utf-8?B?VEtN?= Microsoft Access Form Coding 5 3rd Aug 2007 03:20 AM
How to use a variable in place of actual control name? =?Utf-8?B?TWFj?= Microsoft Access 3 1st Feb 2007 01:50 PM
how do I place an actual return key stroke in access record =?Utf-8?B?ZWRt?= Microsoft Access 7 17th May 2005 08:13 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 07:44 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top