Results 1 to 2 of 2

Thread: Moving Data from 1 Source Worksheet across multiple worksheets in the same workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,031
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

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

    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(SrcSheet.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.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •