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

  1.
    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
    1. 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
    2. 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
    3. 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
    4. Dobynette
       
      I succeeded by simply assigning an arbitrary name to ActiveSheet and then using the commands name.Activate and name.Select.
      0