copy and paste special in new sheet

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
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories