Need to produce a couple workbooks..

[MrEbzz]

Workbook 1- being a user input sheet. For example.

Column names
Date // Time // Name // Email // External Status // Internal Status


Date - needs to automatically input the date and time it’s being inputted into the user table..

External status - needs to highlight the row depending on option selected ( 1 of 2 options needed, Approved, Rejected) I.e Approved highlights that row green. Rejected highlights it red.

Internal status - just needs to select from one of 2 options like the above and be change the row colour dependant on selection.


Workbook 2

This is just for reporting automation. For example

Data gets exported to an excel document, I then need to do the following with this data :

1. Remove all/any rows with a ‘name’ beginning with ‘MI’
2. CHANGE all words = ‘FALSE’ to ‘0’ & TRUE = to ‘1’
3. Separate data based on filter function (
Example — created tickets need to be stored to a ‘created tickets on “date”’ tab.
Closed tickets need to be stored to a ‘closed tickets on”date”’ tab.)
4. Format header text columns to bold on all 3 tabs.
5. Outline all data on all 3 tabs
6. Remove grid lines on all 3 tabs

 

[awoohaw]

Welcome to the forum.
What have you built so far? Are you asking to have someone to build this for you from scratch? Mr. Excel has consulting service referrals:

Consulting Services

Are you stuck doing a tedious manual process day after day? This can be automated using VBA macros behind your Excel worksheets. For over twenty years, these Excel consultants work with customers via e-mail or telephone to build a solution.

www.mrexcel.com

 

[MrEbzz]

Welcome to the forum.
What have you built so far? Are you asking to have someone to build this for you from scratch? Mr. Excel has consulting service referrals:

Consulting Services

Are you stuck doing a tedious manual process day after day? This can be automated using VBA macros behind your Excel worksheets. For over twenty years, these Excel consultants work with customers via e-mail or telephone to build a solution.

www.mrexcel.com

Hey,

Thanks for your response.

I’m able to create all tables, workbooks, etc myself however I just wanted others opinions on best practices/methods.

I am able to create the user sheet accurately.. however this is using drop down lists which flag an error in tables when using this?

The report workbook I need some advise on which macros/ VBA to use

 

[awoohaw]

1. Remove all/any rows with a ‘name’ beginning with ‘MI’
Turn on autofilter, go to the name column, click the drop down, select "Begins with", type "mi" (this is not a case sensitive search), click OK, select all the rows, right click delete rows (or select delete rows from the ribbon)

2. CHANGE all words = ‘FALSE’ to ‘0’ & TRUE = to ‘1’
CNTL-H, type False in search for, 0 in replace with, click OK
CNTL-H, type TRUE in search for, 1 in replace with, click OK

3. Separate data based on filter function (
Example — created tickets need to be stored to a ‘created tickets on “date”’ tab.
Closed tickets need to be stored to a ‘closed tickets on”date”’ tab.)
A number of ways to to this.
one is to autofilter, filter by the column you're concerned with
(Assuming you already have headers on each worksheet)
Select all rows that match your filter criteria
CUT, then paste into a new worksheet

4. Format header text columns to bold on all 3 tabs.
Group your sheets (hold cntl or shft key down and select the sheets you want to have headers bold.)
Click the cells (or row) of sheet you want bold, from home tab, click BOLD icon.
right click a tab at bottom, select Ungroup Sheets.

5. Outline all data on all 3 tabs
Unsure what you mean by outline.

6. Remove grid lines on all 3 tabs
Group your sheets (hold cntl or shft key down and select the sheets you want to have no gridlines)
Select the VIEW Ribbon "show" section, unlick Gridlines.

 

[MrEbzz]

1. Remove all/any rows with a ‘name’ beginning with ‘MI’
Turn on autofilter, go to the name column, click the drop down, select "Begins with", type "mi" (this is not a case sensitive search), click OK, select all the rows, right click delete rows (or select delete rows from the ribbon)

2. CHANGE all words = ‘FALSE’ to ‘0’ & TRUE = to ‘1’
CNTL-H, type False in search for, 0 in replace with, click OK
CNTL-H, type TRUE in search for, 1 in replace with, click OK

3. Separate data based on filter function (
Example — created tickets need to be stored to a ‘created tickets on “date”’ tab.
Closed tickets need to be stored to a ‘closed tickets on”date”’ tab.)
A number of ways to to this.
one is to autofilter, filter by the column you're concerned with
(Assuming you already have headers on each worksheet)
Select all rows that match your filter criteria
CUT, then paste into a new worksheet

4. Format header text columns to bold on all 3 tabs.
Group your sheets (hold cntl or shft key down and select the sheets you want to have headers bold.)
Click the cells (or row) of sheet you want bold, from home tab, click BOLD icon.
right click a tab at bottom, select Ungroup Sheets.

5. Outline all data on all 3 tabs
Unsure what you mean by outline.

6. Remove grid lines on all 3 tabs
Group your sheets (hold cntl or shft key down and select the sheets you want to have no gridlines)
Select the VIEW Ribbon "show" section, unlick Gridlines.

Thanks again for your detailed response.

It’s not so much being able to do it manually, I have no issues there. It’s just a repetitive task where the processes never change. Therefore I wanted to actually actually this.

I have attempted to do a macro, but there is only so much it allows.

I wonder if VBA is the solution to my automation issues:

And sorry, just to clarify when I said “outline all data on all 3 tabs” I actually meant allocate the data to each tab correctly for example:

Tab1 (date created ) - date created data.
Tab2 (all open) - all open data.
Tab3 (date closed) - date closed data.

Once again *Automation is the primary goal*

Many Thanks
Mr.E

 

[awoohaw]

You can record macros to do nearly all of that.
You may need to tweak them some. For instance to delete the rows with MI in the name:
Change this recorded macro:

Sub AutoFilter_MI()

' Autofilter_MI Macro



    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$6").AutoFilter Field:=1, Criteria1:="=Mi*", _
        Operator:=xlAnd
   Rows("4:5").Select
    Selection.Delete Shift:=xlUp
    ActiveSheet.Range("$A$1:$C$4").AutoFilter Field:=1
End Sub

to:
deleted extraneous part of the autofilter step (not really needed)
The only thing you need to really change to make the macro functional in various situations is to
change the delete specific rows to deleting the rows of the current region (the filtered data excluding the header)
and turned autofilter off (by removing the Field requirement).

Sub AutoFilter_MI()

' Autofilter_MI Macro

    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$6").AutoFilter Field:=1, Criteria1:="=Mi*"
    Range("A1").CurrentRegion.Offset(1).EntireRow.Delete
    ActiveSheet.Range("$A$1:$C$4").AutoFilter

End Sub

You can also use the autofilter in macros to select the data you want to copy from one sheet to another. Record a macro of copy and paste of filtered data into another worksheet, then update it accordingly.