| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Why is the actual calculation different from actual sample | Excel Discussion (Misc queries) | |||
| referencing the actual row number? How? | Excel Programming | |||
| Can I display the actual data in the data field of a pivot chart? | Excel Discussion (Misc queries) | |||
| Scrolls Far Below Actual Data | Excel Discussion (Misc queries) | |||
| Scrolls Far Below Actual Data | Excel Discussion (Misc queries) | |||
Linear Mode
