Definire si utilizare PrevSheet & ActiveSheet pentru SUMIF in macro VBA

2.88K viewsIntrebari
0

Salutare!

Ma confrunt cu o dilema legata de un excel buclucas…incerc sa scriu in VBA un cod prin care sa imi faca un sumif pe active sheet dintr-un sheet precedent (indiferent care ar fi denumirea lui).

Marea provocare este sa gasesc o solutie prin care sa nu folosesc in interiorul formulei denumiri absolute de sheet-uri (Sheet1, Sheet2 etc), ci sa fac o formula cu PrevSheet si cu ActiveSheet.

0

Alexandra

“Usoara intrebare” 🙂 Vezi articolul de aici:

http://www.globaliconnect.com/excel/index.php?option=com_content&view=article&id=189:excel-vba-worksheets-refer-activate-or-select-add-a-name-copy-or-move-hide-or-display-delete-page-layout-a-view-calculate&catid=79&Itemid=475

Acolo am gasit aceasta functie:

Example 1 – select single or multiple worksheets:

Sub Worksheets_Select()
‘select single or multiple worksheets – consider a workbook containing 3 worksheets, namely “Sheet1”, “Sheet2” & “Sheet3” in the order of left to right.

‘selects and activates the second worksheet (“Sheet2”) – the previous selection is replaced so this is the only selected worksheet:
ActiveWorkbook.Worksheets(2).Select
‘returns the active worksheet (“Sheet2”):
MsgBox ActiveWorkbook.ActiveSheet.Name

‘select multiple worksheets in the active workbook, using an array of worksheet names – the first worksheet in the array (“Sheet3”) becomes the active worksheet:
ActiveWorkbook.Sheets(Array(“Sheet3”, “Sheet1”)).Select
‘returns the active worksheet (“Sheet3”):
MsgBox ActiveWorkbook.ActiveSheet.Name

‘selects and activates the second worksheet (“Sheet2”) – the previous selection is replaced so this is the only selected worksheet:
ActiveWorkbook.Worksheets(“Sheet2”).Select
‘returns the active worksheet (“Sheet2”):
MsgBox ActiveWorkbook.ActiveSheet.Name

‘select multiple worksheets – all worksheets except last (this selects the worksheets “Sheet1” & “Sheet2”):
Dim i As Integer
For i = 1 To ThisWorkbook.Worksheets.count – 1
ActiveWorkbook.Worksheets(i).Select (False)
Next i
‘returns the active worksheet (“Sheet2”) – this was the active worksheet before the For…Next loop:
MsgBox ActiveWorkbook.ActiveSheet.Name

End Sub