VBA Edit Links

Hiya,

Need some help on my coding below.

I'm trying to edit the links when the file copies over the formulas and change the source book from the xlsm file to the xlsx. I've attempted the coding but it has no impact when I play through the code, which would indicate that I'm missing something or its not in the right order, but I simply can't figure out where I'm going wrong. Any ideas?

I've highlighted the code where I want to edit the links

Code:

---

Dim w As Worksheet, b As Workbook, ol As Object, msg As Object, Rng As RangeDim mypath As String, myfile As String, scc As String, sto As String
Dim link, linkSources, newLink As String
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting"
With Sheets("Control")
 sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")
  End With
Set w = Sheets("Mainstay Master")
Set X = Sheets("Mainstay Report")
Set b = Workbooks.Add
Set Rng = b.Sheets(1).Range("F4:F13")

ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng

With w
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Cells(1, 1).Resize(lr, lc).Copy
    b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
    .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
End With
b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value2
X.Copy , b.Sheets(1)
b.Sheets(1).Name = "Mainstay Master"

Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report").Range("b5").Copy
X.Range("b5").PasteSpecial Paste:=xlPasteValues

newLink = ThisWorkbook.Path & "\Mainstay Master.xlsx"
linkSources = ThisWorkbook.linkSources(xlLinkTypeExcelinks)
If IsArray(linkSources) Then
        For Each link In linkSources
            If InStr(link, "Mainstay Master Template.xlsm") Then _
          ThisWookbook.ChangeLink link, newLink, xlLinkTypeExcelLinks
    Next
    End If

With X
    Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Sitenames"
    End With

---

Welcome to the forum - please take a minute to read the FAQs at the link in my sig

