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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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?
 

iandb

New Member
Joined
Feb 16, 2009
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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.
 

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
What's the name of the workbooks?
And the name of the Sheets?
 

iandb

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

ADVERTISEMENT

What's the name of the workbooks?
And the name of the Sheets?
One file is called Sales and the sheets are just numbered 1 - 6
Other file is called Service with sheet 1
 

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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 ?
 

iandb

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

ADVERTISEMENT

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
 

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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.
 

iandb

New Member
Joined
Feb 16, 2009
Messages
34
Office Version
  1. 365
Platform
  1. Windows
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!
 

afonsomira

Board Regular
Joined
Aug 11, 2020
Messages
62
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,704
Messages
5,707,970
Members
421,539
Latest member
zuniBM

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
Top