Macro to

[thelad]

Hi,

I have a macro that works and lists in sheet 1 the names of all the worksheets within the workbook in column A.

However I would like to further develop this macro to do the following:

(Hope this is not to complex for the very skilled ppl here)

• Create a copy of each tab and move to spreadsheet that is in location g1 (I will have link of spreadsheet there)
• The Tabs i need to do the above to will have Y beside them in column B
• if possible I would like to include the word at end of each tab name that is beside in column C.

Let me give an example:

john	Y	Hap		t:/backup.xls
Jay
Mich	Y	Hi
Joe	Y	Ho
Jim

<tbody>
</tbody>

So if raw data was above the macro would copy and move the tabs, John, Mich and Joe (tab names with Y in column B) to spreadsheet in T drive for example.(move them after tabs already in it.)

Then if possible if like to rename them as original name as in column A but put the few letters in name that is in column C. for example.

On spreadsheet they have being moved too tab John will now be John Hap, tab Mich will be Mich Hi and tab Joe will be Joe Hi.

Do you think the above is possible?

I really appreciate you reading this. Thanks

 

[dUBBINS]

Alright, I am not as good at writing script as a lot of the folks in here. But since no one is helping you, I will write some ugly code that will work for you.

Tell me, do you have a set number of columns? If so, what is the range? (e.g. Column A to Column X, etc.)

 

[dUBBINS]

Okay, here is some code I threw together. Maybe you can sort out the changes you need to make it work, if not I will help you on Monday. Definitely, the Z in both places needs to be changed to whatever you last Column is. And the UNKNOWN needs to be replaced with your workbook name.

Sub createNEW()
Dim shtNM1 As String
Dim shtNM2 As String
Dim cpy As String
Dim fileNM As String
Dim x As Integer
    x = 1
Dim lastROW As Integer
    lastROW = Cells(Rows.Count, "a").End(xlUp).Row
For x = 1 To lastROW
    cpy = Cells(x, 2).Value
        If cpy = "y" Then
            shtNM1 = Cells(x, 1).Value
            shtNM2 = Cells(x, 3).Value
            Sheet(shtNM1).Select
            Range("a1:z100").Copy
            With Workbook("backup.xls")
                .Activate
                .Select
            End With
            Sheets(Sheets.Count).Select
            Sheets.Add after:=ActiveSheet
            ActiveSheet.Name = shtNM1 & " " & shtNM2
            Range("a1:z100").PasteSpecial
            With Workbook("UNKNOWN.xls")
                .Activate
                .Select
            End With
        Else
        End If
Next x
End Sub

 

[thelad]

hii Dobbinn..thanks for looking at this...I will test your code later or tomorrow hopefully...Thans alot for looking at this though.

Regards