Button in sheet1 with macro, copied to sheet2 and sheet3, is not working

[ExcelAverage]

Hello guys,

I have this macro code in Sheet1 assigned to a button named Combination. I copied the whole sheet twice and moved to end. So they have the same data content and hopefully the macro in sheet1 will work as well in sheets2 and 3. But it's not. I just copied the code from VB codes Excel objects Sheet1 to Sheet2 and Sheet3. The cursor changed to a reading glass but it doesn't display any result. I am sure it's the worksheets(1).range part that needs to be edited but i am not sure if that is simply changing it to worksheets(2) or worksheets(3). I tried that also but no effect also. Please help.
This is the vb code i used in sheet 1 also from Mrexcel.com

-------------------------------------------------------------------------

Option Explicit

Public Sub ModelPricing_Template()

Dim a As Integer, b As Integer, c As Integer, d As Integer
Dim e As Integer, f As Integer, g As Integer

Worksheets(1).Range("a15:IV65536").ClearContents

For a = 4 To 6
For b = 4 To 7
For c = 4 To 7
For d = 4 To 7
For e = 4 To 13
For f = 4 To 5
For g = 4 To 5


Worksheets(1).Range("A" & Range("A65536").End(xlUp).Row + 1) = Worksheets(1).Cells(a, 1)
Worksheets(1).Range("B" & Range("B65536").End(xlUp).Row + 1) = Worksheets(1).Cells(b, 2)
Worksheets(1).Range("C" & Range("C65536").End(xlUp).Row + 1) = Worksheets(1).Cells(c, 3)
Worksheets(1).Range("D" & Range("D65536").End(xlUp).Row + 1) = Worksheets(1).Cells(d, 4)
Worksheets(1).Range("E" & Range("E65536").End(xlUp).Row + 1) = Worksheets(1).Cells(e, 5)
Worksheets(1).Range("F" & Range("F65536").End(xlUp).Row + 1) = Worksheets(1).Cells(f, 6)
Worksheets(1).Range("G" & Range("G65536").End(xlUp).Row + 1) = Worksheets(1).Cells(g, 7)

Next g
Next f
Next e
Next d
Next c
Next b
Next a

End Sub

-------------------------------------------------------------------------

Thanks in advance again

 

[patel45]

explain better your goal, this is a very bad code.

 

[Snakehips]

ExcelAverage,

The (1) in Worksheets(1) refers to the first tab sheet in your workbook, (2) the second etc. If the sheets you have copied are in tab positions 2 & 3 then altering the code to Worksheets(2) etc should be ok. If however the sheets are not tab 2 & tab 3 it will not work.

I assume that this code will be called from within the sheet it is to compute on ie the selected / active sheet?

If so then try changing all 'Worksheet(1)' references to 'ActiveSheet'


Hope that helps.

 

[ExcelAverage]

Hello Patel,

thanks for the comment, appreciate that. What i wanted to do here is to combine 8 sets of data with different number of elements. This one, For a = 4 To 6, is column A with 3 elements in there starting at row 4. This is like the idea behind cartesian products but having multiple sets and not just two.

Hi Snakehips,

many thanks for your suggestion. Do I need to do the changing to ActiveSheet even to the orginal sheet where i have the codes?

Thanks

 

[ExcelAverage]

Hello again,

Thanks very much for you accommodation to this post, but how about let the macro do the combination on the other two sheets, too? They are exactly alike, just that the first sheet contains description, the next has the price and the third has the code. I can just do the concatenation later.

Thanks for helping me out guys...

 

[ExcelAverage]

Thanks Snakehips,

I think I am all good now. Sheet2 now does the combination, too.

Many thanks again. i will take note of this every time...