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.

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


And below is the answer worksheet.

[TABLE="class: cms_table, width: 777"]
<tbody>[TR]
[TD]Q1[/TD]
[TD]Put your answer in this cell[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Emp ID[/TD]
[TD]Last Name[/TD]
[TD]First Name[/TD]
[TD]Department[/TD]
[TD]Division[/TD]
[TD]Salary[/TD]
[TD]Start Date[/TD]
[TD]Birth Date[/TD]
[TD]Age[/TD]
[TD]Years In Service[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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.