Go back to first workbook after activating a second workbook

[VBAProIWish]

Hello All,

The code below copies data from the "2011-03 Green" workbook and pastes it into the "Tea" workbook.

How can I tell the macro to go back to the original workbook which will have a different name every month I run this macro?

For example...THIS month it's "2011-03 Green", but next month the workbook will be named "2011-04 Green"

Sub aaa_test_macro
 
 
Dim Rng As Range
Set Rng = Range("a1").CurrentRegion
Set Rng = Rng.Offset(1, 0)
Set Rng = Rng.Resize(Rng.Rows.Count - 1)
Rng.Copy
 
    Windows("Tea.xlsm").Activate
 
Range("A1").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
 
How can I change the line below to go to the previously activated workbook?
[B][COLOR=red]Windows("2011-03 Green.xlsm").Activate[/COLOR][/B]
 
End Sub

Thanks much!

 

[VoG]

If it is the workbook containing the code, try

ThisWorkbook.Activate

 

[VBAProIWish]

VoG,

No, unfortunately it's not. The workbook will never have the same name either.

 

[VBAProIWish]

Wouldn't there be some code like...

Set activesheet as "originalsheet"

code

code

code


Go back to "originalsheet"?

Just a thought

 

[VoG]

Try

Sub aaa_test_macro()
 
 
Dim Rng As Range
Dim wb As Workbook
Set wb = ActiveWorkbook
Set Rng = Range("a1").CurrentRegion
Set Rng = Rng.Offset(1, 0)
Set Rng = Rng.Resize(Rng.Rows.Count - 1)
Rng.Copy
 
    Windows("Tea.xlsm").Activate
 
Range("A1").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
 
wb.Activate
 
End Sub

 

[VBAProIWish]

Yes yes yes,

That worked! 2 quick things if possible...

The only thing now is that the "tea" WB starts as a hidden WB, but after the macro, it becomes unhidden.

1. Is there a way to keep the WB hidden after the macro?
2. Is there a way to ignore that ENTIRE macro if the "tea" WB is not found?

This macro would be perfect then


Thanks!

 

[VoG]

Maybe:

Sub aaa_test_macro()
 
 
Dim Rng As Range
Dim wb As Workbook
Set wb = ActiveWorkbook
Set Rng = Range("a1").CurrentRegion
Set Rng = Rng.Offset(1, 0)
Set Rng = Rng.Resize(Rng.Rows.Count - 1)
Rng.Copy
On Error GoTo puke
    Windows("Tea.xlsm").Activate
 
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
puke:
 
wb.Activate
ActiveWindow.Visible = False
End Sub

 

[VBAProIWish]

Ahh, maybe my fault, it's hiding the original workbook, not the "Tea" workbook.

 

[VoG]

Sorry, I misunderstood. Try

Sub aaa_test_macro()
 
 
Dim Rng As Range
Dim wb As Workbook
Set wb = ActiveWorkbook
Set Rng = Range("a1").CurrentRegion
Set Rng = Rng.Offset(1, 0)
Set Rng = Rng.Resize(Rng.Rows.Count - 1)
Rng.Copy
On Error GoTo puke
    Windows("Tea.xlsm").Activate
 
Range("A1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
Windows("Tea.xlsm").Visible = False
puke:
 
wb.Activate
End Sub

 

[VBAProIWish]

Purrrrrrrrrfection!

Once again VoG to the rescue!

ThanksThanks​