VBA - Move data from one named Range to another

[OriginalCarey]

Hi guys

Struggling here if I'm honest. I've got a CRM userform system where the user inputs data, and should it fall under one of these Business Areas to go into the last row of that named range, which then expands that named range.

Business Areas:

Marketing Retail Direct (named range name: rngMRD)
Know Me (named range name: rngKnowMe)
Cross Functional Spend (named range name: rngCFS)
Global Revenue (named range name: rngGlobalRevenue)

The data saves into the "DataRoom" sheet.

The users then have the ability to load up that data in a replicated "View" system, and make changes to that row of data and then save back into the "DataRoom", overwriting changes.

However - what I need to do is give the System the ability to should the user change the Business Area from say "Know Me" to "Marketing Retail Direct", that the row of data is moved out of rngKnowMe and moved into rngMRD. The named range for MRD will need to increase by 1 and the named range of Know Me will need to of course decrease by one.

At the moment the system is built so all named ranges are together, ie; it'll find the last row within the named range of Know Me and place in their, expanding the Named Range of Know Me by 1 - thus all "Business Areas" are together for use within another section of the System..

Could anyone assist me with this?

As a further info - the name of the "listbox" within the Userform where the current Business Area, and where they can change it to another, is called "listBusinessArea"

Cheers

Carey

 

[AlphaFrog]

Can you show your code; Specifically the parts where you populate the Userform and where you write back to the worksheet?

 

[oldbrewer]

