Select previous sheet Macro

[Leo7724]

Hi,

This is a very easy macro for you guys I'm sure. I'm stuck though.

Basically, on the rebuy shipping tab I want to 'create a copy and move to end'. I then want to return to the original tab and copy and paste special values. I then want to rename this (first) tab 'rebuy ''todays date'' ' - I then want this sheet to be moved, create copy, new workbook.

This can easily be recorded I hear you say!! However, when I want to do another rebuy (meaning I want to do the whole process again using the recently created tab) it won't work as the recording method is always searching for 'rebuy shipping' tab which no longer exists (it is now called rebuy todays date'.

Do I need to insert ActiveSheet.previous into this macro so that it now tries copying the previous tab instead of that very first one? Please find it attached.

Oh and just for good measure, I also need the IPQ NOW value to be transferred to the IPQ WAS cell of the new tab.

Is it doable? Please help! Thanks so much.

[venkat1926]

try this macro every time you have fresh data . new data sheet must be "rebuy shipping"

Please Login or Register  to view this content.

[Leo7724]

Hi Venkat, Thanks for that however it doesnt seem to work.

Really stuck now. All I need is to be able to copy a tab (move to end) - revert back to the tab you just copied, copy all the data in there and paste special values, rename the tab to todays date.

Sounds simple I just can't work out how to keep it all relative to the latest tab!

[Leo7724]

try this macro every time you have fresh data . new data sheet must be "rebuy shipping"

Please Login or Register  to view this content.

Hi,

I have almost fixed the copying over issue it was a case of changing the sheet name to 'active sheet'

Sub macrorebuy1()
'
' macrorebuy1 Macro
'
' Keyboard Shortcut: Ctrl+k
'
ActiveSheet.Select
ActiveSheet.Copy Before:=Sheets(4)
ActiveSheet.Previous.Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E2").Select
Application.CutCopyMode = False
Selection.FillDown
End Sub

However, the newly copied sheet isnt always moving to the end, any ideas?

Also, the second part (where I have coded 'active.previous.select', it is not doing what I want (ie moving back to the sheet we've just copied from and copy pasting special -

I think I'm almost there though!