Copying and pasting from a different workbook | Excel forum

Resolved · High Priority · Version 2010

Thu 20 Aug 2015, 18:05 Delegate Freddie said...

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

Fri 21 Aug 2015, 10:40 Trainer Jens said...

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