Consulting

Page 2 of 2 FirstFirst 1 2
Results 21 to 35 of 35

Thread: VBA Edit Links

  1. #21
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    Yes you're spot on with this. tbh, it shouldn't be there so I've removed. Benefit of fresh pair of eyes. :-)


    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?

    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.


    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)

  2. #22
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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 View Post
    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?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #23
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    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 View Post
    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?
    Attached Files Attached Files

  4. #24
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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?
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  5. #25
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    See if this one-liner doesn't do 99%+ of what you're trying to do:
    Sub blah()
        Sheets(Array("Mainstay Master", "Mainstay Report")).Copy
    End Sub
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  6. #26
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    Capture 3.jpgI 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 View Post
    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?

    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

    Last edited by SteveABC; 03-02-2021 at 01:30 PM. Reason: add attachment

  7. #27
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    see msg#25
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  8. #28
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    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 View Post
    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.


    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

  9. #29
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    See note in comments in the code below.
    This is one way:
    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
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  10. #30
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    re. Teamviewer, do an internet search…

  11. #31
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    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 View Post
    See note in comments in the code below.
    This is one way:
    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

  12. #32
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    after:
    Set NewBk = ActiveWorkbook
    add:
    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.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  13. #33
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    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

  14. #34
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by SteveABC View Post
    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.
    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 View Post
    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 View Post
    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:
    2021-03-03_182421.png


    Quote Originally Posted by SteveABC View Post
    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 View Post
    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').
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  15. #35
    VBAX Regular
    Joined
    Feb 2021
    Posts
    30
    Location
    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 View Post
    Step through the following macro with F8 on the keyboard observing the active sheet as you go.
    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:
    2021-03-03_182421.png


    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').

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •