Macro to

thelad

Board Regular
Joined
Jan 28, 2011
Messages
235
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:

johnYHapt:/backup.xls
Jay
MichYHi
JoeYHo
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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

dUBBINS

Well-known Member
Joined
Feb 9, 2015
Messages
504
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

Well-known Member
Joined
Feb 9, 2015
Messages
504
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.

Code:
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

Board Regular
Joined
Jan 28, 2011
Messages
235
hii Dobbinn..thanks for looking at this...I will test your code later or tomorrow hopefully...Thans alot for looking at this though.

Regards
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,316
Members
414,053
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top