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]

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

Doesn't produce a usable screenshot that can be copied and pasted from only an image (see the previous post).

 

[MARK858]

@ richardswaim, can you post a screenshot of one of the sheets it is copying from as well.

If you haven't installed anything to post a usable screenshot then post an image and I will see if it is practical for me to write something without testing (no promises there though).

 

[richardswaim]

Here you go,

This is a screenshot of an active sheet.

 

[richardswaim]

I will be adding data to each active sheet from time to time. I will be needing to add parts on the left side, adding labor and other data. I need to have that data transferred to the Data sheet. It is listed on the tabs on the bottom of the workbook. On the screenshot, you can see a CommandButton that reads (Save to data). That is the button I am using to add the data onto Sheet2(Data).

 

[MARK858]

@richardswaim, I know this might seem like a silly question but as I can't see anything majorly wrong with your code I will ask it anyway.

Is your current issue just that the new entry is currently overwriting the previous entry in the Data sheet?

 

[richardswaim]

Mark858, another problem I am having, in the code I posted earlier, I am getting a Run-time error '13', Type mismatch for line

objX.Object.Value = ""

in the code. Do you have an idea what it may need to resolve the error message.

 

[MARK858]

What Object are you trying to clear the value of?

 

[richardswaim]

I would like to clear the data on the whole sheet, but I have 39 dropdown boxes and some 400 formula driven text boxes. The cells that are being updated are formula driven cells. The Sheet1(Work Order) is the master worksheet, everything starts with it, then is copied to each Worksheet and named according to cell "M1".

I don't want the master to be totally set back to Nothing, that would remove all dropboxes and formulas. I just want it to be returned to the original forms condition before having any data entered on it, including the initialization at the very beginning of the code.

 

[MARK858]

Sorry but I still don't understand what objects you are trying to clear. What exactly do you mean by

39 dropdown boxes

Is what you are calling a "dropdown box" some sort of ActiveX object?

What do you actually want cleared exactly? the dropboxes (remember I can't see what you are looking at)?

If yes what do you get if you record yourself clearing 1 dropbox manually?

You should really concern yourself with one problem at a time and get that working first before going on to the next stage which in this case is the transferring of the data.

As far as that is concerned all that I can see is needed is adding a +1 (it could be tidied a bit or turned in to arrays but no real gain with data that small).

Private Sub CommandButton2_Click()

Dim lastRow As Long
Dim wsMasterSheet As Worksheet
Dim wsDataSheet As Worksheet

Set wsMasterSheet = ActiveSheet
Set wsDataSheet = ThisWorkbook.Sheets("Data")

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

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

End Sub

 

[richardswaim]

By adding the +1 to that line, causes the code to put an entry, A16-102, on the Data sheet, say in Row 10 for example. Then when I click on the "Save" button, it puts the same A16-102 in Row 11 but does place the desired info in the rest of that Row. It duplicates the Work Order number on 2 Rows.

The master sheet is made up of "drop boxes" and formulated cells. "Drop Boxes" have the down arrow on the side of the cell and allow me to select a value, in Excel. On the left side of the Master sheet, you can see a cell under Part#, all those cells are drop boxes with data from the Parts worksheet. When a Part# is selected, I have formulas in the next cells to auto-populate the Description and Price in those cells on that Row. Then placing a quantity*price formula, puts the total price in the total cell.

I don't want to cause the form to have the dropdown boxes deleted, just set back to "Start" condition. Does that help you?