Solved

populating an inventory style excel spreadsheet with another excel form

Posted on 2011-03-16
13
416 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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 how to use longer labels with horizontal bar charts instead of the vertical column chart.

679 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