Column of Data Copied to Separate Worksheets 1

[shelby55]

Hi

I have a workbook that Skip helped lots with where I have one sheet of data that becomes many sheets (each row of data on the "rawdata" worksheet becomes a single worksheet with the tab showing as the caseno).

I now have to replace a field already on these worksheets but can't just re-run the macro because data has already been entered in different cells.

How would I take this column of data (the replacement data) and map to plunk into each worksheet that matches the caseno?

Thanks.

 

[SkipVought]

Here is my test data from my rawdata2 sheet
[tt]
caseno caseval
Sheet2 x
Sheet3 y
[/code]
and my results were that x is in B30 on Sheet2 and y is in B30 on Sheet3.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Using the code you provided:
[code[Sub CorrectData()
Dim r As Range

With Sheets("rawdata2")
'for each caseno, ASSUMING that HEADERS are in rows 1 & DATA starts in ROW 2
For Each r In .Range(.Cells(2, "A"), .Cells(2, "A").End(xlDown))
Sheets(r.Value).[B30].Value = r.Offset(0, 1).Value
Next

End With

End Sub[/code]

results in the subscript out of range error message on the line before Next.

Also, in your example, is the name on the tab sheet2 because as I mentioned, mine shows as being sheet2 with the actual name being RawData_A i.e. sheet2(RawData_A).

 

[SkipVought]

Where is the data I asked you to post?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Try this modification...

Sub CorrectData()
    Dim r As Range
    
    With Sheets("rawdata2")
        'for each caseno, ASSUMING that HEADERS are in rows 1 & DATA starts in ROW 2
        For Each r In .Range(.Cells(2, "A"), .Cells(2, "A").End(xlDown))
            Sheets([b]Trim(r.Value)[/b]).[B30].Value = r.Offset(0, 1).Value
        Next
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Still get the same error message, Skip.

The data on rawdata2 is:
CaseNo Data
1 9
2 9
3 9
4 9
5 9
6 8
7 8
8 8
9 8
10 0

 

[PHV]

Why is column A a number instead of a sheet name ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

So you have sheets named
"1", "2", "3", "4", "5", "6", "7", "8", "9", "10" etc???

REALLY?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I understand completely!

These numbers look like INDEXES to the sheets, not Sheet NAMES!!!

Sheets([b]CStr(r.Value)[/b]).[B30].Value = r.Offset(0, 1).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

In other words...
[tt]
Sheets(1) [IS NOT EQUIVALENT TO] Sheets("1")
[/tt]
Sheets(1) is the FIRST sheet in the workbook.

Interestingly the sheets above were in consecutive order in the workbook ...

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}