Update data code

[richardswaim]

In my workbook, I have a worksheet , Sheet1(Work Order), created by a commandbutton1, that command created a worksheet and copied Sheet1(Work Order) to that Tab and named the worksheet according to the value of cell"M1"(A16-102). At the same time, it entered data from that copied worksheet to another worksheet, Sheet2(Data). On Sheet2(Data), the name of the worksheet (A16-102) is placed in Column A, along with 5 other cells of value entered on the same row. At another point in time, I will make changes to A16-102 and will need the cells in Sheet2(Data) to reflect those changes.

I need a code that I will be able to place on each worksheet that will look at the value of Cell A in a given row and make the changes to update the data in that row. Does that make sense?

My current code is:

'Transfer information
wsDataSheet.Cells(lastRow, 1).Value = ActiveSheet.Range("M1").Value
wsDataSheet.Cells(lastRow, 2).Value = ActiveSheet.Range("J8").Value
wsDataSheet.Cells(lastRow, 3).Value = ActiveSheet.Range("G8").Value
wsDataSheet.Cells(lastRow, 4).Value = ActiveSheet.Range("O26").Value
wsDataSheet.Cells(lastRow, 5).Value = ActiveSheet.Range("O25").Value
wsDataSheet.Cells(lastRow, 6).Value = ActiveSheet.Range("O27").Value
wsDataSheet.Cells(lastRow, 7).Value = ActiveSheet.Range("N30").Value

End Sub

I believe the wsDataSheet.cells is wrong, cause Sheet2(Data) is name of the sheet I need it transferred to. I don't know that Transfer information is even the right type to use. I know it's not easy, but can I get some help?

 

[MARK858]

Post the rest of the code including where wsDataSheet is defined (when you paste the code, select the code and click the # icon to put code tags around the code).
Is the created worksheet the activesheet at the time that you run the code?

 

[richardswaim]

Yes Mark858, the sheet created is the active sheet at the time I am trying to update that info. I will post the code that originally places the info into the Sheet2(Data). It is :

Private Sub CommandButton2_Click()

Dim strInput As String
Dim strOutput
Dim intOutput As String
Dim lastRow As Long
Dim wsMasterSheet As Worksheet
Dim wsDataSheet As Worksheet
Dim wsNewSheet As Worksheet
Dim strNewSheetName As String
Dim objX As Object

Set wsMasterSheet = ActiveSheet
wsMasterSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = Range("M1").Value
Set wsDataSheet = ThisWorkbook.Sheets("Data")

lastRow = wsDataSheet.Cells(wsDataSheet.Rows.Count, "A").End(xlUp).Row
lastRow = lastRow

'Transfer information
wsDataSheet.Cells(lastRow, 1).Value = wsMasterSheet.Range("M1").Value
wsDataSheet.Cells(lastRow, 2).Value = wsMasterSheet.Range("J8").Value
wsDataSheet.Cells(lastRow, 3).Value = wsMasterSheet.Range("G8").Value
wsDataSheet.Cells(lastRow, 4).Value = wsMasterSheet.Range("O26").Value
wsDataSheet.Cells(lastRow, 5).Value = wsMasterSheet.Range("O25").Value
wsDataSheet.Cells(lastRow, 6).Value = wsMasterSheet.Range("O27").Value
wsDataSheet.Cells(lastRow, 7).Value = wsMasterSheet.Range("N30").Value

wsDataSheet.Select

With wsMasterSheet
    For Each objX In .OLEObjects
    objX.Object.Value = ""
    Next objX
End With


End Sub

I need a code to put in the CommandButton(Update Data) that will place the updated data into Sheet2(Data). This Command Button(Update Data) will not need to create a worksheet and name it the value of "M1" as in this code, but it will need to search the "A" row for the corresponding name of the activesheet and then place the data from the listed cells, in the formula, into their assigned cells.

Make sense?

 

[MARK858]

Make sense?

Afraid not to me.

First of all wsDataSheet is Sheets("Data").

Probably the code you posted is confusing the issue but you put

but it will need to search the "A" row for the corresponding name of the activesheet

"A" normally refers to columns not rows and your code is using 6 different columns

wsDataSheet.Cells(lastRow, [COLOR="#FF0000"]1[/COLOR])

to

wsDataSheet.Cells(lastRow, [COLOR="#FF0000"]6[/COLOR])

for what I am assuming are the "assigned cells" (the other sheet is using 4 different columns)

Please post some screenshots of Sheet2(Data) and the activesheet you are copying from so we can see the layout.
See my signature block below for some links to how to post usable screenshots.

Btw, what you have posted is VBA code not a formula.

 

[richardswaim]

I stand corrected, as you can tell, I am very new to VBA. I sure wished I had taken this up years ago, it is fascinating. I will see what I can do about the screenshots, not sure how to do that, but I will get it done. Please be patient with me on this, I want this to work so bad.

 

[richardswaim]

OK, I thought I could make a screenshot and place it on here. How do I do that without a URL location?

 

[Caleeco]

OK, I thought I could make a screenshot and place it on here. How do I do that without a URL location?

Upload your file to TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

Tiny pic will create a URL for you to use here

Regards
Caleeco

 

[richardswaim]

Forgive my ignorance of not getting screenshots placed in here, I will learn how to accomplish that. This is what the Sheet2(Data) looks like. Does this help?

Work Order Number Date Unit Number Parts Labor Add. Labor Total
A16-100
A16-101 6/3/16 2:23 PM 204 $5.78 $0.00 $15.00 $20.78
A16-102 6/3/16 2:36 PM 401 $40.46 $0.00 $30.00 $70.46
A16-103 6/3/16 3:48 PM 401 $5.78 $0.00 $48.00 $53.78
A16-104 6/3/16 3:53 PM 401 $11.12 $600.00 $16.00 $627.12
A16-105 6/4/16 11:16 AM 401 $11.12 $600.00 $16.00 $627.12
A16-106 6/4/16 11:19 AM 401 $11.12 $600.00 $16.00 $627.12
A16-107 6/4/16 1:45 PM T02 $23.12 $600.00 $16.00 $639.12

 

[MARK858]

How do I do that without a URL location

As I stated there are links in my signature block at the bottom of every post I make.

If you are saying that you can't download an add-in then you are looking at the borders copy paste link in my signature block, it only leads you to another thread with instructions (the only trouble is you have to put in the row and column references).

If you can download add-ins then in the HTML zip folder in the MrExcel HTML Maker there is a word document with installation instructions (there is also a video if needed) and on the Excel Jeanie web page click on activating E2007 on the left hand side. Once you have it installed click the "How to" for basic usage.

Rory's addin will be basically the same procedure to install as I don't think he put in any auto install (been a while though since I downloaded it).

 

[richardswaim]

OK, let's try this!