I added CODE tags to your post - you can use the [#] icon to enter them and paste your macro between

Thanks Paul.

Do we always apply the code tags when posting the code?

Quote:

---

Originally Posted by SteveABC

Thanks Paul.

Do we always apply the code tags when posting the code?

---

Yes, it sets of the macro and applies some formatting


Sub NoCODEtags()
Msgbox "Hello World"
End Sub

Code:

---

Sub WithCODEtags()
    Msgbox "Hello World"
End Sub

---

In my version of Excel (O365) the type of linksource for a link to an Excel workbook is xlExcelLinks, not xlLinkTypeExcelLinks

Code:

---

linkSources = ThisWorkbook.linkSources(xlExcelLinks)

---

You could use the number 1 instead:

Code:

---

linkSources = ThisWorkbook.linkSources(1)

---

What version of Excel are you using?

Cross posted elsewhere.

I've changed the code as below but still didn't work. I'm using O365. Any other ideas?

Code:

---

You could use the number 1 instead:
linkSources = ThisWorkbook.linkSources(1)
What version of Excel are you using

---

direrctly after the line which begins:
newLink = ThisWorkbook.....
try adding the temporary line:
MsgBox IsArray(ThisWorkbook.LinkSources)
and run it.
What does the messagebox say?

Where is this code? (Standard code module? a sheet's code-module? the ThisWorkbook code-module) and in which workbook is it situated? (Let's hope it's not within the Personal workbook.)

You need to say where you've crossposted this question.

Message box stated = True.

The code is situated in the workbook....Mainstay Master Template.xlsm

Quote:

---

Originally Posted by p45cal

direrctly after the line which begins:
newLink = ThisWorkbook.....
try adding the temporary line:
MsgBox IsArray(ThisWorkbook.LinkSources)
and run it.
What does the messagebox say?

Where is this code? (Standard code module? a sheet's code-module? the ThisWorkbook code-module) and in which workbook is it situated? (Let's hope it's not within the Personal workbook.)

---

Why? Can delete, its not a problem.

Quote:

---

Originally Posted by p45cal

You need to say where you've crossposted this question.

---

Quote:

---

Originally Posted by SteveABC

Message box stated = True.

The code is situated in the workbook....Mainstay Master Template.xlsm

---

It's so difficult to do this in the dark, but check very carefully where you're adding/changing things and whether it's happening where you think it's happening:
All references to ThisWorkbook in the code refer to the workbook where the code resides, which you tell me is Mainstay Master Template.xlsm
So lines such as:

Code:

---

ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng

---

where you're adding a name to Mainstay Master Template.xlsm, not to any newly created workbook.
This line:

Code:

---

linkSources = ThisWorkbook.linkSources(1)

---

will return the links from Mainstay Master Template.xlsm
and this:

Code:

---

ThisWorkbook.ChangeLink link, newLink, xlLinkTypeExcelLinks

---

will try to update links in Mainstay Master Template.xlsm

Is that what you want to happen?
Perhaps those references to ThisWorkbook need to be changed to b?
Don't forget to correct both instances of xlLinkTypeExcelLinks to 1 or xlExcelLinks.

Quote:

---

Originally Posted by SteveABC

Why? Can delete, its not a problem.

---

There's no need to delete.
This will give you a full explanation: http://www.excelguru.ca/content.php?184

I've added this post to Mr ExcelForum, but I'm going to delete as no response.

Quote:

---

Originally Posted by p45cal

There's no need to delete.
This will give you a full explanation:

---

Hello.

Yes I can see why its hard to resolve if I don't make it clear for you. So let me give you some more information.

Basically I've created a new workbook which I've created two tabs and copied over data from my main file called "Mainstay Master Template.Xlsm"

W = Mainstay Master
X = Mainstay Report.

In my main file. The Mainstay report (x) gets its information from the Mainstay master tab (w). Which is essentially what I want to reproduce in the new workbook.

When I copy over the formula's however. The mainstay report still has references to the Mainstay Master Template.xlsm. So I want the formula's in Mainstay report (x) to only reference the Mainstay master (W) tab in the new created workbook. So no links to the Mainstay Master template.xlsm file.

Think I've changed the code in red as per your last post, but not sure If I've totally changed as you suggested. It breaks on this line.

Code:

---

linkSources = b.Sheets(1).linkSources

---

Code:

---

Dim w As Worksheet, b As Workbook, ol As Object, msg As Object, Rng As Range
Dim mypath As String, myfile As String, scc As String, sto As String
Dim link, linkSources, newLink As String
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting\Reports\"
With Sheets("Control")
 sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")
  End With
Set w = Sheets("Mainstay Master")
Set x = Sheets("Mainstay Report")
Set b = Workbooks.Add
Set Rng = b.Sheets(1).Range("F4:F13")
Set linkSources = b.Sheets(1)


ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng


With w
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Cells(1, 1).Resize(lr, lc).Copy
    b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
    .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
End With
b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value2
x.Copy , b.Sheets(1)
b.Sheets(1).Name = "Mainstay Master"


Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report").Range("b5").Copy
x.Range("b5").PasteSpecial Paste:=xlPasteValues


With x
    Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Sitenames"
    End With
   
newLink = mypath & "\Mainstay Master.xlsx"
linkSources = b.Sheets(1).linkSources
If IsArray(linkSources) Then
        For Each link In linkSources
            If InStr(link, "Mainstay Master.xlsx") Then _
          x.Sheets.ChangeLink link, newLink, xlExcelLinks
    Next
    End If

---

give these a try:

Code:

---

linksources=b.linksources(1)

---

Code:

---

b.ChangeLink link, newLink, 1

---

I don't think you need the line further up the macro:

Code:

---

Set linkSources = b.Sheets(1)

---

You might also need to change (if you have problems):

Code:

---

newLink = mypath & "\Mainstay Master.xlsx"

---

to

Code:

---

newLink = mypath & "Mainstay Master.xlsx"

---

Thank you for coming back promptly on this.

Unfortunately the suggestions you made didn't work.

I'm still seeing the reference as per the below.

Code:

---


=VLOOKUP($B$4,'[Mainstay Master Template.xlsm]Mainstay Master'!$F:$BU,27,FALSE)

---

Here's what the codes looks like now.

Code:

---

Dim w As Worksheet, b As Workbook, ol As Object, msg As Object, Rng As Range
Dim mypath As String, myfile As String, scc As String, sto As String
Dim link, linkSources, newLink As String
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting\Reports\"
With Sheets("Control")
 sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")
  End With
Set w = Sheets("Mainstay Master")
Set x = Sheets("Mainstay Report")
Set b = Workbooks.Add
Set Rng = b.Sheets(1).Range("F4:F13")


ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng


With w
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Cells(1, 1).Resize(lr, lc).Copy
    b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
    .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
End With
b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value2
x.Copy , b.Sheets(1)
b.Sheets(1).Name = "Mainstay Master"


Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report").Range("b5").Copy
x.Range("b5").PasteSpecial Paste:=xlPasteValues


With x
    Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Sitenames"
    End With
   
newLink = mypath & "\Mainstay Master.xlsx"
linkSources = b.linkSources(1)
If IsArray(linkSources) Then
        For Each link In linkSources
            If InStr(link, "Mainstay Master.xlsx") Then _
          b.ChangeLink link, newLink, 1
    Next
    End If

---

This is going to have to be done trial and error.
Your code:

Code:

---

If InStr(link, "Mainstay Master.xlsx") Then _

---

looks for .xlsx, shouldn't it be looking for xlsm?
Or more completely: Mainstay Master Template.xlsm ?

I suspect, but not sure, that your:

Code:

---

ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng

---

should be:

Code:

---

b.Names.Add Name:="Sitenames", RefersTo:=Rng

---

I've changed the below as you specified...but now its asking to be manually pointed. Which I'm guessing is good and we're heading on the right path.

Code:

---

This is going to have to be done trial and error.
If InStr(link, "Mainstay Master.xlsx") Then _

looks for .xlsx, shouldn't it be looking for xlsm?
Or more completely: Mainstay Master Template.xlsm ?

---

I've changed from thisworkbook to B.names.

but the code now breaks on this line... with application or object defined error. Is this because of the Sitenames reference?

Code:

---

    Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Sitenames"

---

Code:

---

I suspect, but not sure, that your:

       

Code:

       

---

ThisWorkbook.Names.Add Name:="Sitenames", RefersTo:=Rng

---

should be:

       

Code:

       

---

b.Names.Add Name:="Sitenames", RefersTo:=Rng

---

[/QUOTE]

---

With these two lines:

Code:

---

Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report").Range("b5").Copy
x.Range("b5").PasteSpecial Paste:=xlPasteValues

---

you're copying from Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report") to x, x which is:
Set X = Sheets("Mainstay Report") of the active workbook before you added another workbook; that is the same sheet. So aren't you just converting a formula in cell B5 on that sheet to a plain value? Maybe that's the intention.

With this next bit of code:

Code:

---

With x
    Range("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Sitenames"
    End With

---

although you've got With x, you're not using it; Range("B4") is not a qualified range so the active sheet is assumed (unless this code is in a sheet's code-module instead of a standard code-module). I don't know which sheet it's referring to, nor which sheet you want to add the validation to.

This is so difficult to do in the dark.
If you want we could do a TeamViewer session tomorrow sometime (I'm in the UK).

Yes you're spot on with this. tbh, it shouldn't be there so I've removed. Benefit of fresh pair of eyes. :-)

Code:

---

you're copying from Workbooks("Mainstay Master Template.xlsm").Worksheets("Mainstay Report") to x, x which is:
Set X = Sheets("Mainstay Report") of the active workbook before you added another workbook; that is the same sheet. So aren't you just converting a formula in cell B5 on that sheet to a plain value? Maybe that's the intention.

---

Ultimately what I'm trying to do here is to create a drop down list in cell 4 on the mainstay report (x.sheet). The list being created from a defined range (F4:F13) on the Mainstay master tab. (w.sheet) The range is to be called sitenames.

Spot on again. Didn't define the range. Doh. Obvious now.

I've amended the code to this instead. Removing the range and replacing with cells. It's solved the 'define app/object error' but inadvertently created a new error on the next line 'Invalid procedure'

I'm guessing this is because the range 'sitenames' has not been defined correctly?

Code:

---

X.Cells("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Sitenames"

---

Happy to speak to an expert. :-)

If you like I could send you a test file so you could try the code yourself. Least you wont be in the dark anymore. Either way I'm happy if it means we can resolve.

Also, just want to say a BIG thank you for helping me.

Code:

---

This is so difficult to do in the dark.
If you want we could do a TeamViewer session tomorrow sometime (I'm in the UK)

---

A view of the actual xlsm file would be a big step to my understanding of the problem.
I also need to understand what the aim of this is; at first I thought you were copying two sheets of that file to another (new) workbook, possibly to send to others, while preserving your xlsm file, but now I'm not so sure when I re-read your first post:

Quote:

---

Originally Posted by SteveABC

change the source book from the xlsm file to the xlsx.

---

It's difficult to work out what you want to do from code that isn't doing what you want it to do!
So along with the file I'd like an overview of your misson.

Another approach to this might be to save (or save a copy of) the whole file as an xlsx (this won't lose you the xlsm file) and remove the sheets you no longer want in that file and save again; I expect the formulae within that file would not point to another file.

Did you understand what I meant about a TeamViewer session?

Hi.

Sorry for the delay. its been a busy day.

I've created a test file that mirror's my actual file. Within this file it has two tabs.

1. Mainstay Master.
2. Mainstay Report.

As you can see from the test file, the report tab uses the master file for its information based on a validation list situated in cell B4. The validation list comes from a list I've defined called sitenames1. See file for the range.

So essentially. I want to copy these tabs and paste into a new workbook called 'Mainstay Master' incorporating the drop down list in B4 on the reporting tab using the defined range 'sitenames1' Because we are creating the a new workbook every time. The defined range will need to be created once the worksheets have been copied over from scratch every time.

The Mainstay master tab will need to be copied over without any formula's, but the formats, row and column sizes must be exact as the the xlsm file. Hence my code 'with w'

If you click on D58 for example in the mainstay report. The formula that exists. I want to see in the new workbook. 'Mainstay Master' (currently getting the link to the XLSM file).

I can then send this report to the client and they will be able to change the site name and the look up the information that pertains to that individual site. (hope this gives you a clearer picture)


No sorry. Have no idea what you mean by Teamviewer session???

Quote:

---

Originally Posted by p45cal

A view of the actual xlsm file would be a big step to my understanding of the problem.
I also need to understand what the aim of this is; at first I thought you were copying two sheets of that file to another (new) workbook, possibly to send to others, while preserving your xlsm file, but now I'm not so sure when I re-read your first post:
It's difficult to work out what you want to do from code that isn't doing what you want it to do!
So along with the file I'd like an overview of your misson.

Another approach to this might be to save (or save a copy of) the whole file as an xlsx (this won't lose you the xlsm file) and remove the sheets you no longer want in that file and save again; I expect the formulae within that file would not point to another file.

Did you understand what I meant about a TeamViewer session?

---

There's no code in the file you attached. You didn't quote the whole code in your first post here (at least it looks that way without Sub… End Sub). Also, I don't know in what kind of module the code you quoted is in.
Could you re-attach?

See if this one-liner doesn't do 99%+ of what you're trying to do:

Code:

---

Sub blah()
    Sheets(Array("Mainstay Master", "Mainstay Report")).Copy
End Sub

---

Attachment 28045I didn't attach any code because I thought you wanted to test your own code.

Here's the whole code.

Quote:

---

Originally Posted by p45cal

There's no code in the file you attached. You didn't quote the whole code in your first post here (at least it looks that way without Sub… End Sub). Also, I don't know in what kind of module the code you quoted is in.
Could you re-attach?

---

Code:

---

Sub SendReportv2()


Dim w As Worksheet, b As Workbook, ol As Object, msg As Object, Rng As Range
Dim mypath As String, myfile As String, scc As String, sto As String
Dim link, linkSources, newLink As String
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting\Reports\"
With Sheets("Control")
 sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")
  End With
Set w = Sheets("Mainstay Master")
Set X = Sheets("Mainstay Report")
Set b = Workbooks.Add
Set Rng = b.Sheets(1).Range("F4:F13")




b.Names.Add Name:="Sitenames", RefersTo:=Rng


With w
    lr = .Cells(Rows.Count, 1).End(xlUp).Row
    lc = .Cells(1, Columns.Count).End(xlToLeft).Column
    .Cells(1, 1).Resize(lr, lc).Copy
    b.Sheets(1).Range("a1").PasteSpecial xlPasteColumnWidths
    .Cells(1, 1).Resize(lr, lc).Copy b.Sheets(1).Range("a1")
End With
b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value = b.Sheets(1).Cells(4, 1).Resize(lr - 3, lc).Value2
X.Copy , b.Sheets(1)
b.Sheets(1).Name = "Mainstay Master"


X.Cells("b4").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
    Formula1:="=Sitenames"
         
newLink = mypath & "\Mainstay Master.xlsx"
linkSources = b.linkSources(1)
If IsArray(linkSources) Then
        For Each link In linkSources
            If InStr(link, "Mainstay Master Template.xlsm") Then _
          b.ChangeLink link, newLink, 1
    Next
    End If
    myfile = mypath & " Mainstay Master.xlsx"
Application.DisplayAlerts = False
For sh = b.Sheets.Count To 3 Step -1
        b.Sheets(sh).Delete
Next
Application.DisplayAlerts = True
b.SaveAs myfile
Set ol = CreateObject("outlook.application")
Set msg = ol.Createitem(0)
With msg




    .To = sto
    .cc = scc
    .Subject = "Mainstay Report & Master file"
    .Body = "Good Morning"
    .attachments.Add myfile
    .display
End With


End Sub

---

Code:

---

http://www.vbaexpress.com/forum/imag...AAAElFTkSuQmCC

---

see msg#25

Wow. Amazing piece of code that. Should have included the test book in my first post. haha. Think of the time we would have saved. haha.

How do I get the new workbook to save down as myfile? We haven't defined anything as a new workbook?

Quote:

---

Originally Posted by p45cal

see msg#25

---

I've included some additional code to your one liner as I need to save it down as myfile in the mypath directory. Its not correct as the attachment doesn't pick up the new workbook that's been created. Mainly due to me not saving the file correctly.

Code:

---

Sub blah()


Dim ol As Object, msg As Object
Dim mypath As String, myfile As String, scc As String, sto As String
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting\Reports\"
Workbooks("Mainstay Master Template").Sheets("Control").Activate
 sto = Join(WorksheetFunction.Transpose(Range("Final")), ";")


Sheets(Array("Mainstay Master", "Mainstay Report")).Copy
   
myfile = mypath & " Mainstay Master.xlsx"


Application.DisplayAlerts = True
Set ol = CreateObject("outlook.application")
Set msg = ol.Createitem(0)
With msg


    .To = sto
    .cc = scc
    .Subject = "Mainstay Report & Master file"
    .Body = "Good Morning"
    .attachments.Add myfile
    .display
End With

---

See note in comments in the code below.
This is one way:

Code:

---

Sub blah2()
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting\Reports"    'note no final backslash. Not needed if you use the next line.
myfile = mypath & "\Mainstay Master.xlsx"    'note leading backslash

'if you use the line below (currently commented out) you can delete the 2 lines above.
'myfile = ThisWorkbook.Path & "\Mainstay Master.xlsx"    'use this to save the new file in the same folder as this workbook

sto = Join(WorksheetFunction.Transpose(ThisWorkbook.Sheets("Control").Range("Final")), ";")
Sheets(Array("Mainstay Master", "Mainstay Report")).Copy
Set NewBk = ActiveWorkbook
NewBk.SaveAs myfile
Set ol = CreateObject("Outlook.Application")
Set msg = ol.Createitem(0)
With msg
  .To = sto
  '.cc = scc 'don't know where this scc comes from
  .Subject = "Mainstay Report & Master file"
  .Body = "Good Morning"
  .attachments.Add myfile
  .display
End With
NewBk.Close    'if you want
End Sub

---

re. Teamviewer, do an internet search…

Ok 90% there. Master Report is now exactly how I want to view the file. Thank you so much for helping me here.

Just a small issue on the Mainstay Master sheet. I need the formula's to be removed. I.e paste values but only in rows 4 to 15.

Quote:

---

Originally Posted by p45cal

See note in comments in the code below.
This is one way:

Code:

---

Sub blah2()
mypath = "W:\.Team Documents\Freehold Team\Mainstay\Reporting\Reports"    'note no final backslash. Not needed if you use the next line.
myfile = mypath & "\Mainstay Master.xlsx"    'note leading backslash

'if you use the line below (currently commented out) you can delete the 2 lines above.
'myfile = ThisWorkbook.Path & "\Mainstay Master.xlsx"    'use this to save the new file in the same folder as this workbook

sto = Join(WorksheetFunction.Transpose(ThisWorkbook.Sheets("Control").Range("Final")), ";")
Sheets(Array("Mainstay Master", "Mainstay Report")).Copy
Set NewBk = ActiveWorkbook
NewBk.SaveAs myfile
Set ol = CreateObject("Outlook.Application")
Set msg = ol.Createitem(0)
With msg
  .To = sto
  '.cc = scc 'don't know where this scc comes from
  .Subject = "Mainstay Report & Master file"
  .Body = "Good Morning"
  .attachments.Add myfile
  .display
End With
NewBk.Close    'if you want
End Sub

---

---

after:
Set NewBk = ActiveWorkbook
add:

Code:

---

With NewBk.Sheets("Mainstay Master")
  With Intersect(.UsedRange, .Rows("4:15"))
    .Value = .Value
  End With
End With

---

If row 15 is not always the last row you can make that 15, say, 500.

That's worked. All done sir. Been a pleasure working with you.

For my own education purposes. I have a few questions.

1. What is intersect and what function does it perform in that code?

2. Can you explain to me how the below copies the sheets and pastes in the exact formats as required into a new workbook? What does the array function do? How comes you don't need to declare?

How do I acknowledge your expertise?

Sheets(Array("Mainstay Master", "Mainstay Report")).Copy

Quote:

---

Originally Posted by SteveABC

1. What is intersect and what function does it perform in that code?

---

Step through the following macro with F8 on the keyboard observing the active sheet as you go.

Code:

---

Sub blah3()
Set rngA = Range("B2:F8")
Set rngB = Range("E6:I12")
rngA.Select
rngA.BorderAround 1
rngB.Select
rngB.BorderAround 1
Intersect(rngA, rngB).Select
Intersect(rngA, rngB).Interior.Color = rgbLightGrey
Range("B2").Select
End Sub

---

Quote:

---

Originally Posted by SteveABC

2. Can you explain to me how the below copies the sheets and pastes in the exact formats as required into a new workbook?
Sheets(Array("Mainstay Master", "Mainstay Report")).Copy

---

It's the same as doing the following but record a macro while you: select both the sheets' tabs (use the Shift key to help with that as, you do with cells)
Right-click one of the selected sheet tabs and choose Move or Copy, then in the To book: dropdown field choose (new book), then tick the Create a copy tick box, click OK. Stop recording and examine the code.
Copying sheets copies pretty much everything. By copying both sheets at once it looks like they keep their interrelationship.

Quote:

---

Originally Posted by SteveABC

What does the array function do?

---

It puts the two strings (sheet names) into an array in memory and is one way the code wants to see multiple sheets referred to:
Attachment 28052

Quote:

---

Originally Posted by SteveABC

How comes you don't need to declare?

---

Because I don't put Option Explicit at the top of the code-module; lazy.

Quote:

---

Originally Posted by SteveABC

How do I acknowledge your expertise?

---

You've already done so merely by replying. I try to remember not to help again when people just run off with a solution without giving any sort of feedback (on some forums it could just be marking the thread as 'Solved').

Well once again thank you for your input. Been incredible to talk and learn from you. The below was very insightful. I hope other people have learnt something from this as well. Look forward to our next discussion.

Quote:

---

Originally Posted by p45cal

Step through the following macro with F8 on the keyboard observing the active sheet as you go.

Code:

---

Sub blah3()
Set rngA = Range("B2:F8")
Set rngB = Range("E6:I12")
rngA.Select
rngA.BorderAround 1
rngB.Select
rngB.BorderAround 1
Intersect(rngA, rngB).Select
Intersect(rngA, rngB).Interior.Color = rgbLightGrey
Range("B2").Select
End Sub

---

It's the same as doing the following but record a macro while you: select both the sheets' tabs (use the Shift key to help with that as, you do with cells)
Right-click one of the selected sheet tabs and choose Move or Copy, then in the To book: dropdown field choose (new book), then tick the Create a copy tick box, click OK. Stop recording and examine the code.
Copying sheets copies pretty much everything. By copying both sheets at once it looks like they keep their interrelationship.



It puts the two strings (sheet names) into an array in memory and is one way the code wants to see multiple sheets referred to:
Attachment 28052


Because I don't put Option Explicit at the top of the code-module; lazy.



You've already done so merely by replying. I try to remember not to help again when people just run off with a solution without giving any sort of feedback (on some forums it could just be marking the thread as 'Solved').

---