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

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

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

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

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