Excel VBA

Hi guys,

I am having a problem with the below code. What I'm trying to do is copy sheets from one workbook - filter by current month and copy onto another (already opened workbook). My macro falls over when it goes to Set y = ActiveWorkbook.Open

Are you able to advise? All I want to do is switch to the following workbook "L:\ClientInterest\BAD-STATIC DATA\Static Data CHECKS\Serena\MASTER_NA.xlsx") - without the need to reopen it again?


Sub CopySheets()

Dim i As Workbook

Set i = Workbooks.Open("L:\ClientInterest\BAD-STATIC DATA\NA-rates\BAD-NA Interest set-up_ 22_07_2015.xlsm")

Sheets(Array("LBARATEF", "LBAPARIF", "LBAMKTCF")).Copy
ActiveWorkbook.SaveAs ("L:\ClientInterest\BAD-STATIC DATA\Static Data CHECKS\Serena\MASTER_NA.xlsx")
Sheets("LBARATEF").Select

i.Close

Sheets("LBARATEF").Range("A1:D300000").AutoFilter Field:=3, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
Cells.Select
Selection.Copy
Worksheets.Add(After:=Worksheets("LBAPARIF")).Name = ("LBARATEF2")
Sheets("LBARATEF2").Select
ActiveSheet.Paste
Sheets("LBARATEF").Delete

Dim x As Workbook
Dim y As Workbook

Set x = Workbooks.Open("L:\ClientInterest\BAD-STATIC DATA\Static Data CHECKS\52 CCY + Market for Static Data Checks.xlsx")
Set y = ActiveWorkbook.Open

x.Sheets("CODINGS").Range("A1:B739").Copy
y.Sheets("LBAMKTCF").Range("C312:D470").PasteSpecial
y.Sheets("LBARATEF2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial

x.Close

Best regards

Freddie