Cut Paste Problem

[vestax22]

Hi, I have a worksheet that extracts data from another worksheet. Upon extraction two fields from the original file are combined or added together to form one field. The way I do this is I copy the contents of the original to the new file once copied i then do the sum of the two fields to be combined and place the results of inside the first field. I then cut out the old field and drag the remaining fields back. The problem is that there are fixed formulas beside the data that seem to adjust to this cut and paste when I want them to stay the same. I tried using absolute values for the address but it doen't work...

Any ideas??

Thx in advance

 

[ChipsIT]

Hi

I'm not an Excel expert but if I understand your post correctly, can you not place a formula in your original sheet, outside of the main workspace, to reference the required cell in the second ie. a dynamic link to the other sheet? Your code can then update the original cell by adding the linked field to it's current value. If the worksheets are in the same file I know you can reference them. For example, in Sheet1 you can have a formula =Sheet2!A1.

Another idea is to use a separate worksheet in your original file to do all your working/calculations so you don't have to play around with the layout of your original.

I hope this helps somewhere!

Good luck
Steve

 

[SkipVought]

Hey V,

Give us the formulas as well as the procedure.

Your description does not cut it.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[xlbo]

boom boom Skip !

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vestax22]

This is the code that retrieves the data from another worksheet. The sub Combine data performs the sum of the two column and does the cut copy.


Private Sub getDataBtn_Click()

Dim fullReportBook As String
Dim sizingPath As String
Dim sizingBookName As String
Dim fileNotFoundMsg As String
Dim bopen As Boolean
Dim wb As Workbook

fullReportBook = ActiveWorkbook.Name
sizingBookName = "Sizing.xls"
sizingPath = "\\Wpnass02\Temp\" & sizingBookName
fileNotFoundMsg = "File Sizing.xls was not found, " & _
"Please make sure the file is present and try again"

Application.ScreenUpdating = False

bopen = False
For Each wb In Workbooks
If wb.Name = sizingBookName Then bopen = True
Next

If bopen = True Then
Workbooks(sizingBookName).Activate
Else
'On Error GoTo fileError
Workbooks.Open (sizingPath)
End If

monthData.Columns("A:N&quot.ClearContents

copySizingData "Standard 1%"
copySizingData "Flux 1%"
copySizingData "Standard 2%"
copySizingData "Flux 2%"


Workbooks(sizingBookName).Close
combineSizing

Menu.Activate

Application.ScreenUpdating = True

response = MsgBox("Data retrieved successfully", vbOKOnly + vbInformation, "Success&quot

Exit Sub


fileError:
Application.ScreenUpdating = True
response = MsgBox(fileNotFoundMsg, vbOKOnly + vbExclamation, "File not found&quot

End Sub

Sub copySizingData(pelletType As String)

Dim firstAddress As Variant
Dim secondAddress As Variant
Dim dataRange As String
Dim totalRange As String

Select Case pelletType

Case "Standard 1%":
dataRange = "Std1pData"
totalRange = "Std1pTotals"
Case "Flux 1%":
dataRange = "Flx1pData"
totalRange = "Flx1pTotals"
Case "Standard 2%":
dataRange = "Std2pData"
totalRange = "Std2pTotals"
Case "Flux 2%":
dataRange = "Flx2pData"
totalRange = "Flx2pTotals"
End Select


With Worksheets("Feuil1&quot


.Range("A1&quot.Select
While Selection.Value <> pelletType
Selection.Offset(1, 0).Select
Wend
firstAddress = Selection.Address
While Selection.Value <> &quot;Month Totals&quot;
Selection.Offset(1, 0).Select
Wend
Selection.Offset(-2, 14).Select
secondAddress = Selection.Address

.Range(firstAddress & &quot;:&quot; & secondAddress).Copy _
Destination:=SPCReportBook.monthData.Range(dataRange)

.Activate
Selection.Offset(2, -14).Select
firstAddress = Selection.Address
Selection.Offset(1, 14).Select
secondAddress = Selection.Address

.Range(firstAddress & &quot;:&quot; & secondAddress).Copy _
Destination:=SPCReportBook.monthData.Range(totalRange)


End With







End Sub


Sub combineSizing()

Dim firstValue As Double
Dim secondValue As Double

With monthData

.Activate
.Range(&quot;E4&quot.Select
For element = 0 To 100
If Selection.Value <> &quot;&quot; Then
If Left(Selection.Value, 6) Like &quot;Sizing&quot; Then
Selection.Value = &quot;Sizing +1/2&quot;
Else
If IsNumeric(Selection.Value) Then
firstValue = Selection.Value
Selection.Offset(0, 1).Select
secondValue = Selection.Value
Selection.Offset(0, -1).Select
Selection.Value = firstValue + secondValue
End If
End If
End If
Selection.Offset(1, 0).Select
Next

.Columns(&quot;G:O&quot.Cut _
Destination:=.Columns(&quot;F:N&quot

End With


End Sub


Here are the formulas on the worksheet the data is being copied to :

=NB.SI($B$6:$B$36;&quot;>&quot;&U6) - NB.SI($B$6:$B$36;&quot;>&quot;&V6)

My excell is in frence so NB.SI means CountIf.

This formula adjusts itelf when the sum and cut copy operations are done since theye are done in the same worksheet as the formula. If there is no way to fix this problem I will perform the sum and cut copy operations before copying the data.

Thx for the help