VBA Code that Copies a Tab and Find/Replace Formulas

[kirklj]

Hello XLers!

I need help with constructing a relatively simple macro. I've built a workbook containing this table:

Company Name	Directory Path
Company B	Path B
Company C	Path C
Company D	Path D
Company E	Path E
Company F	Path F
Company G	Path G

The macro should be able to perform the following:

1. Copy template tab "Company A", and then place the copied tab after "Company A".
2. Rename the copied tab to "Company B" and then Find/Replace Directory Path A with Path B.
3. Repeat step 1 but place the copied tab after "Company B". **
4. Repeat step 2, but with Company C.
5. Continue to loop through the Company Name - Directory Path table indefinitely until each company is represented with its own tab.

**NOTE: For each subsequent copy of template tab "Company A", the copied tab will need to go to the "back of the line" so that when the macro concludes, the tab order is Company A, Company B, Company C, etc.

Thanks in advance for your help, XLers!

 

[Legacy 143009]

Where should Directory Path info go in Column B or C sheet?

 

[kirklj]

Where should Directory Path info go in Column B or C sheet?

The info coming into the "Company A" sheet is done so via a data link to an outside source. That said, by changing the directory path (from Company A's data source to Company B's, for example) the underlying information for a specific company will then populate the template. I hope this makes sense.

 

[kirklj]

The info coming into the "Company A" sheet is done so via a data link to an outside source. That said, by changing the directory path (from Company A's data source to Company B's, for example) the underlying information for a specific company will then populate the template. I hope this makes sense.

Where should Directory Path info go in Column B or C sheet?

Since my workbook is currently influx, my plan was to use a named range to refer to the directory path info.

 

[Legacy 143009]

Unfotunately, I didn't get it.. Fİnd my example below. I hope you can modify it according to your need.
Let's say you have:

Code:

Sub myFunction()
  Dim lRow As Long, newSheet As Worksheet
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
  
  For i = 2 To lRow
    Worksheets("Company A").Copy After:=Sheets(Sheets.Count)
    Set newSheet = ActiveSheet
    newSheet.Name = Cells(i, 1).Value
    newSheet.Cells(2, 1).Value = Cells(i, 1).Value
    newSheet.Cells(2, 2).Value = Cells(i, 2).Value
  Next
End Sub

You will have:

 

[kirklj]

Unfotunately, I didn't get it.. Fİnd my example below. I hope you can modify it according to your need.
Let's say you have:
View attachment 82521
View attachment 82523
Code:

Sub myFunction()
  Dim lRow As Long, newSheet As Worksheet
  lRow = Cells(Rows.Count, 1).End(xlUp).Row
 
  For i = 2 To lRow
    Worksheets("Company A").Copy After:=Sheets(Sheets.Count)
    Set newSheet = ActiveSheet
    newSheet.Name = Cells(i, 1).Value
    newSheet.Cells(2, 1).Value = Cells(i, 1).Value
    newSheet.Cells(2, 2).Value = Cells(i, 2).Value
  Next
End Sub

You will have:
View attachment 82526

Thanks for you guidance!