Solved

populating an inventory style excel spreadsheet with another excel form

Posted on 2011-03-16
13
412 Views
Last Modified: 2012-05-11
I would like to populate an inventory spreadsheet with the contents of another excel worksheet.  Idea being that each time the worksheet is filled out, say after hitting an enter or print button, it would create a new row of corresponding data to the inventory spreadsheet.

If anyone has a simple/no code solution.  it would be greatly appreciated.

0
Comment
Question by:barelysane
  • 8
  • 5
13 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35153753
>>>>dea being that each time the worksheet is filled out, say after hitting an enter or print button, it would create a new row of corresponding data to the inventory spreadsheet.

barelysane: The above shouldn't be a problem. May I see a sample file so that I get my references right?

Sid
0
 

Author Comment

by:barelysane
ID: 35154019
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35154081
Quick question. For example for "Task 1" can both values in the check box remain empty or one needs to be ticked when updating the other sheet?

Sid
0
 

Author Comment

by:barelysane
ID: 35154087
it can be empty
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35154089
Ok. I am almost done.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35154092
Wait. One last question. :) What if both are ticked for task 1? Which value should I pickup?

Sid
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:barelysane
ID: 35154103
it should only be yes,no, or neither, not both.  thanks
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35154105
Ok. Gimme 10 mins

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 250 total points
ID: 35154159
Sample file attached. Do remember to change the output files name and path in the code below. I have documented the code so that it is easier to understand.

Sid

Code Used

Dim i As Long, LastRow As Long
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim fFile As String

Private Sub CommandButton1_Click()
    Set wb1 = ActiveWorkbook
    Set ws1 = wb1.Sheets("Sheet1")
    
    '~~> Check if Y/N is not ticked together
    If ValidateCB("Check Box 2", "Check Box 3") = False Or _
    ValidateCB("Check Box 12", "Check Box 13") = False Or _
    ValidateCB("Check Box 14", "Check Box 15") = False Or _
    ValidateCB("Check Box 16", "Check Box 17") = False Or _
    ValidateCB("Check Box 18", "Check Box 19") = False Or _
    ValidateCB("Check Box 20", "Check Box 21") = False Or _
    ValidateCB("Check Box 22", "Check Box 23") = False Or _
    ValidateCB("Check Box 24", "Check Box 25") = False Or _
    ValidateCB("Check Box 26", "Check Box 27") = False Then
        MsgBox "Please select only Y or N"
        Exit Sub
    End If
    
    '~~> Check if location is not empty
    If Len(Trim(ws1.Range("B3").Value)) = 0 Then
        MsgBox "Location cannot be empty"
        Exit Sub
    End If
    
    '~~> Changes the path here
    fFile = "C:\Sample-Worksheet-to-populate.xlsx"
    
    '~~> Open the workbook to populate
    Set wb2 = Workbooks.Open(fFile)
    Set ws2 = wb2.Sheets("Sheet1")
    
    '~~> get the lastrow to be populated
    LastRow = ws2.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    '~~> Store location
    ws2.Range("A" & LastRow).Value = ws1.Range("B3").Value
    
    '~~> Get the Y/N values
    If ws1.CheckBoxes("Check Box 2").Value = 1 Then
        ws2.Range("B" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 3").Value = 1 Then
        ws2.Range("B" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 12").Value = 1 Then
        ws2.Range("C" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 13").Value = 1 Then
        ws2.Range("C" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 14").Value = 1 Then
        ws2.Range("D" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 15").Value = 1 Then
        ws2.Range("D" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 16").Value = 1 Then
        ws2.Range("E" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 17").Value = 1 Then
        ws2.Range("E" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 18").Value = 1 Then
        ws2.Range("F" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 19").Value = 1 Then
        ws2.Range("F" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 20").Value = 1 Then
        ws2.Range("G" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 21").Value = 1 Then
        ws2.Range("G" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 22").Value = 1 Then
        ws2.Range("H" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 23").Value = 1 Then
        ws2.Range("H" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 24").Value = 1 Then
        ws2.Range("I" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 25").Value = 1 Then
        ws2.Range("I" & LastRow).Value = "N"
    End If
    If ws1.CheckBoxes("Check Box 26").Value = 1 Then
        ws2.Range("J" & LastRow).Value = "Y"
    ElseIf ws1.CheckBoxes("Check Box 27").Value = 1 Then
        ws2.Range("J" & LastRow).Value = "N"
    End If
    
    '~~> Save and close the workbook
    wb2.Close savechanges:=True
    
    '~~> inform user that data is save successfully
    MsgBox "Data Saved"
    
    '~~> Clear data for next input
    ws1.Range("B3").ClearContents
    For i = 12 To 27
        ws1.CheckBoxes("Check Box " & i).Value = -4146
    Next
    ws1.CheckBoxes("Check Box 2").Value = -4146
    ws1.CheckBoxes("Check Box 3").Value = -4146
End Sub

Function ValidateCB(CB1 As String, CB2 As String) As Boolean
    ValidateCB = True
    If ws1.CheckBoxes(CB1).Value = 1 And _
    ws1.CheckBoxes(CB2).Value = 1 Then
        ValidateCB = False
    End If
End Function

Open in new window

Sample-Form.xlsm
0
 

Author Comment

by:barelysane
ID: 35158561
Sid,

Looks very promising.  Issue with the output though, see attached.  

Thanks!
Sample-Worksheet-to-populate.xlsx
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35158674
Did you change anything in the code because it is working absolutely fine here.

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35158700
Output Sample attached

Sid
Sample-Worksheet-to-populate.xlsx
0
 

Author Comment

by:barelysane
ID: 35158913
Got it.  thanks for your help!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Generating a graph via Excel 3 28
Excel Copy Macro down foe each row 4 21
Excel 2010 - Populate a formula in multiple cells 6 27
Help Updated Qtr 2 11
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now