Recording a macro

[Bmaho]

I have a project I am working on for my class and I am having trouble with this question. Below I am posting the actual question, the worksheet with the database, and the worksheet where I will be placing the macro button that will bring up the input box for the user to input information. What I am trying to do first is record a macro beginning in the answer worksheet, because that's where my answer needs to go, that gets the percentage of employees from the accounting department that makes more than 45,000. Then I think I can replace "accounting" with input box. As you can see below, the answer worksheet has a cell for the answer, and the criteria for the question is the headings copy/pasted from the database. the database begins with Emp ID in A7 and the column for years in service goes to J61.

Create a macro, Q_1, which will ask the user for the department of an employee and then calculate percentage of employees from that department who are earning above $45,000 among all employees in the database? Set up criteria on Queries worksheet, and answer should be on Queries sheet in the cell indicated. Create two macro buttons, one each on Database and Queries sheet. Macro should be self-sufficient including first clearing the criteria range.

The paragraph above is the question, and below is the database wordksheet.

Emp ID	Last Name	First Name	Department	Division	Salary	Start Date	Birth Date	Age	Years In Service
1011	Gorton	Hazel	Accounting	Copier	$27,597.85	2/3/2000	11/21/1964	53	18
1012	Preston	Liza	Engineering	Printer	$43,394.15	1/26/2006	12/2/1984	33	12
1041	Tercan	Robert	R and D	Printer	$28,043.68	4/16/2002	9/21/1965	52	15
1054	Smith	Howard	Art	Copier	$25,176.06	4/16/2009	8/9/1987	30	8
1055	Albert	Maxine	Marketing	Copier	$26,040.56	4/8/1999	8/20/1967	50	19
1075	Kane	Sheryl	Art	Printer	$23,239.44	8/7/2006	8/28/1979	38	11
1152	Henders	Mark	Accounting	Printer	$26,646.20	1/21/2000	10/23/1971	46	18
1153	Plant	Allen	Accounting	Printer	$28,043.68	1/13/2009	11/3/1963	54	9
1167	Berwick	Sam	Marketing	Copier	$31,913.88	4/18/2001	9/28/1970	47	16

<tbody>
</tbody>

And below is the answer worksheet.

Q1	Put your answer in this cell
Emp ID	Last Name	First Name	Department	Division	Salary	Start Date	Birth Date	Age	Years In Service

<tbody>
</tbody>

 

[Logit]

.

Option Explicit


Sub viscell()
Dim VisibleCells As Range


Set VisibleCells = Range("A1:J100" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
Application.ScreenUpdating = False
VisibleCells.Copy
    With Worksheets("Sheet2").Range("A3")
        .PasteSpecial xlPasteColumnWidths
        .PasteSpecial xlPasteValues, , False, False
        .PasteSpecial xlPasteFormats, , False, False
    End With
   
    With Worksheets("Sheet2")
        .Range("A1").Value = "Q1"
        .Range("B1").Value = "Put your answer in D1"
     End With
     
    Application.CutCopyMode = False
    Sheets("Sheet2").Select
    Sheets("Sheet2").Range("D1").Select
Application.ScreenUpdating = True
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/Prlt5BKksWSc7D4XbyBKJS0xwblviwTneLvDV18qQbp

 

[Bmaho]

Thank you for your help. I actually ended up figuring it out after some trial and error.

 

[Logit]

.
Sorry I couldn't be of assistance.

Cheers.

 

[Bmaho]

.
Sorry I couldn't be of assistance.

Cheers.

Don't be sorry! Your input is helpful because it seems as though there is always several ways to get a solution in Excel. I like to see the various ways people know, and figure out why those solutions work. So thank you for your input.