Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Customer Records Both New and Changed
Greetings
I have to save my customers info from row 110 on sheet "Enter" to ether the last row (New Customer Info) or the same row (Change Current Customer Info) as the chosen customer in sheet "CI". The problem is that I am currently saving the .Value to these two locations and I really need to save the .Value only to Cells 2 - 40 and save the formula in cell A110 on "Enter" to cell 1 on "CI" (or copy the formula from the cell above cell 1 on "CI", since it is the same formula). Here is the code for what I am currently doing: Option Explicit Dim x As Long Dim wks_target As Worksheet Dim wks_source As Worksheet __________________________________________ Private Sub SaveCustInfoChanges_Click() Set wks_target = Worksheets("CI") Set wks_source = Worksheets("Enter") x = Application.WorksheetFunction. _ Match(wks_source.Range("B1"), _ wks_target.Range("A1:A" & wks_target.Range("A" & _ Rows.Count).End(xlUp).Row), 0) wks_target.Rows(x) = wks_source.Rows(110).Value End Sub __________________________________________________ Private Sub SaveInvAsNewRec_Click() Set wks_target = Worksheets("INV") Set wks_source = Worksheets("Enter") x = wks_target.Range("A65536").End(xlUp).Row + 1 wks_target.Rows(x) = wks_source.Rows(100).Value End Sub I can't seem to figure out how to split up the pasting or add the formula to cell 1 on "CI"!!! Anyone have any ideas? Any help would be appreciated. TIA -Minitman |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Customer Records Both New and Changed
Maybe just plop in the values like you did (255 (or 39) columns were ok!)
Then come back and fix column A. wks_target.Rows(x) = wks_source.Rows(110).Value wks_target.cells(x,"A").formula = wks_source.cells(110,"A").formula Minitman wrote: Greetings I have to save my customers info from row 110 on sheet "Enter" to ether the last row (New Customer Info) or the same row (Change Current Customer Info) as the chosen customer in sheet "CI". The problem is that I am currently saving the .Value to these two locations and I really need to save the .Value only to Cells 2 - 40 and save the formula in cell A110 on "Enter" to cell 1 on "CI" (or copy the formula from the cell above cell 1 on "CI", since it is the same formula). Here is the code for what I am currently doing: Option Explicit Dim x As Long Dim wks_target As Worksheet Dim wks_source As Worksheet __________________________________________ Private Sub SaveCustInfoChanges_Click() Set wks_target = Worksheets("CI") Set wks_source = Worksheets("Enter") x = Application.WorksheetFunction. _ Match(wks_source.Range("B1"), _ wks_target.Range("A1:A" & wks_target.Range("A" & _ Rows.Count).End(xlUp).Row), 0) wks_target.Rows(x) = wks_source.Rows(110).Value End Sub __________________________________________________ Private Sub SaveInvAsNewRec_Click() Set wks_target = Worksheets("INV") Set wks_source = Worksheets("Enter") x = wks_target.Range("A65536").End(xlUp).Row + 1 wks_target.Rows(x) = wks_source.Rows(100).Value End Sub I can't seem to figure out how to split up the pasting or add the formula to cell 1 on "CI"!!! Anyone have any ideas? Any help would be appreciated. TIA -Minitman -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Customer Records Both New and Changed
Hey Dave,
I just tried it and it works as written, which is to say it's not working as needed! Since it is the formula that is being copied and it is referring to the cells in row 110 (on whichever sheet the formula is placed), this formula does not adjust to the changed position. All of the references within the formula are static. In this case row 110 on "CI" is Jack Brown and the record saved is Maria Larson which is row 872 on "CI". The reference cell for Maria Larson is displaying the information for the Jack Brown record. As will every record that I save with this modification. I guess what I need is not to paste the contents of A110 on "Enter" over to the Target on "CI", but <CTRL+C the cell with everything and hit <ENTER on the target cell on "CI". Is there anyway to do that? -Minitman On Sun, 09 May 2004 08:04:54 -0500, Dave Peterson wrote: Maybe just plop in the values like you did (255 (or 39) columns were ok!) Then come back and fix column A. wks_target.Rows(x) = wks_source.Rows(110).Value wks_target.cells(x,"A").formula = wks_source.cells(110,"A").formula Minitman wrote: Greetings I have to save my customers info from row 110 on sheet "Enter" to ether the last row (New Customer Info) or the same row (Change Current Customer Info) as the chosen customer in sheet "CI". The problem is that I am currently saving the .Value to these two locations and I really need to save the .Value only to Cells 2 - 40 and save the formula in cell A110 on "Enter" to cell 1 on "CI" (or copy the formula from the cell above cell 1 on "CI", since it is the same formula). Here is the code for what I am currently doing: Option Explicit Dim x As Long Dim wks_target As Worksheet Dim wks_source As Worksheet __________________________________________ Private Sub SaveCustInfoChanges_Click() Set wks_target = Worksheets("CI") Set wks_source = Worksheets("Enter") x = Application.WorksheetFunction. _ Match(wks_source.Range("B1"), _ wks_target.Range("A1:A" & wks_target.Range("A" & _ Rows.Count).End(xlUp).Row), 0) wks_target.Rows(x) = wks_source.Rows(110).Value End Sub __________________________________________________ Private Sub SaveInvAsNewRec_Click() Set wks_target = Worksheets("INV") Set wks_source = Worksheets("Enter") x = wks_target.Range("A65536").End(xlUp).Row + 1 wks_target.Rows(x) = wks_source.Rows(100).Value End Sub I can't seem to figure out how to split up the pasting or add the formula to cell 1 on "CI"!!! Anyone have any ideas? Any help would be appreciated. TIA -Minitman -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Customer Records Both New and Changed
Ah, I get it.
Two choices: wks_target.cells(x,"A").formular1c1 = wks_source.cells(110,"A").formular1c1 (and references to the same row should stay put.) or just copy|paste and excel will adjust the references: wks_source.cells(110,"A").copy _ destination:=wks_target.cells(x,"A") Minitman wrote: Hey Dave, I just tried it and it works as written, which is to say it's not working as needed! Since it is the formula that is being copied and it is referring to the cells in row 110 (on whichever sheet the formula is placed), this formula does not adjust to the changed position. All of the references within the formula are static. In this case row 110 on "CI" is Jack Brown and the record saved is Maria Larson which is row 872 on "CI". The reference cell for Maria Larson is displaying the information for the Jack Brown record. As will every record that I save with this modification. I guess what I need is not to paste the contents of A110 on "Enter" over to the Target on "CI", but <CTRL+C the cell with everything and hit <ENTER on the target cell on "CI". Is there anyway to do that? -Minitman On Sun, 09 May 2004 08:04:54 -0500, Dave Peterson wrote: Maybe just plop in the values like you did (255 (or 39) columns were ok!) Then come back and fix column A. wks_target.Rows(x) = wks_source.Rows(110).Value wks_target.cells(x,"A").formula = wks_source.cells(110,"A").formula Minitman wrote: Greetings I have to save my customers info from row 110 on sheet "Enter" to ether the last row (New Customer Info) or the same row (Change Current Customer Info) as the chosen customer in sheet "CI". The problem is that I am currently saving the .Value to these two locations and I really need to save the .Value only to Cells 2 - 40 and save the formula in cell A110 on "Enter" to cell 1 on "CI" (or copy the formula from the cell above cell 1 on "CI", since it is the same formula). Here is the code for what I am currently doing: Option Explicit Dim x As Long Dim wks_target As Worksheet Dim wks_source As Worksheet __________________________________________ Private Sub SaveCustInfoChanges_Click() Set wks_target = Worksheets("CI") Set wks_source = Worksheets("Enter") x = Application.WorksheetFunction. _ Match(wks_source.Range("B1"), _ wks_target.Range("A1:A" & wks_target.Range("A" & _ Rows.Count).End(xlUp).Row), 0) wks_target.Rows(x) = wks_source.Rows(110).Value End Sub __________________________________________________ Private Sub SaveInvAsNewRec_Click() Set wks_target = Worksheets("INV") Set wks_source = Worksheets("Enter") x = wks_target.Range("A65536").End(xlUp).Row + 1 wks_target.Rows(x) = wks_source.Rows(100).Value End Sub I can't seem to figure out how to split up the pasting or add the formula to cell 1 on "CI"!!! Anyone have any ideas? Any help would be appreciated. TIA -Minitman -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving Customer Records Both New and Changed
Hey Dave,
That works very well. Thank you, very much. -Minitman On Sun, 09 May 2004 15:48:18 -0500, Dave Peterson wrote: wks_target.cells(x,"A").formular1c1 = wks_source.cells(110,"A").formular1c1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=now() changed to absolute apon saving | Excel Discussion (Misc queries) | |||
Excel for Customer History Records | New Users to Excel | |||
Saving : Changed by another user | Excel Discussion (Misc queries) | |||
Saving excel data that is changed weekly | Excel Worksheet Functions | |||
Text manipulaion (summarizing customer records) Index function probably not good choice | Excel Discussion (Misc queries) |