Results 1 to 7 of 7

Thread: Combing Macros For Copying Ranges

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    33

    Combing Macros For Copying Ranges

    Good Afternoon -

    Macro 1 copies a range and inserts it into the row I have highlighted and shifts down. Works great.
    Macro 2 copies a range and inserts it into the row I have highlighted and shifts down. It Calls another macro, M_Highlight_All_Options to hightlight 60 tabs and do the same thing. Works great.

    Macro 3 is my attempt to combine the two. Whats happening is it will work for Macro 1, but when it gets to Macro 2, it doesnt retain the row of the active cell I was in. Would there be a way to log that row and be able to use it throughout both Macros? i'd also like to combine this all into one macro if possible, using the "call" is kind of my attempt to do so with my low level of macro writing.

    Thank you!

    Code:
    Sub Macro1()
    
    Dim ws As Worksheet
    
    Set ws = Sheets("INTERNAL")
    
    If ActiveCell.Column = 2 Then
    ws.Range("INTERNAL_DOOR").Copy
    'Call M_Highlight_All_Options
    ActiveCell.Insert Shift:=xlDown
    Application.CutCopyMode = False
    'Call M_Unhighlight_All_Options
    Else
        MsgBox "YOU'RE NOT IN COLUMN B"
        End If
    End Sub
    
    Sub Macro2()
    
    Dim ws As Worksheet
    
    Set ws = Sheets("INTERNALO")
    
    If ActiveCell.Column = 2 Then
    ws.Range("INTERNALO_DOOR").Copy
    Call M_Highlight_All_Options
    ActiveCell.Insert Shift:=xlDown
    Application.CutCopyMode = FalseA
    Call M_Unhighlight_All_Options
    Else
        MsgBox "YOU'RE NOT IN COLUMN B"
        End If
    End Sub
    Sub Macro3()
    
    
    Call Macro1
    Call Macro2
    
    End Sub

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,621

    Re: Combing Macros For Copying Ranges

    you can use the address of the activecell to return to it like

    Code:
    Sub Macro1()
    
    Dim ws As Worksheet
    
    Set ws = Sheets("INTERNAL")
    
    If ActiveCell.Column = 2 Then
    aa = ActiveCell.Address
    ws.Range("INTERNAL_DOOR").Copy
    'Call M_Highlight_All_Options
    ActiveCell.Insert Shift:=xlDown
    Application.CutCopyMode = False
    'Call M_Unhighlight_All_Options
    Range(aa).Offset(1).Select
    Else
        MsgBox "YOU'RE NOT IN COLUMN B"
        End If
    End Sub
    you can change the offset as required, as posted it should select the cell 1 row below the starting row, it is possible that you may need to save the address after inserting, especially if you are pasting multiple rows, do some testing
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  3. #3

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    33

    Re: Combing Macros For Copying Ranges

    I think this is working - does this check out??

    Code:
    Sub Macro4()
    
    Dim ws1 As Worksheet
    Set ws1 = Sheets("INTERNAL")
    
    
    Dim ws2 As Worksheet
    Set ws2 = Sheets("INTERNALO")
    
    
    If ActiveCell.Column = 2 Then
    aa = ActiveCell.Address
    ws1.Range("INTERNAL_DOOR").Copy
    'Call M_Highlight_All_Options
    ActiveCell.Insert Shift:=xlDown
    Application.CutCopyMode = False
    'Call M_Unhighlight_All_Options
    Range(aa).Offset(1).Select
    Else
        MsgBox "YOU'RE NOT IN COLUMN B"
        End If
    
    If ActiveCell.Column = 2 Then
    'aa = ActiveCell.Address
    Range(aa).Offset(1).Select
    ws2.Range("INTERNALO_DOOR").Copy
    Call M_Highlight_All_Options
    ActiveCell.Insert Shift:=xlDown
    Application.CutCopyMode = False
    Call M_Unhighlight_All_Options
    
    Else
        MsgBox "YOU'RE NOT IN COLUMN B"
        End If
    
    End Sub

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,621

    Re: Combing Macros For Copying Ranges

    looks like what i was suggesting, does it work as desired?
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5

    Thread Starter
    Member
    Join Date
    Jul 2014
    Posts
    33

    Re: Combing Macros For Copying Ranges

    it seems to be working but i changed the last Range(aa).Offset(1).Select to Range(aa).Offset(0).Select

    how do I add an if statement to the beginning? getting errors.

    Code:
    If ActiveSheet.Name <> "EXPORT" Then MsgBox "wrong sheet"
    
    Else
    I'd like it to end the macro if you're not on the EXPORT tab, otherwise continue with the macro.

  6. #6
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,140

    Re: Combing Macros For Copying Ranges

    Code:
    If ActiveSheet.Name <> "EXPORT" Then
        MsgBox "Wrong sheet to Export!", vbOKOnly, "Error"
        Exit Sub
    End If

  7. #7
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,621

    Re: Combing Macros For Copying Ranges

    it seems to be working but i changed the last Range(aa).Offset(1).Select to Range(aa).Offset(0).Select
    i thought you might need to move down a row, if not offset(0) can be omitted
    Code:
    Range(aa).Select
    i would, as far as possible, avoid working with active anything, also avoid selecting ranges or working with the selection object, instead work with full qualified ranges
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Click Here to Expand Forum to Full Width