macro to auto update with new worksheet name

shaz0503

New Member
Joined
Oct 2, 2017
Messages
8
Hi all

I have a workbook that i have developed that reports the number of document reviews due - either over due or future due..

A) within the workbook i have a macro (borrowed from the web) that creates a new worksheet for each business unit and the current reporting month (From a set range). works perfectly see below

Sub CreateSheetsformonth()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Formatting").Range("i2:i19")


For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub


B) I then have a macro that goes to a master sheet and copies the values related to 'Unit 1" for example....worksheet is titled 'ARP To 31 March 2017' for example

my issue is that while my A) is updated within the range on a monthly basis, how can i auto update the worksheet name B) within the macro

hard to explain sorry the below code is where i need to be able to auto update the worksheet name


Sub Month_Updates_ARP()
'
' Month_Updates Macro
'

'
Sheets("Quarter").Select
Range("A1:e1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$e$500").AutoFilter Field:=4, Criteria1:="ARP"
Range("$A$1:$e$500").Select
Selection.Copy
Sheets("ARP To 31 March 2017").Select
Range("A1").Select
ActiveSheet.Paste
Columns("A:E").Select
Selection.ColumnWidth = 52.73
Application.CutCopyMode = False
Sheets("Formatting").Select
End Sub


there is a total of 50 worksheets created and need updating and i don't want to have to manually do thiws within code...

any ideas appreciated

rgds Shaz
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Probably very few people, if any, will take the time to find a solution if you don't take the time to put code tags (google on how to) around your code in Post #1
 
Upvote 0
All

Apologies

Please see below code with tags

[Sub CreateSheetsformonth()
Dim MyCell As Range, MyRange As Range

Set MyRange = Sheets("Formatting").Range("i2:i19")


For Each MyCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
Next MyCell
End Sub][/CODE]

and

Code:
Sub Month_Updates_ARP()
'
' Month_Updates Macro
'

'
    Sheets("Quarter").Select
    Range("A1:e1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$e$500").AutoFilter Field:=4, Criteria1:="ARP"
    Range("$A$1:$e$500").Select
    Selection.Copy
    Sheets("ARP To 31 March 2017").Select
    Range("A1").Select
    ActiveSheet.Paste
    Columns("A:E").Select
    Selection.ColumnWidth = 52.73
    Application.CutCopyMode = False
    Sheets("Formatting").Select
End Sub

hoping i posted this ok...

to simply explain my issue - i have a worksheet that contains all documents due
 
Upvote 0
...sorry - something amiss - accidentally posted

ill try again

Code:
Sub CreateSheetsformonth()
    Dim MyCell As Range, MyRange As Range
     
    Set MyRange = Sheets("Formatting").Range("i2:i19")
    

    For Each MyCell In MyRange
        Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
        Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    Next MyCell
End Sub

AND


Code:
Sub Month_Updates_ARP()
'
' Month_Updates Macro
'

'
    Sheets("Quarter").Select
    Range("A1:e1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$e$500").AutoFilter Field:=4, Criteria1:="ARP"
    Range("$A$1:$e$500").Select
    Selection.Copy
    Sheets("ARP To 31 March 2017").Select
    Range("A1").Select
    ActiveSheet.Paste
    Columns("A:E").Select
    Selection.ColumnWidth = 52.73
    Application.CutCopyMode = False
    Sheets("Formatting").Select
End Sub

as i mentioned - i have a worksheet that contain a list of documents for review..

I need to create a new sheet for each business unit based on a list in Format sheet range I2:I9 that includes the date of the current quarter-- the above code works fine...

my issue is when i then want to select the information from sheet Quarter that matches Format sheet range I2:I9 and paste in the newly created worksheet - the above code to copy the information is based on searching for the Business Unit code whereas the pasting is based on the Business Unit code and date --two differing ranges...

This report is to be run quarterly...

this is very difficult to put in words

again apologies and any assistance appreciated

Shaz
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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