loop to look up value in column a cant start on sheet number 2

[jacinthn]

im having trouble with this loop i want this macro to look up the values in column a and return the coresponding values from column G of the match sheet to column f of my current worksheet then loop that through all the sheets in the workbook but do it for all the values in column A not just 1
i keep getting an error on 2 to sheets.count type mismatch... i dont know how to fix this help ??


For wbs = 2 To Sheets.Count
For Each wbs In ActiveWorkbook.Worksheets
wbs.Activate
y = 3
x = 2
Do
x = x + 1
If Range("A3") = ActiveWorkbook.Sheets("MASTER").Range("A" & x) Then
Range("F" & y) = ActiveWorkbook.Sheets("MASTER").Range("G" & x).Value
y = y + 1
End If
Loop Until x = 3000
Next sht
Next

 

[Sthrncali]

You were close... I didn't check the rest of the code but the below change will get you past the type mismatch error you were receiving.

im having trouble with this loop i want this macro to look up the values in column a and return the coresponding values from column G of the match sheet to column f of my current worksheet then loop that through all the sheets in the workbook but do it for all the values in column A not just 1
i keep getting an error on 2 to sheets.count type mismatch... i dont know how to fix this help ??


For wbs = 2 To WorkSheets.Count
For Each wbs In ActiveWorkbook.Worksheets
wbs.Activate
y = 3
x = 2
Do
x = x + 1
If Range("A3") = ActiveWorkbook.Sheets("MASTER").Range("A" & x) Then
Range("F" & y) = ActiveWorkbook.Sheets("MASTER").Range("G" & x).Value
y = y + 1
End If
Loop Until x = 3000
Next sht
Next

 

[jacinthn]

thanks so much!!!

 

[jacinthn]

tried that still getting type mismatch on that line i have no idea whats wrong with excel today

 

[Sthrncali]

im having trouble with this loop i want this macro to look up the values in column a and return the coresponding values from column G of the match sheet to column f of my current worksheet then loop that through all the sheets in the workbook but do it for all the values in column A not just 1
i keep getting an error on 2 to sheets.count type mismatch... i dont know how to fix this help ??


For wbs = 2 To Sheets.Count
For Each wbs In ActiveWorkbook.Worksheets
wbs.Activate
y = 3
x = 2
Do
x = x + 1
If Range("A3") = ActiveWorkbook.Sheets("MASTER").Range("A" & x) Then
Range("F" & y) = ActiveWorkbook.Sheets("MASTER").Range("G" & x).Value
y = y + 1
End If
Loop Until x = 3000
Next sht
Next

Try this change:

For x = 2 To Worksheets.Count
Worksheets(x).Activate

y = 3
x = 2

Do
x = x + 1
If Range("A3") = ActiveWorkbook.Sheets("MASTER").Range("A" & x) Then
Range("F" & y) = ActiveWorkbook.Sheets("MASTER").Range("G" & x).Value
y = y + 1
End If
Loop Until x = 3000

Next