[TABLE="width: 1152"]
<colgroup><col width="64" span="18" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]area[/TD]
[TD="width: 64"]sales[/TD]
[TD="width: 64"]identcode[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD]area1[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]data to be added[/TD]
[TD]area3[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD="colspan: 3"](7 comes from master table)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area2[/TD]
[TD="align: right"]367[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area3[/TD]
[TD="align: right"]389[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area1[/TD]
[TD="align: right"]411[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area2[/TD]
[TD="align: right"]433[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]easy to add new data on the right to a master table[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area3[/TD]
[TD="align: right"]455[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]but should that area3 be changed to area2 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area3[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]user would need to use the identcode[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]to make the amendment[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]using this approach there is no need to have[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]different ranges for each "area"[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]just use first blank row[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]having multi users adding and changing data[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]can be a problem[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]have you considered using access ?[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[OriginalCarey]

Can you show your code; Specifically the parts where you populate the Userform and where you write back to the worksheet?

Private Sub CommandButton1_Click()

Dim vreg As String, drow As Integer, c As Range

ThisWorkbook.Sheets("DataRoom").Select



   n = Application.Match(Me.ListBox1.Value, Range("A:A"), 0)
   
   'Extra rows to be added.
   
   Cells(n, 1).Value = Me.txtIAGCode.Text
   Cells(n, 2).Value = Me.txtTrainingName.Text
    Cells(n, 3).Value = Me.listcategories.Text
   Cells(n, 4).Value = Me.listSubCat.Text
   Cells(n, 5).Value = Me.DateTraining.Value
   Cells(n, 6).Value = Me.listStatus.Text
   Cells(n, 7).Value = Me.txtForecast.Text
   Cells(n, 8).Value = Me.txtCommitted.Text
   Cells(n, 9).Value = Me.txtActual.Text
   Cells(n, 10).Value = Me.txtImpScore.Text
   Cells(n, 11).Value = Me.txtReqName.Text
   Cells(n, 12).Value = Me.txtStaffNum.Text
   Cells(n, 13).Value = Me.txtEveDes.Text
   Cells(n, 14).Value = Me.txtBusRat.Text
   Cells(n, 15).Value = Me.txtImp.Text
   Cells(n, 16).Value = Me.DateReq.Value
   Cells(n, 17).Value = Me.txtManName.Text
   Cells(n, 18).Value = Me.txtDepName.Text
   Cells(n, 19).Value = Me.txtBPName.Text
   Cells(n, 20).Value = Me.txtSupName.Text
   Cells(n, 21).Value = Me.TxtConName.Text
   Cells(n, 22).Value = Me.txtEMail.Text
   Cells(n, 23).Value = Me.txtTelNum.Text
   Cells(n, 24).Value = Me.txtSupAdd.Text
   Cells(n, 25).Value = Me.txtSupPC.Text
   Cells(n, 26).Value = Me.txtNumAtt.Text
   Cells(n, 27).Value = Me.txtCostAtt.Text
   Cells(n, 28).Value = Me.txtROI.Text

Unload Me

End Sub

This is the code to write the data back into the DataRoom.

- I can't use Access, my Client requested it built into Excel.

 

[OriginalCarey]

This is the userform populating code from their selection on ListBox1

Private Sub ListBox1_Click()

Dim vreg As String, drow As Integer, c As Range

ThisWorkbook.Sheets("DataRoom").Select

   Let vreg = ListBox1.Value
   With ThisWorkbook.Sheets("DataRoom").Range("a:a")
      Set c = .Find(vreg, LookIn:=xlValues, LookAt:=xlWhole)
      If Not c Is Nothing Then
         Let drow = c.Row
      End If
   End With
   
'//LOAD IN IAG2BUY DETAILS\\'



On Error GoTo Error

Let Me.txtIAGCode.Value = ThisWorkbook.Sheets("DataRoom").Cells(drow, 1).Value
Let Me.txtTrainingName.Value = ThisWorkbook.Sheets("DataRoom").Cells(drow, 2).Value
Let Me.listcategories.Value = Sheets("DataRoom").Cells(drow, 3).Value
Let Me.listSubCat.Value = Sheets("DataRoom").Cells(drow, 4).Value
Let Me.DateTraining.Value = Sheets("DataRoom").Cells(drow, 5).Value
Let Me.listStatus.Value = Sheets("DataRoom").Cells(drow, 6).Value
Let Me.txtForecast.Value = Sheets("DataRoom").Cells(drow, 7).Value
Let Me.txtCommitted.Value = Sheets("DataRoom").Cells(drow, 8).Value
Let Me.txtActual.Value = Sheets("DataRoom").Cells(drow, 9).Value
Let Me.txtImpScore.Value = Sheets("DataRoom").Cells(drow, 10).Value
Let Me.txtReqName.Value = Sheets("DataRoom").Cells(drow, 11).Value
Let Me.txtStaffNum.Value = Sheets("DataRoom").Cells(drow, 12).Value
Let Me.txtEveDes.Value = Sheets("DataRoom").Cells(drow, 13).Value
Let Me.txtBusRat.Value = Sheets("DataRoom").Cells(drow, 14).Value
Let Me.txtImp.Value = Sheets("DataRoom").Cells(drow, 15).Value
Let Me.DateReq.Value = Sheets("DataRoom").Cells(drow, 16).Value
Let Me.txtManName.Value = Sheets("DataRoom").Cells(drow, 17).Value
Let Me.txtDepName.Value = Sheets("DataRoom").Cells(drow, 18).Value
Let Me.txtBPName.Value = Sheets("DataRoom").Cells(drow, 19).Value
Let Me.txtSupName.Value = Sheets("DataRoom").Cells(drow, 20).Value
Let Me.TxtConName.Value = Sheets("DataRoom").Cells(drow, 21).Value
Let Me.txtEMail.Value = Sheets("DataRoom").Cells(drow, 22).Value
Let Me.txtTelNum.Value = Sheets("DataRoom").Cells(drow, 23).Value
Let Me.txtSupAdd.Value = Sheets("DataRoom").Cells(drow, 24).Value
Let Me.txtSupPC.Value = Sheets("DataRoom").Cells(drow, 25).Value
Let Me.txtNumAtt.Value = Sheets("DataRoom").Cells(drow, 26).Value
Let Me.txtCostAtt.Value = Sheets("DataRoom").Cells(drow, 27).Value
Let Me.txtROI.Value = Sheets("DataRoom").Cells(drow, 28).Value


Error:
Exit Sub
UF2.Show

End Sub

 

[oldbrewer]

[TABLE="width: 1024"]
<tbody>[TR]
[TD]area[/TD]
[TD]sales[/TD]
[TD]identcode[/TD]
[TD][/TD]
[TD]rows[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area1[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area2[/TD]
[TD="align: right"]367[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area3[/TD]
[TD="align: right"]389[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area3[/TD]
[TD="align: right"]411[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area2[/TD]
[TD="align: right"]433[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area3[/TD]
[TD="align: right"]455[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]area3[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]now let us assume that a change is required to[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]the table on the left[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]the above table was successfully changed by this macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]identcode[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]For j = 2 To Cells(1, 6)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]new area[/TD]
[TD]area3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"] If Cells(j, 3) = Cells(13, 11) Then Cells(j, 1) = Cells(15, 11)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] Next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]End Sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]identcode 4 relating to 411[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 3"]is now changed to area3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]data before change[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]area[/TD]
[TD]sales[/TD]
[TD]identcode[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]area1[/TD]
[TD="align: right"]345[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]area2[/TD]
[TD="align: right"]367[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]area3[/TD]
[TD="align: right"]389[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]area1[/TD]
[TD="align: right"]411[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]area2[/TD]
[TD="align: right"]433[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]area3[/TD]
[TD="align: right"]455[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]area3[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[OriginalCarey]

Hi mate

I have to be honest, I really don't understand what you''ve done!!

 

[oldbrewer]

I just showed you how to go in and amend one of the lines in your data from area1 to area3

at this stage I have not given the code to paste new data into your table as I was unsure if you would be interested in this approach.

 

[OriginalCarey]

I just showed you how to go in and amend one of the lines in your data from area1 to area3

at this stage I have not given the code to paste new data into your table as I was unsure if you would be interested in this approach.

Hi mate thanks for clarifying

What I really need is for if the user changes lets say a Row of Data that is currently under "Know Me" and thus sits within the "Know Me" named range section, and they change that at a future date to lets say "Global Revenue", I need that Row of data to be stripped out of "Know me" named range, and plonked into the "Global Revenue" named range, and have the Named ranges changed.

 

[oldbrewer]

ok I really do not see why all data cannot sit in just one table, and say column A can be "know me" or "global revenue", then one simple change in one cell does the business. The single table can be analysed with a pivot table, or by other means..........