link cells from sheets that are non existent..

[omairhe]

Hello,

I am linking cells from a workbook named new stock 2018 .
the said workbook will have 12 sheets by the end of the year for each month from january to december . but it currently only have 5 (january to may.) I have linked all 12 months by making dummy sheets of june til december with no data inside of them. these dummy sheets will later be deleted. and when the next time the 2 workbooks are opened side by side the links are jeopardized for june til december.

What possibly I can do to achieve my goal here?

Thank u for your patience..

 

[EdwardsBueno]

Hello, are you able to provide a screenshot for it?

 

[njimack]

Don't delete the dummy sheets - leave them empty and then paste data into them when available

 

[omairhe]

Hello Edwards.
thank u. the screenshot is not available just now but can post lomorrow.

 

[omairhe]

Hey njimack,
i wish but the real data sheets are created once a command is given through a VBA script. hence it is an aotomatic process and the sheet is then renamed into month's name manually afterwards. for instance "June 2018"

 

[omairhe]

What if i create a hidden sheet inside the aforementioned workbook "new stock 2018"
from here on I put a formula in cell A1 which will output number of sheets available in the workbook...for jan to may
cell A1 = 5

now in other workbook say i put a condition. If cell A1 =5 only then formula may execute the link else do nothing. similarly for june if cell A1 = 6 then perform link else do not.

will it work like this?

Thank you for yr help.

 

[njimack]

So amend the existing VBA - you only need to add a single line of code to delete the sheet before creating a new one

 

[omairhe]

###So amend the existing VBA - you only need to add a single line of code to delete the sheet before creating a new one###

ok so if I have a dummy sheet named "June 2018" the code will delete this dummy sheet and create a new sheet by the same name "June 2018" with real data. this also means the code should check for last non dummy sheet which is "May 2018" and what comes nexr is June so it will delete the dummy June 2018 and create non dummy June 2018.

Instead of deleting could it not simply put the data in June 2018? and how will code know that Jume comes after May. and how it knows that May is not a dummy sheet.?

 

[njimack]

That was my first suggestion, to populate the existing sheets. To determine which sheet to use, the code would be something like :
Sheets(Format(Date, "mmmm yyyy"))

 

[omairhe]

That was my first suggestion, to populate the existing sheets. To determine which sheet to use, the code would be something like :
Sheets(Format(Date, "mmmm yyyy"))

I am posting the vba script. I will program the workbook to comprise of only 12 sheets.
January 2018, February 2018, March 2018, April 2018, May 2018, June 2018, July 2018, August 2018, September 2018, October 2018, November 2018 and December 2018 respectively. Currently January to May are having some data and June to December are all empty sheets. I would like the followng VBA script to run on next empty sheet which in this case is June 2018. Can this be done please?

the code is

Sub Macro1()'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+k
Application.ScreenUpdating = False
On Error Resume Next
    Cells.Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Cells.Select
    ActiveSheet.Paste
'
    Columns("C:C").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("D:D").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'
    Columns("F:F").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("G:G").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'
    Columns("J:J").Select
    Application.CutCopyMode = False
    Selection.Copy
    Columns("K:K").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'
    Range("E9").Select
    Range("E9:E4000").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
'
    Range("L9").Select
    Range("L9:L4000").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
'
    Range("N9").Select
    Range("N9:AR4000").Select
    Selection.SpecialCells(xlCellTypeConstants, 23).Select
    Selection.ClearContents
    Range("A1").Select
'
    Columns("D:D").ColumnWidth = 0
    Columns("G:G").ColumnWidth = 0
    Columns("J:J").ColumnWidth = 0
    Columns("K:K").ColumnWidth = 0
    Columns("L:L").ColumnWidth = 0
'
        Range("N6").Select
    ActiveWindow.FreezePanes = True
'
        Application.ScreenUpdating = True
End Sub

Thank you.