Freddie has attended:
Excel VBA Introduction course
Copying and pasting from a different workbook
Hi Guys
I was wondering whether you could help with the below:
I'm trying to only filter data by current month, then copy and paste the worksheet (LBARATEF) to another workbook along with two other worksheets. All I have at the moment is the below and am now completely lost in terms of defining workbooks etc.
Sub CopySheets()
Dim i As Workbook
Dim x As Workbook
Dim y As Workbook
Set i = Workbooks.Open("L:\ClientInterest\BAD-STATIC DATA\APAC-rates\BAD-APAC Interest set-up_17_08_2015.xlsm")
Sheets(Array("LBARATEF", "LBAPARIF", "LBAMKTCF")).Copy
ActiveWorkbook.SaveAs ("L:\ClientInterest\BAD-STATIC DATA\Static Data CHECKS\Serena\MASTER_APAC_MIDMONTHCHECK.xlsx")
Sheets("LBARATEF").Select
Sheets("LBARATEF").Range("A:D").AutoFilter Field:=3, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic
i.Close
Any help would be hugely beneficial!
Freddie
RE: Copying and pasting from a different workbook
Hi Freddy,
Thank you for the forum question.
I have tested the code with one of my workbooks and the code below works fine. I have amended few things as you can see.
It is code from my test.
See the comments in the code.
Sub CopySheets()
Dim i As Workbook
Set i = Workbooks.Open("C:\Users\jens\Documents\data.xlsx")
'''''I have added i
i.Sheets(Array("data", "data2")).Copy
ActiveWorkbook.SaveAs ("C:\Users\jens\Documents\data2.xlsx")
Sheets("Data").Select
'''''I am using Currentregion to refer to the whole range
Range("A1").CurrentRegion.AutoFilter Field:=7, Criteria1:=xlFilterThisYear, Operator:=xlFilterDynamic
i.Close
End Sub
I hope this can be a help.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
