Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Need a little help with a Macro


(!)

BFarmac's Avatar
BFarmac BFarmac is offline
Member with 3 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
13-Apr-2012, 02:24 PM #1
Need a little help with a Macro
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.
etaf's Avatar
etaf   (Wayne) etaf is offline
Computer Specs
Moderator with 52,952 posts.
 
Join Date: Oct 2003
Location: Surrey, UK
13-Apr-2012, 05:16 PM #2
moved to business applications forum - where you will get more answers on excel
Rollin_Again's Avatar
Member with 4,699 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
13-Apr-2012, 08:24 PM #3
Can you post a sample workbook showing the data layout?

Rollin
BFarmac's Avatar
BFarmac BFarmac is offline
Member with 3 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
16-Apr-2012, 07:57 AM #4
Sample Log
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
File Type: xlsx Sample Log.xlsx (115.5 KB, 34 views)
Rollin_Again's Avatar
Member with 4,699 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
16-Apr-2012, 06:16 PM #5
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
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
17-Apr-2012, 02:52 AM #6
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.
Rollin_Again's Avatar
Member with 4,699 posts.
 
Join Date: Sep 2003
Location: Atlanta, GA - Planet Earth
Experience: Advanced
17-Apr-2012, 08:45 AM #7
Quote:
Originally Posted by Garf13LD View Post
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.
This won't work unless the data validation values in cell D7 matches the sheet name exactly which is doesn't.

Rollin
BFarmac's Avatar
BFarmac BFarmac is offline
Member with 3 posts.
THREAD STARTER
 
Join Date: Apr 2012
Experience: Intermediate
17-Apr-2012, 09:02 AM #8
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
Garf13LD's Avatar
Garf13LD Garf13LD is offline
Member with 455 posts.
 
Join Date: Apr 2012
Experience: Intermediate
18-Apr-2012, 05:36 AM #9
Quote:
Originally Posted by Rollin_Again View Post
This won't work unless the data validation values in cell D7 matches the sheet name exactly which is doesn't.

Rollin
Well, either u name the sheet exactly as the part code or u go the long way of select case.
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