Solved

populating an inventory style excel spreadsheet with another excel form

Posted on 2011-03-16
13
411 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
Comment Utility
>>>>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
Comment Utility
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
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
Comment Utility
it can be empty
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Ok. I am almost done.

Sid
0
 
LVL 30

Expert Comment

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

Sid
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

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

Expert Comment

by:SiddharthRout
Comment Utility
Ok. Gimme 10 mins

Sid
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 250 total points
Comment Utility
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
Comment Utility
Sid,

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

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

Expert Comment

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

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
Comment Utility
Output Sample attached

Sid
Sample-Worksheet-to-populate.xlsx
0
 

Author Comment

by:barelysane
Comment Utility
Got it.  thanks for your help!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

771 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

12 Experts available now in Live!

Get 1:1 Help Now