excel macro why don't it work

[kiril0]

Sub datatransfer()


Dim j As Integer
Dim k As Integer
Dim n As Integer




Sheets("Master").Activate
sheetcounter = 2
k = 24
j = 1


Do


Do Until Cells(j, 1).Value = Worksheets(sheetcounter).Cells(15, 5).Value
j = j + 1
Loop


n = j + 1
Do
Worksheets(sheetcounter).Cells(k, 4).Value = Cells(n, 3).Value
Worksheets(sheetcounter).Cells(k, 2).Value = Cells(n, 2).Value
If Cells(n, 8).Value = "" Then
Worksheets(sheetcounter).Cells(k, 31).Value = Cells(n, 6).Value
Else
Worksheets(sheetcounter).Cells(k, 30).Value = Cells(n, 8).Value
Worksheets(sheetcounter).Cells(k, 31).Value = Cells(n, 6).Value

End If
k = k + 1
n = n + 1
Loop While Cells(n, 2).Value <> ""


sheetcounter = sheetcounter + 1
Loop Until sheetcounter = 500
End Sub

 

[jproffer]

It would help to know why it "don't work".

What is it doing, if anything? Is it doing part of what you expect, and then stopping?....or is it doing part of what you expect and then doing something wrong?

One thing I can see as a possibility is "sheetcounter". If that is a string (you don't dimension that one, so it could be), I'm not sure "WorkSheets(sheetcounter)" will do what you expect. And I'm also not overly familiar with the usage of "Do...Loop", but I don't think "Loop While" is the syntax you're wanting. Maybe something like:

While
........
.......
.......
Wend

I think. But like I said, don't take my work for that part as I'm just not that familiar.

 

[kiril0]

I whan to transfer data from sheet "master" to multiple sheets base on the value of Cells(15, 5) in the resiving and the value of cell in the "master" sheet.
The master sheet contains several tables but for each table the content of cell(j,1) is uniquely.
I get run time error 9 for the line

Do Until Cells(j, 1).Value = Worksheets(sheetcounter).Cells(15, 5).Value

 

[mse330]

How many sheets do you have in your workbook ? Do you have 500 or more because the "sheetcounter" loops until 500 which I think is the cause of the error you are getting in case you have less than 500 sheets

Loop Until sheetcounter = 500

 

[kiril0]

How many sheets do you have in your workbook ? Do you have 500 or more because the "sheetcounter" loops until 500 which I think is the cause of the error you are getting in case you have less than 500 sheets

Loop Until sheetcounter = 500

Thanks
the sheets are 494

 

[mse330]

You are welcome ... Glad I could help