Make One Macro Out of Two

[DaleKeel]

I would like to have one macro instead of calling to another. I just do not know how to do it.

Thanks For Your Time


Sub Copy_Paste_two_rows()
With Sheets("XXX")
If Range("C7") = "BTC-LTC" Then
.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A7:J7").Value
ElseIf Range("C7") = "BTC-ENJ" Then
.Range("N" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A7:J7").Value
End If
If Range("C8") = "BTC-LTC" Then
.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A8:J8").Value
ElseIf Range("C8") = "BTC-ENJ" Then
.Range("N" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A8:J8").Value
End If

Call XXX

End With
End Sub
-----------------------------------------------------------

Sub XXX()
'
' XXX Macro
'
'
Range("M5").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=46
Sheets("XXX").Select
Range("C1").Select
End Sub

 

[Trebor76]

Hi DaleKeel,

Maybe this:

Option Explicit
Sub Copy_Paste_two_rows()
    
    Application.ScreenUpdating = False
    
    With Sheets("XXX")
        If Range("C7") = "BTC-LTC" Then
            .Range("A" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A7:J7").Value
        ElseIf Range("C7") = "BTC-ENJ" Then
            .Range("N" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A7:J7").Value
        End If
        If Range("C8") = "BTC-LTC" Then
            .Range("A" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A8:J8").Value
        ElseIf Range("C8") = "BTC-ENJ" Then
            .Range("N" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A8:J8").Value
        End If
        ActiveWindow.ScrollWorkbookTabs Sheets:=46
        Range("C1").Select
    End With
    
    Application.ScreenUpdating = True
    
End Sub

Regards,

Robert

 

[DaleKeel]

The script continued to work but I guess I moved the location of the tab so the curser does not go to that page. Could you allow me the choice of what sheet opens up when the macro is activated. Thanks again for your time.

 

[Trebor76]

This line of code...

With Sheets("XXX")

...governs that the code is run from the tab XXX. Change that line to be which tab name you want the code to run from.

 

[DaleKeel]

I sorry I was not clear enough and I really did not understand what your additional code did until now. The macro does work, preform an action, on sheet "XXX". But I am executing the macro from a different sheet. The code you changed places the curser in C1 on the sheet I execute the macro from. I wanted XXX sheet to become visible and the curser to be in cell C1 of sheet XXX so that I could see what the macro did on sheet XXX.
Sorry I did/do not have the correct words to get my point across.
I do appreciate your time and response. Thanks

 

[DaleKeel]

I think I have figured it out. May not be the most effective but it works. I am up for further suggestions.

Sub Copy_Paste_two_rows()

Application.ScreenUpdating = False

With Sheets("XXX")
If Range("C7") = "BTC-LTC" Then
.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A7:J7").Value
ElseIf Range("C7") = "BTC-ENJ" Then
.Range("N" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A7:J7").Value
End If
If Range("C8") = "BTC-LTC" Then
.Range("A" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A8:J8").Value
ElseIf Range("C8") = "BTC-ENJ" Then
.Range("N" & Rows.Count).End(xlUp)(2).Resize(1, 10).Value = Range("A8:J8").Value
End If
Worksheets("XXX").Activate
Range("C1").Select
End With

Application.ScreenUpdating = True

End Sub

 

[Trebor76]

I think I have figured it out. May not be the most effective but it works.

That's great

I am up for further suggestions.

Maybe just change this line...

Worksheets("XXX").Activate

...to this:

.Activate

Regards,

Robert

 

[DaleKeel]

Thanks again.