Creating a Macro button/ Sending data/ Reporting Excel 2010

April 17, 2013 at 13:24:52
Specs: Windows 7
I am creating an inventory sheet in excell.

I would like to create a macro button on first spreadsheet. When you click the button, it would would look for values in column A found in column B (which will be hidden).

Example:
Column A Column B
234 234
134 174
123 123
134 133

If the values are present or not present, a summary report would be generated in another sheet that would report the following:

Column A Column B
234 In Stock
134 Not in Stock
123 In Stock
134 Not in Stock

Could you send me instruction to create the macro button which will do the above and create a summary report in excel 2010?



See More: Creating a Macro button/ Sending data/ Reporting Excel 2010

Report •


#1
April 17, 2013 at 14:01:58
I just put something together really quick for you. I haven't tested this, but I have created very similar Macros before. You will need to modify this a little, but for the most part, I think this may do what you want.

Sub FindInventory()
	Dim NewSheet
    Dim DateValue$
    Dim FindString As String
    Dim Rng As Range
	Range("A:B").EntireColumn.Hidden = False
    FindString = InputBox("Please Enter full text of value you want to find", "You Must Enter something!")
        If Trim(FindString) = "" Then
                MsgBox "You did not enter something. Lookup cancled."
                Range("A:B").EntireColumn.Hidden = True
                Exit Sub
        Else
        With Sheets("Sheet1Name").Range("A:A")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
                Else
                MsgBox "No Records Found."
                Range("A:B").EntireColumn.Hidden = True
                Exit Sub
            End If
        End With
    End If
	Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
	NewSheet.Name = "InventoryReport"
    Selection.Copy
    Sheets("InventoryReport").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    
    Sheets("Sheet1Name").Select
    ActiveCell.Offset(0, 1).Select
    Selection.Copy
    Sheets("InventoryReport").Select
    Range("B1").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1Name").Select
    ActiveCell.Offset(0, 1).Select
    Selection.Copy
    Sheets("InventoryReport").Select
    Range("C1").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1Name").Select
    ActiveCell.Offset(0, 1).Select
    Selection.Copy
    Sheets("InventoryReport").Select
    Range("D1").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1Name").Select
    ActiveCell.Offset(0, 1).Select
    Selection.Copy
    Sheets("InventoryReport").Select
    Range("E1").Select
    ActiveSheet.Paste
    
    Sheets("Sheet1Name").Select
    ActiveCell.Offset(0, 1).Select
    Selection.Copy
    Sheets("InventoryReport").Select
    Range("F1").Select
    ActiveSheet.Paste
	Sheets("Sheet1Name").Select
    Range("A:B").EntireColumn.Hidden = True
 End Sub


Report •

#2
April 17, 2013 at 20:42:22
Newbie10:

Rarely, if ever, do you have to Select an object in Excel in order to perform a VBA operation on it. You can almost always perform the operation directly within VBA.

Selecting Sheets and Ranges is extremely inefficient.

I don't know if your code satisfies the requirements of the OP, but in any case, each section of code that looks like this:

  
    Sheets("Sheet1Name").Select
    ActiveCell.Offset(0, 1).Select
    Selection.Copy
    Sheets("InventoryReport").Select
    Range("B1").Select
    ActiveSheet.Paste


Can be replaced with a single instruction similar to this:

    Sheets("Sheet1Name").Range(ActiveCell.Offset(0, 1).Address).Copy _
       Destination:=Sheets("InventoryReport").Range("B1")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#3
April 18, 2013 at 07:13:39
Sweet! Thanks DerbyDad03! As you can probably tell, I'm still learning Excel VBA too... I'll take any advice I can get!

Report •

Related Solutions

#4
Report •

#5
April 23, 2013 at 16:57:13
Ok this didn't work well, I am importing values into column A and want the program to to look in column B and create a report in sheet two when I press the macro button.

Report •

#6
April 24, 2013 at 07:32:26
I'm modifying my code and have some questions...

How do I know if an item is 'In Stock' or 'Not in Stock'? You only refer to Column A and a Column B. Is there a Column C that shows # of items in stock?

Could you please define what each column is so I can search the correct column and report on the correct column?


Report •

#7
April 24, 2013 at 13:32:50
cctalk, I'm going to post what I think you want so you can try it since I haven't heard back from you yet. Give it a shot and let me know. I made tons of changes after re-reading your op several times. You may need to modify it some as I don't know what determines if an item is in stock or not in stock. Right now, if Column B does not have a value, the script assumes it is out of stock.

Sub CreateInvRpt()
    Dim NewSheet
    Dim FindString As String
    Dim Rng As Range
    Dim i As Long
    DelInvRptSht
    
    Range("A:B").EntireColumn.Hidden = False
    Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
    NewSheet.Name = "InventoryReport"
    ActiveCell = "Column A"
    Cells(1, 2).Select
    ActiveCell = "Status"
    Sheets("Inventory").Select
    Range("A1").Select
    
        i = 2
        With ActiveSheet
            Do While i <= .Rows.Count
                If .Cells(i, 1) <> "" Then
                    Cells(i, 1).Select
                        Selection.Copy
                        Sheets("InventoryReport").Select
                        Cells(i, 2).Select
                        
                            If Sheets("Inventory").Cells(i, 2) <> "" Then
                            ActiveCell = "In Stock"
                            Else
                            ActiveCell = "Not in Stock"
                            End If
                        
                        Cells(i, 1).Select
                        ActiveSheet.Paste
                    Sheets("Inventory").Select
                Else
                    Exit Do
                End If
                i = i + 1
            Loop
        End With
    
    Sheets("Inventory").Select
    Range("A:B").EntireColumn.Hidden = True
 End Sub
 
 Sub DelInvRptSht()
    Application.DisplayAlerts = False
    On Error Resume Next
    Sheets("InventoryReport").Delete
    Application.DisplayAlerts = True
    On Error GoTo 0
End Sub


Report •


Ask Question