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

OldSoldier

New Member
Joined
Mar 29, 2011
Messages
13
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to MrExcel.

You have assigned the new worksheet to a variable here:

Code:
Set NewSheet = ActiveSheet
So you can use NewSheet to refer to it.
 

OldSoldier

New Member
Joined
Mar 29, 2011
Messages
13
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
I think that should be:

Code:
Selection.Copy NewSheet.Range("U1")
The After argument applies when you copy a worksheet not a range.
 

OldSoldier

New Member
Joined
Mar 29, 2011
Messages
13
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Instead of:

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

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

OldSoldier

New Member
Joined
Mar 29, 2011
Messages
13
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

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

OldSoldier

New Member
Joined
Mar 29, 2011
Messages
13
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
 

Forum statistics

Threads
1,081,556
Messages
5,359,547
Members
400,533
Latest member
fpenning

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top