[excel vba] Problem assigning a macro to a button
Hello everyone,
In a few words, I learned to code VBA late. It's not always very clean but I'm managing. That said, I have a recurring problem: on some of my macros, when I want to assign it to a button (for an Excel sheet), "this formula is too complicated to be assigned to an object." Sometimes it works, sometimes it doesn't (most of the time). Note that the macro runs very well when I execute it from the VBA page (F5). I don't understand...
Example of this very simple macro: it consolidates the following sheets into the "Recap" sheet
Sub Synth()
Sheets("Recap").Select
EndIndicCode = Range("B1000").End(xlUp).Row
j = 5
For i = 0 To EndIndicCode - 17
sheet = Sheets("recap").Range("c17").Offset(i, 0).Value
Sheets(sheet).Select
Range("A:B").Select
Selection.Copy Destination:=Sheets("Synth").Cells(1, j)
Application.CutCopyMode = False
j = j + 3
Next i
Sheets("Synth").Range("A1").Select
End Sub
I’ve scoured the forums before asking my question.
Thank you (very much!) in advance.
P.S./This was my first question on Comment Ça Marche, and on a forum in general!
In a few words, I learned to code VBA late. It's not always very clean but I'm managing. That said, I have a recurring problem: on some of my macros, when I want to assign it to a button (for an Excel sheet), "this formula is too complicated to be assigned to an object." Sometimes it works, sometimes it doesn't (most of the time). Note that the macro runs very well when I execute it from the VBA page (F5). I don't understand...
Example of this very simple macro: it consolidates the following sheets into the "Recap" sheet
Sub Synth()
Sheets("Recap").Select
EndIndicCode = Range("B1000").End(xlUp).Row
j = 5
For i = 0 To EndIndicCode - 17
sheet = Sheets("recap").Range("c17").Offset(i, 0).Value
Sheets(sheet).Select
Range("A:B").Select
Selection.Copy Destination:=Sheets("Synth").Cells(1, j)
Application.CutCopyMode = False
j = j + 3
Next i
Sheets("Synth").Range("A1").Select
End Sub
I’ve scoured the forums before asking my question.
Thank you (very much!) in advance.
P.S./This was my first question on Comment Ça Marche, and on a forum in general!
5 answers
-
Just a small clarification: Excel 2010 :)
-
ContributorHello,
You tell us that you want to send to the "recap" sheet, but in your code, you are sending copies to the "synth" sheet?
You also say it combines the tabs, yet in your code, you are only sending one tab named "feuille"... would there then be a button in each sheet?....
Waiting for your response...
Michel -
Hello Michel,
Thank you for your response. A few clarifications:
- In the "recap" tab, in C17:C60: there are the names of all the tabs.
- The sheet variable allows selecting them one by one.
- In order to copy columns A:B and then paste them into the "Synth" tab one after the other (basically in F:G for tab 3, then in I:J for tab 4...).
The button would therefore be only on "recap" or on "synth", I don't mind :)
Sorry for not being clearer from the start.
Have a great day and thank you again. -
ContributorHello
Some lines posed comprehension issues for me (question marks)
returns the values of columns A B from the sheets in "synth"; suggestion:Option Explicit
Work template:
'-------------------------------------------
Sub Synthesize()
Dim EndIndicCode As Integer, RowIndex As Integer, SheetName As String
Dim LastRow As Integer, Col As Integer, T_sheet()
'Screen frozen
Application.ScreenUpdating = False
With Sheets("recap")
EndIndicCode = .Range("B1000").End(xlUp).Row
Col = 5
For RowIndex = 17 To EndIndicCode '???? did not understand the -17
SheetName = .Cells(RowIndex, "C")
With Sheets(SheetName)
LastRow = .Cells.Find("*", , , , , xlPrevious).Row
T_sheet = .Range("A1:B" & LastRow).Value
Sheets("Synth").Cells(1, Col).Resize(UBound(T_sheet), 2) = T_sheet
Col = Col + 3 '???? +2 if adjacent ....
End With
Next
End With
Sheets("Synth").Range("A1").Select
End Sub
https://www.cjoint.com/?3Bch3xUCyLK
--
Michel -
Hello Michel,
I should have attached the file from the start.
Strangely, I can create a button with your file but not with mine (even when putting your macros in my file).
Here is the attached file, I had used the example of the macro A3_OngletSynth. But it also applies to A2_MajData for instance. (And this is despite the fact that other macros in the file are accessed through a button -located in the Recap tab-).
https://www.cjoint.com/?0BcteDPtf8n
Thank you for everything Michel.
Have a great end of the day.
Best regards, Thomas.