1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Need a little help with a Macro

Discussion in 'Business Applications' started by BFarmac, Apr 13, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. BFarmac

    BFarmac Thread Starter

    Joined:
    Apr 13, 2012
    Messages:
    3
    I have a spreadsheet that that has several sheets (around 10) on it. The first sheet is a Data entry sheet and it puts the information onto the second sheet. My problem is I need it to put the information entered onto a particular sheet depending on what is entered into the first box. Ex. if A is entered I need all the information to go to the sheet marked A, if B is entered then I need all the information to go to sheet B.
    Thanks for any help you can provide. ​
     
  2. etaf

    etaf Wayne Moderator

    Joined:
    Oct 2, 2003
    Messages:
    55,950
    moved to business applications forum - where you will get more answers on excel
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Can you post a sample workbook showing the data layout?

    Rollin
     
  4. BFarmac

    BFarmac Thread Starter

    Joined:
    Apr 13, 2012
    Messages:
    3
    I have cut down the workbook to display the input data screen and two of the tabs that I need the data to go to. What I am trying to do is have the data go to the correct tab based on the Solutions space (D7). I have a macro on the original document that moves the data when you click subit but it only sends it to the first tab. I am not opposeed to sending all the data to one page but I would still need help getting the information onto the other tabs.

    Any help you can provide will be greatly appreciated.
     

    Attached Files:

  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    Your button click event or macro should have a select statement that evaluates the value in cell D7 and takes the appropriate action. Here is a simple example.

    Code:
    Sub TransferData()
    
    vSolution = Sheets("Input").Range("D7").Value
    
    Select Case vSolution
    
    Case "TBAH(0.1N)"
    
       Sheets("TBAH 0.1N").Range("D5").Value = Sheets("Input").Range("D7").Value
    
    
    Case "HCL04(0.1N)"
    
        Sheets("HCL04 0.1N").Range("D5").Value = Sheets("Input").Range("D7").Value
        
        
    End Select
    
    End Sub
    
    
    Regards,
    Rollin
     
  6. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Dim txt As String
    txt = Range("D7")
    Sheets(txt).Range("D5") = Sheets("Input").Range("D5")


    Do have error handling in case Sheet is not present.
     
  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,732
    This won't work unless the data validation values in cell D7 matches the sheet name exactly which is doesn't.

    Rollin
     
  8. BFarmac

    BFarmac Thread Starter

    Joined:
    Apr 13, 2012
    Messages:
    3
    I have tried to fit the code you supplied into my sheet and it didnt work. This is the code that i have on the workbook already. can you take a look and see where I am going wrong. This is the one that works but only send to the first sheet.
    Code:
    Sub UpdateLogWorksheet()
        Dim historyWks As Worksheet
        Dim inputWks As Worksheet
        Dim nextRow As Long
        Dim oCol As Long
        Dim myRng As Range
        Dim myCopy As String
        Dim myCell As Range
        
        'cells to copy from Input sheet - some contain formulas
        myCopy = "D5,D7,D9,D11,D13,D15,D17,D19,D21,D23,D25,D27,D29,D31,D33,G5"
        Set inputWks = Worksheets("Input")
        Set historyWks = Worksheets("TBAH 0.1N")
        With historyWks
            nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
        End With
        With inputWks
            Set myRng = .Range(myCopy)
            If Application.CountA(myRng) <> myRng.Cells.Count Then
                MsgBox "Please fill in all the cells!"
                Exit Sub
            End If
        End With
        With historyWks
            With .Cells(nextRow, "A")
                .Value = Now
                .NumberFormat = "mm/dd/yyyy"
            End With
            .Cells(nextRow, "AF").Value = Application.UserName
            oCol = 2
            For Each myCell In myRng.Cells
                historyWks.Cells(nextRow, oCol).Value = myCell.Value
                oCol = oCol + 1
            Next myCell
        End With
        
        'clear input cells that contain constants
        With inputWks
          On Error Resume Next
             With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
                  .ClearContents
                  Application.GoTo .Cells(1) ', Scroll:=True
             End With
          On Error GoTo 0
        End With
    End Sub
    
     
  9. Garf13LD

    Garf13LD

    Joined:
    Apr 17, 2012
    Messages:
    455
    Well, either u name the sheet exactly as the part code or u go the long way of select case.
     
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1049199