Moving Data from 1 Source Worksheet across multiple worksheets in the same workbook

[envious22]

Good afternoon,


I am attempting to streamline a tracking sheet being utilized by several of our employees. There are about 100 worksheets in the workbook currently, each named after a unique unit number. This makes it a nightmare to navigate efficiently and I am attempting to use an Input sheet to streamline the process.


I have created a sheet with cells for all the relevant data they track daily. I am attempting to use the macro to define the worksheet from a cell value (located in B8:B31). I want it to use the cell in column B to find the worksheet, then copy the relevant data in each row to the worksheet it belongs too.


So far here is what I am trying for code:

Sub InputPumpData()
    Dim SrcSht       As Worksheet
    Dim Pump1Sht      As Worksheet
    Dim Pump2Sht      As Worksheet
    Dim Pump3Sht      As Worksheet
    Dim Pump4Sht      As Worksheet
    Dim lngDestLrow1  As Long
    Dim lngDestLrow2  As Long
    Dim lngDestLrow3  As Long
    Dim lngDestLrow4  As Long
    
If MsgBox("Please confirm that you are moving and resetting intentionally?", vbYesNo + vbQuestion, "Move Data?") = vbNo Then Exit Sub


    'Define Worksheets
    Set SrcSht = Sheets("Dashboard")
    If cell <> "" Then Set Pump1Sht = Sheets(SrcSht.Range("B8").Text)
    If cell <> "" Then Set Pump2Sht = Sheets(SrcSht.Range("B9").Text)
    If cell <> "" Then Set Pump3Sht = Sheets(SrcSht.Range("B10").Text)
    If cell <> "" Then Set Pump4Sht = Sheets(SrcSheet.Range("B11").Text)

    'Define Destination Sheet Lrow
    lngDestLrow1 = Pump1Sht.Cells(Columns.Count, "A").End(xlUp).Row
    lngDestLrow2 = Pump2Sht.Cells(Columns.Count, "A").End(xlUp).Row
    lngDestLrow3 = Pump3Sht.Cells(Columns.Count, "A").End(xlUp).Row
    lngDestLrow4 = Pump4Sht.Cells(Columns.Count, "A").End(xlUp).Row

    'Move Data
    Pump1Sht.Cells(lngDestLrow1 + 1, "A") = SrcSht.Range("E6") 'Enter the form Field A on the next available row
    Pump1Sht.Cells(lngDestLrow1 + 1, "B") = SrcSht.Range("C8") 'Enter the form Field B on the next available row
    Pump1Sht.Cells(lngDestLrow1 + 1, "S") = SrcSht.Range("D8") 'Enter the form Field C on the next available row
    Pump1Sht.Cells(lngDestLrow1 + 1, "Q") = SrcSht.Range("E8") 'Enter the form Field D on the next available row
    Pump1Sht.Cells(lngDestLrow1 + 1, "R") = SrcSht.Range("F8") 'Enter the form Field E on the next available row
    Pump1Sht.Cells(lngDestLrow1 + 1, "O") = SrcSht.Range("G8") 'Enter the form Field F on the next available row
    Pump1Sht.Cells(lngDestLrow1 + 1, "P") = SrcSht.Range("H8") 'Enter the form Field G on the next available row
    Pump1Sht.Cells(lngDestLrow1 + 1, "I") = SrcSht.Range("I8") 'Enter the form Field H on the next available row
    Pump1Sht.Cells(lngDestLrow1 + 1, "J") = SrcSht.Range("I8") 'Enter the form Field I on the next available row
    Pump1Sht.Cells(lngDestLrow1 + 1, "K") = SrcSht.Range("I8") 'Enter the form Field J on the next available row
    Pump1Sht.Cells(lngDestLrow1 + 1, "L") = SrcSht.Range("I8") 'Enter the form Field K on the next available row
    Pump1Sht.Cells(lngDestLrow1 + 1, "M") = SrcSht.Range("I8") 'Enter the form Field L on the next available row

The move data code repeats for all 4 entries.

When I run the code I get an error "Object Variable or With block variable not set" on the first attempt to find the last row on the first destination sheet.

I've got a lot of variables at play here, I am not sure if I am going about this in the right method. I am attempting to convert code from a personal sheet I made that was just taking data from one input page and moving the selected cells to an archive worksheet essentially. Adding multiple outputs seems to have me stumped.

Any suggestions or help would be greatly appreciated.

Thank you!

 

[JLGWhiz]

Set SrcSht = Sheets("Dashboard")
    If cell <> "" Then Set Pump1Sht = Sheets(SrcSht.Range("B8").Text)
    If cell <> "" Then Set Pump2Sht = Sheets(SrcSht.Range("B9").Text)
    If cell <> "" Then Set Pump3Sht = Sheets(SrcSht.Range("B10").Text)
    If cell <> "" Then Set Pump4Sht = Sheets([COLOR=#ff0000]SrcSheet[/COLOR].Range("B11").Text)

could be the SrcSheet typo. If not that, check the values in column B to make sure the spelling is correct. The typo still needs to be fixed.