How to copy worksheet from one excel file to another

[Varun]

Guys,

How can I copy lets say sheet1 from excel file named one.xls and paste to a
different excel file named two.xls wherein the sheet name also changes from
sheet1 to Temp? Been try to do this but cannot get it to work.

Earlier in 2003, I got the code via recording a macro but in excel 2007 I am
unable to figure out how to copy sheet using macro.

Thanks.

 

[ryguy7272]

There may be a few solutions:
http://www.rondebruin.nl/copy7.htm

Or:
http://www.rondebruin.nl/ado.htm

If neither of those options work for you, post back with a little more detail.

HTH,
Ryan---

 

[Bernard Liengme]

Manually: Use View | Windows } Arrange } tiled
Drag the tab of Sheet1 to the tab list of the second workbook
Then rename the new worksheet

Macro
Sub Macro1()
Workbooks("Book1").Sheets("Sheet1").Copy
Before:=Workbooks("Book2").Sheets(1)
Workbooks("Book2").Sheets("Sheet1 (2)").Select
Workbooks("Book2").Sheets("Sheet1 (2)").Name = "temp"
End Sub

best wishes

 

[Varun]

Ryan,

Thanks for help but that does not point me to the right direction. I have a
sub called CreateTempSheet (shown below) and it's kind of doing the job but
not to the fullest extent.

Private Sub CommandButton_Click()

Dim ddifn As String
Dim ddifnWkBk As Workbook

ddifn = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls", _
Title:="Please select layer stackup file received from fab shop")

Set ddifnWkBk = Workbooks.Open(ddifn, , ReadOnly)

Call CreateTempSheet

End Sub

Sub CreateTempSheet()
Sheets("Sheet1").Select
Sheets("Sheet1").Copy
Sheets("Sheet1").Copy Before:=Workbooks("ddi_march_23.xlsm").Sheets(2)
Sheets("Sheet1").Name = "Temp"
End Sub


I am opening a workbook shown above then calling the sub CreateTempSheet
which I'd like to use to copy sheet1 from the .xls file opened then paste as
Temp worksheet in the current excel file.

Any help is appreciated.

 

[Varun]

Hi Bernard,

Can you please once again repeat the process? When I click View in Excel
2007, what do I do next (I see Arrange All and some other options and don't
see Windows or Arrange or Tiled) so I am unable to record the macro.

Help is appreciated.

 

[ryguy7272]

Ok, Varun, this seems to be working:
Sub CCClick()
Dim ddifn As String
Dim ddifnWkBk As Workbook
ddifn = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls", _
Title:="Please select layer stackup file received from fab shop")
Set ddifnWkBk = Workbooks.Open(ddifn, , ReadOnly)
Sheets("Sheet1").Select
Cells.Select
Selection.Copy
ddifn = Application.GetOpenFilename(FileFilter:="Excel Files, *.xls", _
Title:="Please select layer stackup file received from fab shop")
Set ddifnWkBk = Workbooks.Open(ddifn, , ReadOnly)
Sheets("Sheet2").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
End Sub

I hope I got it right. All I did was fire the macro, click on Book1, then
click on Book2. That's pretty much it. Watch the Workbook names and Sheet
nsmes; change to suit your specific needs.

HTH,
Ryan---