1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

change excel macro

Discussion in 'Business Applications' started by ozdogs, May 6, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    I have some workbooks that use macros to filter, sort copy and past data from one to another. The workbooks are "NEW FORM"and "Exp Workout" . When recording the macros, I used the PASTE function to copy data from New Forn to Exp Workout after it had been filtered and sorted.

    The problem was that column Y in New Form contains hyperlinks and when data is pasted over the hyperlink is lost and it displays only as text. I have been experiementing with some blank workbooks and if I copy the same data from my original NEW FORM and paste into blank workbook using PASTE SPECIAL - all using source theme (Excel 2007), then the hyperlinks remain functional.

    Now.........my problem. Is there a way of editing the code in my original macro to change from PASTE to PASTE SPECIAL function as above. I have attached a copy of the macro code with the PASTE action highlighted in red text. You will see that the PASTE function appears 10 times and all would need to be changed. Everything else in the existing macro works great, just want to keep the hyperlinks active. Many thanks

    Sub NEWAUTOMATED()
    '
    ' NEWAUTOMATED Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+N
    '
    Windows("NEW FORM.xlsm").Activate
    Sheets("NEW 2 TURNS").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("NEW 2 TURNS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("NEW 2 TURNS").Sort.SortFields.Add Key:=Range( _
    "$H$2:$H$999999"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("NEW 2 TURNS").Sort
    .SetRange Range("$A$1:$Y$999999")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Windows("EXP WORKOUT.xlsm").Activate
    Sheets("workout").Select
    Application.Goto Reference:="DateOutput"
    Workbooks("NEW FORM.xlsm").Sheets("NEW 2 TURNS").Columns("A:Y").AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("A1:E2"), CopyToRange:=Range( _
    "workout!Extract"), Unique:=False
    Range("G5:AE11").Select
    Selection.Copy
    Sheets("DATA").Select
    Range("B3").Select
    ActiveSheet.Paste
    Range("B4:Z9").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I4:I9"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
    .SetRange Range("B3:Z9")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Sheets("workout").Select
    Range("G6:AE29").Select
    Selection.ClearContents
    Range("G10").Select
    Application.Goto Reference:="DateOutput"
    Workbooks("NEW FORM.xlsm").Sheets("NEW 2 TURNS").Columns("A:Y").AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("A4:E5"), CopyToRange:=Range( _
    "workout!Extract"), Unique:=False
    Selection.Copy
    Sheets("DATA").Select
    Range("B11").Select
    ActiveSheet.Paste
    Range("B12:Z17").Select
    Application.CutCopyMode = False
    Range("B11:Z17").Select
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I12:I17"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
    .SetRange Range("B11:Z17")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Sheets("workout").Select
    Range("G6:AE29").Select
    Selection.ClearContents
    Range("G12").Select
    Application.Goto Reference:="DateOutput"
    Workbooks("NEW FORM.xlsm").Sheets("NEW 2 TURNS").Columns("A:Y").AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("A7:E8"), CopyToRange:=Range( _
    "workout!Extract"), Unique:=False
    Selection.Copy
    Sheets("DATA").Select
    Range("B19").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I20:I25"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
    .SetRange Range("B19:Z25")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Sheets("workout").Select
    Range("G6:AE29").Select
    Selection.ClearContents
    Range("G11").Select
    Application.Goto Reference:="DateOutput"
    Workbooks("NEW FORM.xlsm").Sheets("NEW 2 TURNS").Columns("A:Y").AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("A10:E11"), CopyToRange:=Range _
    ("workout!Extract"), Unique:=False
    Selection.Copy
    Sheets("DATA").Select
    Range("B27").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I28:I33"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
    .SetRange Range("B27:Z33")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    ActiveWindow.SmallScroll Down:=27
    Sheets("workout").Select
    Range("G6:AE29").Select
    Selection.ClearContents
    Range("G14").Select
    Application.Goto Reference:="DateOutput"
    Workbooks("NEW FORM.xlsm").Sheets("NEW 2 TURNS").Columns("A:Y").AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("A13:E14"), CopyToRange:=Range _
    ("workout!Extract"), Unique:=False
    Selection.Copy
    Sheets("DATA").Select
    Range("B35").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I36:I41"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
    .SetRange Range("B35:Z41")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Sheets("workout").Select
    Range("G6:AE29").Select
    Selection.ClearContents
    Range("G12").Select
    Application.Goto Reference:="DateOutput"
    Workbooks("NEW FORM.xlsm").Sheets("NEW 2 TURNS").Columns("A:Y").AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("A16:E17"), CopyToRange:=Range _
    ("workout!Extract"), Unique:=False
    Selection.Copy
    Sheets("DATA").Select
    Range("B43").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I44:I49"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
    .SetRange Range("B43:Z49")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Sheets("workout").Select
    Range("G6:AE29").Select
    Selection.ClearContents
    Range("G12").Select
    Application.Goto Reference:="DateOutput"
    Workbooks("NEW FORM.xlsm").Sheets("NEW 2 TURNS").Columns("A:Y").AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("A19:E20"), CopyToRange:=Range _
    ("workout!Extract"), Unique:=False
    Selection.Copy
    Sheets("DATA").Select
    Range("B51").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I52:I57"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
    .SetRange Range("B51:Z57")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    ActiveWindow.SmallScroll Down:=21
    Sheets("workout").Select
    Range("G6:AE29").Select
    Selection.ClearContents
    Range("G15").Select
    Application.Goto Reference:="DateOutput"
    Workbooks("NEW FORM.xlsm").Sheets("NEW 2 TURNS").Columns("A:Y").AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("A22:E23"), CopyToRange:=Range _
    ("workout!Extract"), Unique:=False
    Selection.Copy
    Sheets("DATA").Select
    Range("B59").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I60:I65"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
    .SetRange Range("B59:Z65")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Sheets("workout").Select
    Range("G6:AE29").Select
    Selection.ClearContents
    Range("G13").Select
    Application.Goto Reference:="DateOutput"
    Workbooks("NEW FORM.xlsm").Sheets("NEW 2 TURNS").Columns("A:Y").AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("A25:E26"), CopyToRange:=Range _
    ("workout!Extract"), Unique:=False
    Selection.Copy
    Sheets("DATA").Select
    Range("B67").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I68:I73"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
    .SetRange Range("B67:Z73")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Sheets("workout").Select
    Range("G6:AE29").Select
    Selection.ClearContents
    Range("G15").Select
    Application.Goto Reference:="DateOutput"
    Workbooks("NEW FORM.xlsm").Sheets("NEW 2 TURNS").Columns("A:Y").AdvancedFilter _
    Action:=xlFilterCopy, CriteriaRange:=Range("A28:E29"), CopyToRange:=Range _
    ("workout!Extract"), Unique:=False
    Selection.Copy
    Sheets("DATA").Select
    Range("B75").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("DATA").Sort.SortFields.Add Key:=Range("I76:I81"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("DATA").Sort
    .SetRange Range("B75:Z81")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Sheets("workout").Select
    Range("G6:AE29").Select
    Selection.ClearContents
    Range("G12").Select
    Windows("NEW FORM.xlsm").Activate
    ActiveWorkbook.Worksheets("NEW 2 TURNS").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("NEW 2 TURNS").Sort.SortFields.Add Key:=Range( _
    "H2:H99999"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
    With ActiveWorkbook.Worksheets("NEW 2 TURNS").Sort
    .SetRange Range("$A$1:$Y$999999")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
    End With
    Windows("EXP WORKOUT.xlsm").Activate
    Sheets("DATA").Select
    ActiveWindow.SmallScroll Down:=-90
    Range("A2:B2").Select
    End Sub
     
  2. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    I know it would be easier to post copies of the 2 workbooks, but they are very large files and even to take a sample does not work very well as they are full of formula that link the 2 sheets together and once changed, the formulas become a mess, etc. so that's why I didnt post the files. I was hoping that there was just a way of editing the paste line in the macro code to use the paste special function instead.
     
  3. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Hi there,
    First, these recorde macro's are nice but impossible to maintain.
    If I want something done and I don't know how it works in vba I record a amcro too for lets say one particular action.
    Just coose one row, select it, change the sheet, select the row and paste it.
    Stop recording and see what it has done.
    That way you can use these parts to edit your vba code.

    There is 99% unnecssary 'junk' in the recorded macro and I think your lines of code could be reduced to let's say 10 by programming it.

    If you have a sample sheet with let's say several rows of varying data attacht these to the post:
    The Source workbook and the target workbook (avoid private sensitive data)
    I'll gladly take a look, make sure your recorded macro is in it too so that I can follow your train of thought.
    I work quite often with one or more workbooks as data source to produce one result.
    I onkly have 2003 working here but have 2007 and 2010 also working at home, but except for special formatiing colors and some extra funcrionality it will work in 2003 and higher.

    I'll see your posting when it's there
     
  4. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
  5. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Okay, I downloaded your file and will take a look at it.
    Don't expect miracles (they take a little longer), I'll do my best
     
  6. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    Your Demo has a problem for me,
    I editted it so it will ask for the xls version, no problem there but it refences unzxisting sheets
    I="NEW 2 TURNS"
    Ik think you should indicate if Sheet1 in "NEW FORM.xlsx" should be renamed to "NEW 2 TURNS" or something else.
    This is needed upon startup to establish the link between the 2 sheets.
     
  7. ozdogs

    ozdogs Thread Starter

    Joined:
    Dec 15, 2009
    Messages:
    31
    yes, sheet 1 in New form would normally be called 2 Turns. that workbook has a number of sheets, but I only copied the first sheet as this is all that is used in the filter, copy & paste process............sorry I didnt rename it
     
  8. Keebellah

    Keebellah Trusted Advisor

    Joined:
    Mar 27, 2008
    Messages:
    6,608
    First Name:
    Hans
    No problem I'll give it a testrun and keep you posted.
    Since I'm almost on the way out I'll work on it later this evening (Ducth Football competition permitting)
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/921375

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice