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

envious22

New Member
Joined
May 7, 2018
Messages
4
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:
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!
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,524
Messages
6,114,117
Members
448,549
Latest member
brianhfield

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top