Simple (I think) problem

I am trying to write a macro where another worksheet has formulas copied to the active worksheet. Ideally I would like to loop it as well but I need to be able to come back to the first worksheet after referencing the one that has formulas copied from it. Haven't got that far yet.
Here's what I have tried;

Columns("A:A").Select
' did try sheet=activesheet here but gave error
Application.Goto Reference:="R5C1"
Selection.EntireColumn.Hidden = False
Sheets("Training Plan").Select
Columns("A:A").Select
Selection.Copy
Sheets("Skills Last Week").Select This is where I need code making it return to the original sheet instead of just the sheet llisted here
Columns("A:A").Select
ActiveSheet.Paste
Selection.EntireColumn.Hidden = True
End Sub

I might be able to reference it to a list of sheets and loop through those which would be ideal, however I still need to be able to get from the copied sheet to the active one.

Thanks

Hi,

If I understand correctly, since your are starting from the "source" sheet, you only need to

Dim Sht As Worksheet
Set Sht=ActiveSheet

and then use Sht in your code ...

HTH
Cheers
Carim

It is a bit hard to work out what you are trying to do from your post. A couple of concepts may help however.

Firstly, you don't need to select a sheet to access it.

ie

Columns("A:A").Select
Selection.Copy

works much better if you simply do this
Columns("A:A").Copy

Better still, just make the destination range = the source range. Here is a simplistic example

Please Login or Register  to view this content.

Well I actually bit the bullet and did it manually before I got these answers.
Couple of thoughts on the answers though.
'If I understand correctly, since your are starting from the "source" sheet, you only need to
Dim Sht As Worksheet
Set Sht=ActiveSheet
and then use Sht in your code ...'

I was actually starting from the destination sheet, otherwise I would have to change the code every time I did it with the next sheet. (There were around 30 sheets). But this may work.

'Columns("A:A").Select
Selection.Copy
works much better if you simply do this
Columns("A:A").Copy
I think that shows how advanced I am...lol.

Better still, just make the destination range = the source range. Here is a simplistic example
Sub copyme()
Workbooks("book2.xls").Sheets("sheet1").Columns(3).Value = Workbooks("book3.xls").Sheets("sheet1").Columns(3).Value
End Sub'

It appears the above is copying from one workbook to another...I only needed one sheet to another, easy enough changed there. However my problem was I was trying to do this on many sheets. With that code it would have to be changed each time you wanted to do a different sheet, unless I am reading it wrong.

Thanks for trying guys, I would still be interested to know how to do it with a loop to go through sheets if anyone can.