COPY SHEET TO ANOTHER WORKBOOK :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

COPY SHEET TO ANOTHER WORKBOOK
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

verluc
Board Master


Joined: 02 Mar 2002
Posts: 703


Status: Offline

 Reply with quote  

COPY SHEET TO ANOTHER WORKBOOK

I have let say Sheet2 that I want to copy to another workbook in sheet1
and automatickly sheet2 and 3 from that new workbook make hidden.
Who can give me a tip to start?
Many thanks in advance

Post Sat Sep 20, 2003 12:27 pm 
 View user's profile Send private message

Andrew Poulsom
MrExcel MVP


Joined: 22 Jul 2002
Posts: 8266

Flag: Uk

Status: Offline

 Reply with quote  

Re: COPY SHEET TO ANOTHER WORKBOOK

Why don't you record a macro while doing it manually?

Post Sat Sep 20, 2003 12:38 pm 
 View user's profile Send private message

verluc
Board Master


Joined: 02 Mar 2002
Posts: 703


Status: Offline

 Reply with quote  

Re: COPY SHEET TO ANOTHER WORKBOOK

I don't know how to do this.Sorry

Post Sat Sep 20, 2003 12:40 pm 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: COPY SHEET TO ANOTHER WORKBOOK

Press Alt+F11
Insert - Module and paste the macro below in.

Sub wbcopy()

Worksheets.Copy

Sheets("Sheet1").Name = "X"
Sheets("Sheet2").Name = "Sheet1"
Sheets("X").Name = "Sheet2"

Worksheets(Array("Sheet2", "Sheet3")).Visible = xlHidden

'Dim oSht As Object 'deletes all hidden sheets
'Application.DisplayAlerts = False 'suppress delete warning
'For Each oSht In Sheets
'If Not oSht.Visible Then oSht.Delete
'Next
'Application.DisplayAlerts = True


End Sub


The last part can delete all the hidden sheets if you want - just take away the ' at the beginning of each line.
_________________
George J

Post Sat Sep 20, 2003 2:05 pm 
 View user's profile Send private message Send e-mail

Richie(UK)
MrExcel MVP


Joined: 18 May 2002
Posts: 2464
Location: Worcester, England
Flag: Uk

Status: Offline

 Reply with quote  

Re: COPY SHEET TO ANOTHER WORKBOOK

Hi v,

The Macro Recorder is a valuable tool in learning VBA and you will benefit greatly from learning how to use it. In fact, I suspect that the majority of experienced coders continue to make use of it now and then.

In Excel's Help files lookup "macros, recording" - this will get you started. Although the code produced is not always very efficient (eg. too many Select and Activate commands) it will help you to learn the VBA equivalent of the actions that you perform manually in Excel.

HTH
_________________
Richie

Post Sat Sep 20, 2003 3:25 pm 
 View user's profile Send private message

verluc
Board Master


Joined: 02 Mar 2002
Posts: 703


Status: Offline

 Reply with quote  

Re: COPY SHEET TO ANOTHER WORKBOOK

Sorry George but I want to copy for example:
Workbook X sheet1 to Workbook Y sheet1
Many thanks for help

Post Sat Sep 20, 2003 10:01 pm 
 View user's profile Send private message

George J
Board Master


Joined: 16 Feb 2002
Posts: 375
Location: Edinburgh, Bonnie Scotland
Flag: Scotland

Status: Offline

 Reply with quote  

Re: COPY SHEET TO ANOTHER WORKBOOK

Just to double check - Does the file you want these sheets imported to have the same sheet names?

Not too sure what you would be able to avhieve with the macro as it seems you are renaming one aheet from a file and just exporting it to another workbook.

Can you correct me if i have this totally confused?

What i have so far is below if someone else wants to try amending it before i can get back to you.

Sub wbcopy()

Dim CurWkbk As Workbook
Dim newWkbk As Workbook
Dim ws As Worksheet

Set CurWkbk = ActiveWorkbook

FNameAndPath = Application.GetOpenFilename _
(FileFilter:="Excel Files (*.xls), *.xls", _
Title:="Select file data is to be transferred to")

If FNameAndPath = False Then Exit Sub

Set newWkbk = Workbooks.Open(FileName:=FNameAndPath)

For Each ws In CurWkbk.Worksheets
ws.Copy before:=newWkbk.Sheets(1)

Next



Sheets("Sheet1").Name = "X"
Sheets("Sheet2").Name = "Sheet1"
Sheets("X").Name = "Sheet2"

Worksheets(Array("Sheet2", "Sheet3")).Visible = xlHidden

'Dim oSht As Object 'deletes all hidden sheets
'Application.DisplayAlerts = False 'suppress delete warning
'For Each oSht In Sheets
'If Not oSht.Visible Then oSht.Delete
'Next
'Application.DisplayAlerts = True


End Sub
_________________
George J

Post Tue Sep 23, 2003 12:32 am 
 View user's profile Send private message Send e-mail
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


Forum Rules:
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.