Macro - Copy current tab to new workbook

[roycettu]

Macro - Copy current tab to new workbook

--------------------------------------------------------------------------------

I have two files saved.

2011 rolling 12.xlsx
2011 Budget rolling 12 updater.xlsx

I'm trying to write a Macro and struggling

I am building the macro within the 2011 Budget rolling 12 updater.xlsx workbook. I will have the PL tab displayed. This spread sheet has multiple tabs.
1. I need to run the macro and it will copy cells from A1 to R315 of the 2011 Budget rolling 12 updater.xlsx workbook PL tab

2. I need it to auto create a new tab in 2011 rolling 12.xlsx workbook and name it the tab cell contents of C1 of the 2011 Budget rolling 12 updater.xlsx PL tab.

3. Paste the copied contents - Values only to A1 in the newly created tab


Or something of the sorts. It could save me hours of copy and pasting
Thank you

[stnkynts]

Please Login or Register  to view this content.

Put in the 2011 Budget rolling 12 updater.xlsx file. Run from that workbook while other workbook is closed. Great job btw on explaining what you want to have accomplished. Made my job that much easier. Let me know if you have any questions.

[roycettu]

I figured it out!!!!!

'Activates the open Workbook
Workbooks("2011 Budget rolling 12 updater.xlsx").Activate

'makes my copy and paste
Workbooks("2011 Budget rolling 12 updater.xlsx").Sheets("PL").Copy After:=Workbooks("2011 rolling 12.xlsx").Sheets("1")

'Probably not the most effecient but copies and paste values ontop of itself
Columns("A:R").Select
Selection.Copy

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'renames my sheet
ActiveSheet.Name = Range("c1").Value

'jumps me back to my other workbook so I can get ready for the next one becuae I'm too much of a noob to do a loop macro :-)
Workbooks("2011 Budget rolling 12 updater.xlsx").Activate

'who is your daddy :-)
End Sub

[roycettu]

Please Login or Register  to view this content.

Put in the 2011 Budget rolling 12 updater.xlsx file. Run from that workbook while other workbook is closed. Great job btw on explaining what you want to have accomplished. Made my job that much easier. Let me know if you have any questions.

Thank you, I figured it out as you posted. I'm going to take yours and try to recreate it and see if I can learn from it.