+ Reply to Thread
Results 1 to 16 of 16

How to achieve autofit in auto pasted links

  1. #1
    Registered User
    Join Date
    01-22-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2013
    Posts
    8

    How to achieve autofit in auto pasted links

    I have a worksheet consisting of several tabs. The first tab contains a table that is filled out manually. The content of the columns is auto pasted with paste link to tables on the other tabs.
    Now I have found the following vba code on internet:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, _
    ByVal Target As Range)
    Dim actCol, curCol
    actCol = Target.Column
    If actCol > 52 Then
    curCol = Chr(Int((actCol - 1) / 52) + 64) & _
    Chr(Int((actCol - 27) / 26) + 64) & _
    Chr(Int((actCol - 27) Mod 26) + 65)
    ElseIf actCol > 26 Then
    curCol = Chr(Int((actCol - 1) / 26) + 64) & _
    Chr(Int((actCol - 1) Mod 26) + 65)
    Else
    curCol = Chr(actCol + 64)
    End If
    Columns(curCol & ":" & curCol).AutoFit
    End Sub

    This code sequence autofits text in all tabs but not in the pasted links.
    Do I need more code or some other command to autofit the pasted links?

  2. #2
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: How to achieve autofit in auto pasted links

    Your SheetChange event will only be triggered when a cell value in the workbook changes. A link points to a value in another workbook, but changing that value doesn't trigger the event in this workbook.

    Welcome to the forum! Please take some time to review the forum rules, then go back and edit your post to include code tags around your code.

    Thanks!
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  3. #3
    Registered User
    Join Date
    01-22-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: How to achieve autofit in auto pasted links

    Hello Natefarm,
    May be I did not use the proper terms as I use excel in a different language. So again:
    I have a workbook with several sheets and I paste links from the first sheet to the others, inside the very same workbook.

  4. #4
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: How to achieve autofit in auto pasted links

    In Excel "Links" refer to formulas or references to values in other workbooks. So I'm still unclear what you mean by "links" if it's in the same workbook. If you just mean a formula that points to a value in another sheet, then what I said above would apply (Changing the value in the other sheet wouldn't trigger the Change event in this one.) However, you also said "paste", which implies "copy and paste", so again I'm unclear.

    But regardless of my confusion, if you enter (or paste) a value into a worksheet, it will trigger that sheet's change event. If you enter a value on another sheet that affects values on this one, it won't trigger the event. I hope that clears it up.

  5. #5
    Registered User
    Join Date
    01-22-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: How to achieve autofit in auto pasted links

    Hello natefarm,
    It is not about formulae but about plain text and just a paste special option called: "koppeling plakken" in Dutch, literally translated as "paste link".

  6. #6
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: How to achieve autofit in auto pasted links

    My apologies, eekie1. I don't normally use Paste Link, but now I understand. I tried your code and it seems to work fine when I Paste Link from another sheet, so I don't know why it doesn't work for you. Put a debug stop at the beginning of your code and see if it hits the event. If so, step through and find what's going wrong. If not, then ??

    That being said, it seems that your code is far more complex than needed. The following would accomplish the same thing.
    Please Login or Register  to view this content.
    Again, please put code tags on your original post. Thanks.

  7. #7
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: How to achieve autofit in auto pasted links

    This would also work.
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    01-22-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: How to achieve autofit in auto pasted links

    Hello natefarm,
    The first code performs just like the complex code I used before. It fits the column width in the first sheet with the manual entries. In the other sheets where entries are like: ='Sheetname'!C14, the column width is not adjusted. Only when I fill an empty cel in a column with text the whole column is adjusted. This procedure is way more time consuming than the manual procedure in which you select the the column headers of the table and double click one of the column header separation lines.

  9. #9
    Registered User
    Join Date
    01-07-2014
    Location
    United States
    MS-Off Ver
    Excel 2013
    Posts
    24

    Re: How to achieve autofit in auto pasted links

    Its a cheese way to do it but you can just to
    Please Login or Register  to view this content.
    That code will autofit the first 10 columns on every sheet. You can just add that little bit of code wherever you need.

  10. #10
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: How to achieve autofit in auto pasted links

    Once again, I was apparently confused. I thought you were pasting the link, and at the time you were pasting it, it didn't resize. Now I understand that you already had the formulas in place, and when you change the source value, it doesn't change in the destination, which is what I have been telling you all along. Something like TankBuilder's code placed into the SheetChange event would take care of it, though it might affect more than you wanted. You could adjust the code as needed though.

  11. #11
    Registered User
    Join Date
    01-22-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: How to achieve autofit in auto pasted links

    TankBuilder's code does not produce any visible effect. The code itself does not induce an error message, hence the syntaxis is right.
    Any other suggestion?

  12. #12
    Registered User
    Join Date
    01-22-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: How to achieve autofit in auto pasted links

    Excuse me, I reacted too fast. It does not work when I move to another cell, but if I execute the macro (developer/macro/execute) the column width in all sheets is adjusted. This is really a step forward!

  13. #13
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: How to achieve autofit in auto pasted links

    Are you including the code in the SheetChange event?

  14. #14
    Registered User
    Join Date
    01-22-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: How to achieve autofit in auto pasted links

    Hello natefarm
    No I am not. I have searched the internet for instructions how to, but then I get terribly confused as a non VBA expert.

  15. #15
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: How to achieve autofit in auto pasted links

    Please Login or Register  to view this content.

  16. #16
    Registered User
    Join Date
    01-22-2014
    Location
    Nederland
    MS-Off Ver
    Excel 2013
    Posts
    8

    Re: How to achieve autofit in auto pasted links

    Hello natefarm,
    Finally perfect after making a small correction to your code (presumably a slip of the keyboard):

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim i As Integer
    Dim j As Integer
    For i = 1 To Sheets.Count
    For j = 1 To 10
    Sheets(i).Columns(j).EntireColumn.AutoFit
    Next j
    j = 0
    Next i
    End Sub

    Thanks a lot for your patient help actions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pasted links broken
    By aaron_burr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 11-30-2012, 10:46 AM
  2. Images and Pasted links
    By redcarded in forum Excel General
    Replies: 0
    Last Post: 02-01-2011, 02:50 AM
  3. Replies: 0
    Last Post: 06-23-2010, 01:13 PM
  4. Links created when files are copied and pasted
    By timorrill in forum Excel General
    Replies: 0
    Last Post: 03-12-2009, 09:36 AM
  5. pasted links
    By bre in forum Excel General
    Replies: 3
    Last Post: 03-07-2005, 02:14 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1