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!!

 

[Andrew Poulsom]

Welcome to MrExcel.

You have assigned the new worksheet to a variable here:

Set NewSheet = ActiveSheet

So you can use NewSheet to refer to it.

 

[OldSoldier]

Thanks for replying.

That's kind of what I was hoping, except the execution gets hung up here:

Selection.Copy After:=Sheets(NewSheet).Range("U1").Select

At this point the new worksheet is now named after cell A2.

 

[Andrew Poulsom]

I think that should be:

Selection.Copy NewSheet.Range("U1")

The After argument applies when you copy a worksheet not a range.

 

[OldSoldier]

It didn't work. I am copying from the "StaffCounts" named range of the "Report" Sheet and trying to paste it as formulas at cell U2 of the new worksheet (That was named after cell A2. I tried your code after neutralizing my line and it didn't work.

What am I missing? I am using Excel 2007, btw. Thanks again.

 

[Andrew Poulsom]

Instead of:

Sheets("Report").Select
Application.Goto Reference:="StaffCounts"
Selection.Copy After:=Sheets(NewSheet).Range("U1").Select

try:

ThisWorkbook.Sheets("Report").Range("StaffCounts").Copy NewSheet.Range("U2")

 

[OldSoldier]

Andrew,

All day to get back to you, to let you know...it worked! With one caveat however. It did not work like this

ThisWorkbook.Sheets("Report").Range("StaffCounts").Copy NewSheet.Range("U1")

It worked like this: (and the second line had to be on another row, apparently

ThisWorkbook.Sheets("Report").Range("StaffCounts").Copy
NewSheet.Range("U1").Select

Thanks much.

It turns out I have one more problem. The macro is hanging up on this line:

Sheets(Sheets.Count).Name = Sheets(Sheets.Count).Range("A2").Value

I think it is because cell A2, the renaming worksheet range is greater than 31 characters. Do you know of some code that would truncate this?

 

[Andrew Poulsom]

Try:

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

 

[OldSoldier]

Wow Andrew - that is awesome. It works. Thank you SO much.

I really can't believe it.

It is occurring to me that I may be able to run a loop that for drop down list range that occupies A2 for each choice in the list that would enable me to run all the values in the list at one time instead of selecting one at a time and running.

Or perhaps you know of the general term for such a procedure?

Thanks again!

Old Soldier

 

[Andrew Poulsom]

What do you want to do in a loop exactly?