View original (French)

Macro, select the active sheet.

Solved
Dobynette -  
 Dobynette -
Hello everyone

I have been trying for several days to adapt a macro for my file, but I can't find the solution.
I created a planning file with several tabs for each month, and then tabs summarizing the presence of each person at each position for tracking purposes.
On each month tab, I have a table with the number of days of presence for each position by person, and a button with a macro that allows me to add these columns to the summary table by position.

What I am looking to do is to have a single macro regardless of the month tab from which I run it. But currently, I haven't found anything that works to replace the "Sheets("January").Select" in my macro.

Does anyone have a solution to designate the sheet from which the macro launch button was clicked?

Thanks in advance :)

2 answers

Answer 1 / 2
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
 
Hello
maybe simply (and subject to your macro), replace:

Sheets("January").select

with

ActiveSheet.select

cheers

--
The quality of the answer mainly depends on the clarity of the question, thank you!
0
Dobynette
 
Hello

Thank you for your reply
I had tried this command but the macro did absolutely nothing.

Here is the macro. Maybe something is missing. 

Sub Suivi()
'
' Follow Macro
'


 Sheets("URG").Select
 Columns("D").Select
 Range("D2").Activate
 Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
 Sheets("January").Select
 Range("B50:B67").Select
 Selection.Copy
 Sheets("URG").Select
 Range("D1").Select
 Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
 , SkipBlanks:=False, Transpose:=False
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 Columns("D:D").ColumnWidth = 5
End Sub


This code repeats identically for 4 copy/paste in different tabs.
0
Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
 
I don't soar very high in macro, but I think that if you start by selecting the URG sheet, ActiveSheet.Select will then necessarily refer to that sheet!

See if you can start your macro with:

ActiveSheet.Select
Range("B50:B67").Select
Selection.Copy

Then select the URG sheet and delete below the lines that refer to the January copy?

It seems to me that the actions you are taking before copying January can be done afterwards, right?

Otherwise, you'll have to wait for someone better at VBA, and that's probably not rare here :-))

Sincerely
0
Dobynette
 
Re!

I had set up the macro this way because I couldn't insert the cells while keeping the formatting all at once, but now I've succeeded and it works well! However, I'm missing a line that allows me to return to the original sheet after the first copy/paste, still without designating it by its name. I'll try to find this new solution :) Thank you!! 

Sub Suivi2()
'
' Suivi2 Macro
'

'
 ActiveSheet.Select
 Range("B50:B66").Select
 Selection.Copy
 Sheets("URG").Select
 Columns("D").Select
 Range("D2").Activate
 Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 Range("D1").Select
 Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
 , SkipBlanks:=False, Transpose:=False
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 
 ActiveSheet.Select
 Range("C50:C66").Select
 Selection.Copy
 Sheets("LCT").Select
 Columns("D").Select
 Range("D2").Activate
 Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
 Range("D1").Select
 Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
 , SkipBlanks:=False, Transpose:=False
 Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 End Sub
0
Dobynette
 
I succeeded by simply assigning an arbitrary name to ActiveSheet and then using the commands name.Activate and name.Select.
0