macro to auto update with new worksheet name

[shaz0503]

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

 

[jolivanes]

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

 

[shaz0503]

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

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

 

[shaz0503]

...sorry - something amiss - accidentally posted

ill try again

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

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