Go back to copy newly-added worksheet named as a cell to copy another range in the same macro

[OldSoldier]

Hello. I am trying to copy two parts of a table, one section just having values and the other having formulas, to a new worksheet with the new worksheet taking on the name of the contents of cell A2.

Cell A2 has the name of the the data set results of a drop-down result set run by a filter macro. I am first copying the entire table in and then I am copying the formula section in to where it belongs.

The formulas do not transfer when I copy the entire table; yet data validation fields, which I want to maintain in the non-formula copied over section, do come over.

It would all work fine, except that I cannot find a way to refer back to my newly-named worksheet (as a generic new or last-activated worksheet) in order to paste my second section (StaffCounts).

Can anybody help with this?

The code, such as it is, is below.


Dim NewSheet As Worksheet
Sheets("Report").Select
Application.Goto Reference:="ReportArea"
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Sheets(Sheets.Count).Name = Sheets(Sheets.Count).Range("A2").Value
Application.CutCopyMode = False
Set NewSheet = ActiveSheet
'NewSheet = ActiveSheet.Range("A2").Value
Sheets("Report").Select
Application.Goto Reference:="StaffCounts"
Selection.Copy After:=Sheets(NewSheet).Range("U1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A4").Select
ActiveWindow.FreezePanes = True
Selection.End(xlDown).Select
'Selection.End(xlDown).Select
'Sheets("Sheet1").Name = MsgBox("Rename Sheet")
End Sub

Thanks!!

 

[OldSoldier]

Well, cell A2 is a data validation associated with a named range list. It is like I would like to have each choice in the list range run the copy, insert and rename worksheet macro we just created for each X in the named range. I am new to macros, or at least to writing them up from scratch.

Thanks.

 

[Andrew Poulsom]

What's the name of the range?

 

[OldSoldier]

Oh, I'm back. Keep on having to run away. Well, the first one is named ReportArea, which includes descriptive items and then the other range is called StaffCounts which copies over the report range part than includes staff counts. The copy/paste function, although it does not specify not to, copies over everything as values; I then copy over the formulas.

Thanks.

 

[Andrew Poulsom]

No, I mean the name used by your data validation.

 

[OldSoldier]

Oh. "Combined."

 

[Andrew Poulsom]

So you want to loop around the range Combined and apply your code?

 

[OldSoldier]

Yes, I think. Like as if I were to select the first selection, execute, select the next, execute again, and so forth until the bottom of the list.

 

[Andrew Poulsom]

Yty (untested):

Sub Test()
    Dim NewSheet As Worksheet
    Dim Cell As Range
    For Each Cell In Range("Combined")
        Set NewSheet = Sheets.Add(After:=Sheets(Sheets.Count))
        Sheets("Report").Range("ReportArea").Copy NewSheet.Range("A1")
        NewSheet.Range("A2").Value = Cell.Value
        NewSheet.Name = NewSheet.Range("A2").Value
        Sheets("Report").Range("StaffCounts").Copy
        NewSheet.Range("U1").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    Next Cell
End Sub

 

[OldSoldier]

It works very well, save for one last caveat - it turns out that some of the worksheets end up having the same name due to the length cutoff. (I applied the LEFT function as with the previous version):

NewSheet.Name = Left(NewSheet.Range("A2").Value, 31

Maybe I could name each one is succession the 29 prior characters +1, +2, etc. I am in deep water experience-wise on this one too. Thanks for your continuing help.

 

[OldSoldier]

Andrew, Thank you for all your help on my macro.