copy and paste special in new sheet

robertc12

Hi,

I've created a macro which opens a workbook, creates and renames a new worksheet, and moves it to the end of the workbook.

I then need to paste into this new worksheet a selection from another workbook.

How would I specify in the code that the selection needs to be pasted into the newly created tab?

The code I have so far is as follows:


Worksheets.Add.Name = Workbooks("SHEET1").Worksheets("monthly report").[p1]

ActiveSheet.Move _
After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

Windows("SHEET1.xls").Activate
Range("A1:N23").Select
Selection.Copy

Dim wb1 As Workbook
Dim wb2 As Workbook
Dim ws As Worksheet

Set wb1 = Workbooks("Sheet 1")
Set wb2 = Workbooks("Monthly Report")
With wb2
Worksheets.Add.Name = .Worksheets("monthly report").[p1]
ActiveSheet.Move After:=.Sheets(.Sheets.Count)
Set ws = wb.Worksheets([p1])
End With
wb1.Range("A1:N23").Copy ws.Range("a1")

However, I got a subscript out of range error pointing at the line of code:

Worksheets.Add.Name = .Worksheets("monthly report").[p1]

If you could point me in the right direction it would be massively appreicated,

Kind Regards
Rob