Create Copy of Worksheet and Move

Resolved · High Priority · Version 2010

Thu 13 Nov 2014, 12:22 Edited on Thu 13 Nov 2014, 12:26 Delegate Mark said...

Mark has attended:
Excel VBA Intro Intermediate course
Excel Advanced - For Power Users course
Excel PowerPivot course
Excel Dashboards for Business Intelligence course

Create Copy of Worksheet and Move

Hi,

I'm trying to create a copy of an existing worksheet and insert it into a different workbook which I'm referring to using a dim as string, I can't however get the VBA to work. Any suggestions?

Dim Site As String
Site = Sheets("2014-20 Based on 2013 (3)").Range("F3").Value & ".xlsm"

Workbooks.Open filename:="G:\Network Operations Team\2014\MBPC\Network Capacity\StockTurn.xlsx"
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=(Site.Sheet(13))

Thanks,
Mark

Thu 13 Nov 2014, 13:30 Trainer Jens said...

RE: Create Copy of Worksheet and Move

Hi Mark,

Thank you for the forum question.

The only problem you have in the code is how you refer to the destination workbook.

Try the code below:

Dim Site As String
Site = Sheets("2014-20 Based on 2013 (3)").Range("F3").Value & ".xlsm"

Workbooks.Open filename:="G:\Network Operations Team\2014\MBPC\Network Capacity\StockTurn.xlsx"
Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=workbooks(Site).Sheet(13)

Please let me know if this is not working for you.

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

Thu 20 Nov 2014: Automatically marked as resolved.

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Saving your Excel Spreadsheet as a CSV File

In situations where you need to save your Excel spreadsheet as a CSV file, follow these simple steps.

Click the File tab and click Save As.
Enter a name in the File name field.
Click the drop-down arrow next to the Save as type field to select the file type. Scroll down the list and select CSV (comma delimited) (*.CSV)
Click Save

The data will now be saved to a separate CSV file which can be used in different applications.

View all Excel hints and tips