Variable Sheet Names

shendik

New Member
Joined
Aug 22, 2011
Messages
15
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
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
 
Upvote 0
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! :beerchug:


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
 
Upvote 0

Forum statistics

Threads
1,224,591
Messages
6,179,767
Members
452,940
Latest member
rootytrip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top