VBA code that copies (0r sets equal) a row of data on one sheet to a "totals" sheet.

[Dienekes]

Hello all,
I have been on this thread a lot reading and learning formulas and VBA codes but I have one that I am stumped on and cannot find a common thread to learn from.

I have a workbook with sheets that start and are named as "Stage 1" and go on to "Stage 50".
Each sheet is identical with a row of data that collects on A143:AZ143. (A143 Being the number of the stage ex. (1) for stage 1).

With these I am looking for help building a vba code that ask what stage number you are trying to find and then paste the row from the stage sheet on the totals sheet over the numbers counting down from A9 and on to A59 for the 50 stages.

There are other sheets I need to link to as well but I feel if I can get this code down I can link it to the other totals sheets in similar form.

Thank you for any help,
Nate

 

[JLGWhiz]

This one works perfectly, awesome! Thank you. Going to run through the workbook and apply this on multiple sheets. Hopefully I can understand the sum of it to link it to other sheets.

Although I don't know which one "this one" is, glad you could use one of them.
Regards, JLG

 

[Dienekes]

This is the one I am running:

Sub StageTotalCorrections()'Allows corrections on each sheet if changes are made after advancing to next stage.


Dim sh As Worksheet, shNm As String, rw As Range


Set sh = Sheets("Summarization")
shNm = InputBox("Enter Current Stage number for data to copy over", "STAGE NUMBER")
Set rw = Sheets("Stage " & shNm).Range("A143:BE143")
rw.Copy
sh.Range("A9").Offset(CLng(shNm) - 1).PasteSpecial xlPasteValues

I have another question though and its more to do with understanding how the code finds a stage number "3", "2", "etc" to copy over to correct. Is there away to get the code to search for a name or identical word? I'm asking because the job may have 2 subjects with stages. So the list would go something like below and I might have to make a correction on one and ask VBA to copy over previous.

Sherry 1720-1HX - 1

Margie 0508-1HX - 1

Sherry 1720-1HX - 2

Margie 0508-1HX - 2

Sherry 1720-1HX - 3

Margie 0508-1HX - 3

Margie 0508-1HX - 4

Margie 0508-1HX - 5

Margie 0508-1HX - 6

Sherry 1720-1HX - 4

Sherry 1720-1HX - 5

<colgroup><col></colgroup><tbody>
</tbody>

 

[sassriverrat]

Here's another that works. Just change "noon" to "stages" and set your cells (mine was N12) and you can delete R9 from the addition....

Function TotalNoonSheetAdd()

'Begins Error Handling Code
On Error GoTo Helper


'Has the ability to add all values from multiple noons and a single cell selection


WS_Count = ActiveWorkbook.Worksheets.Count
Eqat = "="
nooncnt = 0
        For i = 1 To WS_Count
            Tname = ActiveWorkbook.Worksheets(i).name
            If Left(Tname, 4) - "Noon" Then
             Eqat = Eqat & "+" & "Noon" & "!N12"
             nooncnt = nooncnt + 1
            End If
        Next i
 If nooncnt > 0 Then
  ActiveCell.Formula = Eqat + Range("R9")
 End If
 
 'Error Clearing Code
Exit Function
Helper:
    resp = MsgBox("We're sorry to see you've encountered an error." & vbCrLf & vbCrLf & "To proceed, we recommend you contact the Developer " & _
    "with error codes [1161] and " & "[" & Err.Number & "-" & Err.Description & "]." & vbCrLf & vbCrLf & "To attempt to patch your problem at least " & _
    "temporarily, we recommend you click [Yes] to see help directions. Would you like to continue?", vbYesNoCancel, name)
        If resp = vbYes Then
            UserForm18.Show
            'MsgBox ("Success")
        ElseIf resp = vbNo Then
            Exit Function
        ElseIf resp = vbCancel Then
            Exit Function
        End If
        
End Function

 

[Dienekes]

Not really having much luck with that code.

 

[sassriverrat]

Sorry for the delay. Ok what cells are you trying to add from each sheet? This is a function so you would type it in like you would a formula.

 

[sassriverrat]

updated for you and confirmed working.....

Sub Totaler()

'Has the ability to add all values from multiple noons and a single cell selection


'Currently set to take a specified cell (In this case "A1") from each sheet
'called "Stages"  to change this, just change "Stages" below to
'the new sheet name(s) and change the cell to whatever you like


'Also- you can set it to add "A1" from each sheet called stages plus another cell,
'(in this case currently "A2" on the activesheet, to change this, change A2 to another
'cell or just delete the whole "+A2" piece.


'Once this macro is added, go back to the designed cell and type in
' "=Totaler(A1)" into the desired cell.....just like any other cell formula.


WS_Count = ActiveWorkbook.Worksheets.Count
Eqat = "="
nooncnt = 0
        For i = 1 To WS_Count
            Tname = ActiveWorkbook.Worksheets(i).name
            If Left(Tname, 4) = "Stages" Then   'change sheet name to desired sheet name
             Eqat = Eqat & "+" & Tname & "!A1"  'change this to desired cell from each sheet
             nooncnt = nooncnt + 1
            End If
        Next i
 If nooncnt > 0 Then
  ActiveCell.Formula = Eqat & "+A2"             'add or delete this piece for desired additional cell
 End If
 
        
End Sub