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]

See if you can use something like this.

Sub Stage()
Dim sh As Worksheet, shNm As String, rw As Range
shNm = InputBox("Enter the Stage number to find.", "STAGE NUMBER")
Set sh = Sheets("Stage " & shNm)
sh.Select
Set rw = Application.InputBox("use the mouse pointer to select the row range you want to copy.", "COPY RANGE", Type:=8)
rw.Copy Sheets("Totals").Range("A9").Offset(CLng(shNm) - 1) 'Edit sheet name
End Sub

The first inputbox allows the user to specify which stage number they want to find. Number only, no text.
The second inputbox allows the user to select the range of cells in a row to copy.
If your totals sheet is not named "Totals" you will need to make a modification to that line of code. See comments in code.

 

[Meesam_ali]

Ok based on my understanding of your problem you want to copy data based on certain "stage name" to the sheet of totals and the data should be transposed i.e. from horizontal to vertical format. Here is my code (i used only two sheets as an example and you can surely use on all of your sheets with proper naming conventions);

Sub StageWork()
Dim rs As Worksheet, wb As Workbook
Dim srchName As String, datRng As Range


Set wb = Application.ThisWorkbook


srchName = InputBox("Please enter name of stage to search for: ")


For Each rs In wb.Worksheets
    With rs
          If InStr(1, rs.Name, srchName) > 0 Then    'Check name of sheet with stage
            .Range("A143:AZ143").Copy
            wb.Sheets("Totals").Range("a9").PasteSpecial Transpose:=True
          Else
          End If
    End With
Next


End Sub

 

[Dienekes]

JLGWhiz,

This is working well and is close to what I am looking by finding the stage on the totals sheet. It is not linking to the stage when it is making equal, however just putting "=C1" etc across the row on the totals.

-Since I am on the stage # (active.sheet) needing to be copied over and the first cell in the row "A143" is the number to search for on the totals page. Is there a way that it will automatically search for that number on the totals sheet?

-And then as far as cutting out the selection. I would think just put in Range."A143:AZ143", Copy and later Pastespecial,values And that might solve the problem?

 

[Meesam_ali]

This is working well and is close to what I am looking by finding the stage on the totals sheet. It is not linking to the stage when it is making equal, however just putting "=C1" etc across the row on the totals.

I couldn't understand this part of your query? Please elaborate more so that I can update the code accordingly.
Are you saying "You are on sheet for a particular stage say for example stage-33 and you want the data in range A143:AZ143 from that sheet (current sheet) to be copied to totals" ?

 

[JLGWhiz]

JLGWhiz,

This is working well and is close to what I am looking by finding the stage on the totals sheet. It is not linking to the stage when it is making equal, however just putting "=C1" etc across the row on the totals.

-Since I am on the stage # (active.sheet) needing to be copied over and the first cell in the row "A143" is the number to search for on the totals page. Is there a way that it will automatically search for that number on the totals sheet?

-And then as far as cutting out the selection. I would think just put in Range."A143:AZ143", Copy and later Pastespecial,values And that might solve the problem?

I am not following the objective. Can you illustrate a before and after scenario?

 

[Dienekes]

I apologize, been working nights.

So my objective is to have a workbook that keeps advancing sheets named as "Stage .." (1, 2, 3, etc) with all the critical values to be collected in A143:AZ143. These values need to end up on the "Totals" page under there correct order of number ie 1, 2, 3, etc. I already have a VBA code linked to a button that will copy these over to the "Totals" sheet, however this code looks for the most recent entry and paste the data underneath the last row pasted on the totals page. Due to it finding the last entry I am having issues if I go back on a previous stage and making corrections, there is no simple vba code to hit that will find that stage number on the totals sheet and recopy values over originals.
So my objective is to make a vba code that seeks out A143 on any stage sheet, since that first row will be the number of the stage and find that unique value on row A of the "Totals" sheet and paste values over.

I did get carried away with maybe some one could create a code that would just make them equal to the row on that specific sheet, in which case no macro needed for corrections since it would automatically change.

 

[JLGWhiz]

The way I read it is that you want to copy any data in A:AZ of row 143 of the Stage sheets to a predeternined row on the Totals sheet where A1 Value of the totals sheet matches column A value on the Totals sheet. If this works for you, you can replace your current button code.

Sub Stage2()
Dim sh As Worksheet, shNm As String, rw As Range
Set sh = Sheets("Totals")
shNm = InputBox("Enter the number only for the Stage to find.", "STAGE NUMBER")
Set rw = Sheets("Stage " & shNm).Range("A143:AZ143")
rw.Copy sh.Range("A9").Offset(CLng(shNm) - 1)
End Sub

 

[JLGWhiz]

In case your totals in row 143 are derived by formula.

Sub Stage2()
Dim sh As Worksheet, shNm As String, rw As Range
Set sh = Sheets("Totals")
shNm = InputBox("Enter the number only for the Stage to find.", "STAGE NUMBER")
Set rw = Sheets("Stage " & shNm).Range("A143:AZ143")
rw.Copy 
sh.Range("A9").Offset(CLng(shNm) - 1).PasteSpecial xlPasteValues
End Sub

 

[Dienekes]

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.