Variable Sheet Names

[shendik]

I am working on a macro that enables automation of data collection from different sheets into one sheet in the same workbook.

I think I have the code as it should be in logic, but there are inconsistenicies in the syntax. I have highlighted these two lines in red below.

I tried to help explain what I was doing via comments in the code below. Please help me modify the two lines in red so that they are correct in syntax and use the information in the productgroup variable as a sheet name. I might just be missing a couple of apostrophes... I am not sure... I tried to play with it but couldn't figure it out.

Please let me know if you need any clarifcations. Thanks in advance!!

Sub BudgetExpense()
'
' This macro consolidates data from different product groups stored in separate sheets into one sheet

' Defining variable productgroup which will store the first product name in a list of products without blanks. This first product name will also be the product group name of the list of products and are stored in the sheet name titled the first product.

Dim productgroup As String

' i is going to be the number of rows
For i = 3 To 100

'If the current cell is not blank but the previous cell is blank then
If Cells(i, 1) <> "" And Cells(i - 1, 1) = "" Then
productgroup = Cells(i, 1)

' j is going to be the number of columns. I would like skip every column which is why I am incrementing j by 2
For j = 3 To 25
Cells(i,j) = sumifs("productgroup"!$D:$D,"productgroup"!$C:$C,Cells(2,j),"productgroup"!$BA:$BA,2012)
j = j + 1
Next j

'If the current and previous cell are not blank then the sheet name would still be the same as the information stored in productgroup as in the earlier case

ElseIf Cells(i, 1) <> "" And Cells(i - 1, 1) <> "" Then
For j = 3 To 25
Cells(i,j) = sumifs("productgroup"!$D:$D,"productgroup"!$C:$C,Cells(2,j),"productgroup"!$BA:$BA,2012)
j = j + 1
Next j
End If

' For all other cases I would like to calculate nothing but to skip to the next row

Next i
End Sub

 

[shendik]

Okay so I managed to modify the code so that I don't get syntax errors as before. Below is the macro code with the updated code for the two red lines.

I am still getting an error stating that the type-declaration character does not match declared data type for the "productgroup" variable.

Am I not supposed to define the variable as string? It can contain both numbers and letters. Please let me know.

Thanks.

Sub BudgetExpense()
'
' This macro consolidates data from different product groups stored in separate sheets into one sheet

' Defining variable productgroup which will store the first product name in a list of products without blanks. This first product name will also be the product group name of the list of products and are stored in the sheet name titled the first product.

Dim productgroup As String

' i is going to be the number of rows
For i = 3 To 100

'If the current cell is not blank but the previous cell is blank then
If Cells(i, 1) <> "" And Cells(i - 1, 1) = "" Then
productgroup = Cells(i, 1)

' j is going to be the number of columns. I would like skip every column which is why I am incrementing j by 2
For j = 3 To 25
Cells(i, j) = "= sumifs(" & productgroup! & " $D:$D," & productgroup! & "$C:$C,Cells(2,j)," & productgroup! & "$BA:$BA,2012)"
j = j + 1
Next j

'If the current and previous cell are not blank then the sheet name would still be the same as the information stored in productgroup as in the earlier case

ElseIf Cells(i, 1) <> "" And Cells(i - 1, 1) <> "" Then
For j = 3 To 25
Cells(i, j) = "= sumifs(" & productgroup! & " $D:$D," & productgroup! & "$C:$C,Cells(2,j)," & productgroup! & "$BA:$BA,2012)"
j = j + 1
Next j
End If

' For all other cases I would like to calculate nothing but to skip to the next row

Next i
End Sub

 

[shendik]

I figured it out!! I am posting the solution just in case anybody has the issue in the future.

Aside from changing the syntax, I had to tweak the formula a bit since it wasn't logically sound either. The code below works!


Sub Budgetexpense()
'
' This macro easily organizes the data from different product group tabs
Dim productgroup As String
Dim i As Integer, j As Integer
Sheets("Budgeted Exp").Select
For i = 3 To 95
If Cells(i, 1) <> "" And Cells(i - 1, 1) = "" Then
productgroup = Cells(i, 1)
Cells(4, 30) = productgroup
For j = 3 To 25
'Cells(i, j) = "= sumifs('productgroup'! $D:$D, 'productgroup'! $C:$C, Cells(2,j), 'productgroup'! $BA:$BA,2012)"
Cells(i, j) = "= sumifs(" & productgroup & "!" & " $D:$D," & productgroup & "!" & "$C:$C, $A" & i & ", " & productgroup & "!" & " $I:$I, " & Cells(2, j) & "," & productgroup & "!" & " $BA:$BA," & Cells(i, 28) & ")"
j = j + 1
Next j

ElseIf Cells(i, 1) <> "" And Cells(i - 1, 1) <> "" Then
For j = 3 To 25
'Cells(i, j) = "= sumifs('productgroup'! $D:$D, 'productgroup'! $C:$C, Cells(2,j), 'productgroup'! $BA:$BA,2012)"
'Cells(i, j) = "= sumifs(" & productgroup & "!" & " $D:$D," & productgroup & "!" & "$C:$C, " & Cells(2, j) & "," & productgroup & "!" & "$BA:$BA,2012)"
Cells(i, j) = "= sumifs(" & productgroup & "!" & " $D:$D," & productgroup & "!" & "$C:$C, $A" & i & ", " & productgroup & "!" & " $I:$I, " & Cells(2, j) & "," & productgroup & "!" & " $BA:$BA," & Cells(i, 28) & ")"
j = j + 1
Next j
End If
Next i
End Sub