PDA

View Full Version : [SOLVED] Copy sheet into another workbook and put in alphabetical order



Nicolaf
03-02-2014, 06:36 AM
Hi,

I have a workbook with sheet1 and sheet2.
Let's call this workbook Fruits.

Starting in sheet1 I would like to have a macro that makes a copy of sheet2 changes its name from sheet2 to name found in sheet1 Cell E3 and copies it into new workbook.
New workbook name will be found in sheet1 cell D3 and the sheet2 copied (which now has new name say mango) must be inserted into new workbook in alphabetical order.

So if new workbook has three sheets called apple, banana and pear and name given to sheet2 is mango then mango must be between banana and pear.

How do I do this?

Nix
:think::think:

patel
03-02-2014, 09:11 AM
can you attach a sample file ?

Nicolaf
03-02-2014, 02:11 PM
Please see sample files attached.

workbook Fruits and workbook February which is one where we must copy sheet we decided to call Mango.

Anything not clear let me know.

Txs,
Nix

Nicolaf
03-02-2014, 02:12 PM
workbook February attached

txs

patel
03-03-2014, 01:05 AM
you attached some very beatiful blank sheets

Nicolaf
03-03-2014, 02:50 AM
Ha ha,

In fact not as blank as may seem :-)

Worksheet Fruit has in sheet1 Cell D3 the path to worksheet I need to open (in this case called February) it also has in Cell E3 the name I want to give sheet2 before copying it to worksheet February.

Worksheet February has three sheets called Apple, Banana and Pear.

So my macro should:

1. Create a copy of Sheet2 and give it name Mango (name found in cell E3)
2. Open workbook February (path found in cell D3 Sheet1).
3. Insert sheet Mango into workbook February in alphabetical order so Mango will come after Banana and before Pear.


The reason why I did not fill in sheet2 with data is because this is irrelevant to macro we are building as in sheet2 there will be a template but we do not need to specify details as macro will create a copy of sheet2 and insert it into new workbook.

Let me know if anything else not clear.

Thanks!
Nix

patel
03-03-2014, 04:22 AM
Sub a()
Set wb = ThisWorkbook
shname = Range("E3")
fname = Range("D3") & ".xlsx"
Workbooks.Open fname
wb.Sheets("Sheet2").Copy Before:=Sheets(1)
Sheets(1).Name = shname
Call shsort
End Sub
Sub shsort()
smove = True
While smove = True

first = True
smove = False
prec = ""
corr = ""
For Each ws In Worksheets
If first Then
corr = LCase(ws.Name)
first = False
Else
prec = corr
corr = LCase(ws.Name)
End If
If prec <> "" Then
If corr < prec Then
smove = True
ws.Move Before:=Sheets(prec)
End If
End If
Next
Wend
End Sub

Nicolaf
03-04-2014, 12:10 PM
Great thanks!

It's exactly what I wanted!

Nix

:hi::hi: