Macro / VBA Help - Different tasks in different workbooks with results in new book

iandb

New Member
Joined
Feb 16, 2009
Messages
34
Office Version
  1. 365
Platform
  1. Windows
Hi All,

Looking for some major help if what I hope to do is even possible.

We run and export to excel two different reports from our business system. and then manually make changes (delete columns; Clear Data from cells; filter; copy and move some data across various sheets ) in all about 250 steps and quite time consuming. Hoping to have a master file hosting a macro / VBA to automate this
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Hi,
Yes, I think it is possible to automate everything except the part of downloading the reports.

Are the steps always the same? Or are there any exceptions?
 
Upvote 0
Hi,
Yes, I think it is possible to automate everything except the part of downloading the reports.

Are the steps always the same? Or are there any exceptions?
Thanks for the quick reply. Yes, it is the same process daily as far as I know and steps repeated on different sheets for different divisions. Have just taken this on from someone else following a "Re-alignment"

The below is the start of the process

  • Click on the ‘Sheet1 tab along the bottom.
  • Click on the top left cell of the spreadsheet.

  • This will highlight the whole spreadsheet.
  • Along the top once it is all highlighted, go to the Data tab. Remove the filters by clicking on the filter icon.

  • Once this is done, go to the Home tab and click on the little arrow next to the paint pot icon and select ‘no fill’
  • Highlight columns A to Q.
  • Right click and select clear contents *NOTE – DO NOT DELETE THE COLUMNS, JUST CLEAR*
Repeat for Sheets 2 and 3

  • Then open sheet 4 tab.
  • Highlight columns A to O
  • Right click and select clear contents *NOTE – DO NOT DELETE THE COLUMNS, JUST CLEAR*
  • Keep the two headers in columns P&Q (P1 & Q1). Just clear the cells below.

  • Repeat for sheet 5

  • Then go to Sheet 6
  • Highlight columns A to L.
  • Right click and select clear contents *NOTE – DO NOT DELETE THE COLUMNS, JUST CLEAR*
  • Repeat for Sheet 7

  • SAVE THE REPORT AS TOMORROWS DATE.
 
Upvote 0
One file is called Sales and the sheets are just numbered 1 - 6
They are like this "Sheet1" , "Sheet2" , "Sheet3" , etc. ?

The second file Service only have Sheet1 ?

What are the steps for file Service ?
 
Upvote 0
They are like this "Sheet1" , "Sheet2" , "Sheet3" , etc. ?

The second file Service only have Sheet1 ?

What are the steps for file Service ?
Hi yes, The sheets on the Sales file are labelled as Sheet1 etc

The steps for the service are not so important at the moment, but they are very similar, hoping if I have a working example then I can transfer / figure it out

Thanks
 
Upvote 0
So I think this will work for the "Sales" file.

Create a new workbook, only for the macro then go to VBE and add this macro:

VBA Code:
Sub macro_for_MrExcel()
Dim i As Long
Dim s As String

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With

'Put the path name here
Workbooks.Open "C:\User\folder\sales.xls"

For i = 1 To 3
    Sheets("Sheet" & i).Select
    Cells.Select
    On Error Resume Next
    Selection.AutoFilter
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A:A,Q:Q").Select
    Selection.ClearContents
Next i

For i = 4 To 5
    Sheets("Sheet" & i).Select
    Range("A:A,O:O").ClearContents
    Range("P2:Q1048576").ClearContents
Next i

For i = 6 To 7
    Sheets("Sheet" & i).Select
    Columns("A:L").ClearContents
Next i

s = Format(DateAdd("d", 1, Now()), "mm_dd_yyyy") & ".xlsm"
ActiveWorkbook.SaveAs Filename:=s

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Then you just need to run the macro whit F5
You can also add a button to the workbook and assign the macro.
 
Upvote 0
Many thanks, I've been given wrong information from the person who did this before, sheets are named differently.

Sheet 1 is called BUV Service Orders
Sheet 2 is called BUV Open Sales Orders
Sheet 3 is called BUK Open Sales Lines

I assume I remove the green note "Put path name here"

A one click button would be great!
 
Upvote 0
Here:

The code updated:
VBA Code:
Sub macro_for_MrExcel()
Dim i As Long
Dim s As String

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
    .Calculation = xlCalculationManual
End With


Workbooks.Open "Put the path name here"

For i = 1 To 3
    Worksheets(i).Select
    Cells.Select
    On Error Resume Next
    Selection.AutoFilter
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A:A,Q:Q").Select
    Selection.ClearContents
Next i

For i = 4 To 5
    Worksheets(i).Select
    Range("A:A,O:O").ClearContents
    Range("P2:Q1048576").ClearContents
Next i

For i = 6 To 7
    Worksheets(i).Select
    Columns("A:L").ClearContents
Next i

s = Format(DateAdd("d", 1, Now()), "mm_dd_yyyy") & ".xlsm"
ActiveWorkbook.SaveAs Filename:=s

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
    .Calculation = xlCalculationAutomatic
End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top