I've written the attached code and can't figure out what i'm doing wrong. I'm trying to copy, sort, and paste information from a bunch of sheets. The below code goes into a death spiral copying and pasting only from the first sheet in my workbook. What's wrong with my code? Why won't it continue through the loop?
Sub Compile()
Dim sh As Worksheet
Dim UKsh As Worksheet
Dim EUsh As Worksheet
Dim USsh As Worksheet
Dim EUrow As Long, UKrow As Long, USrow As Long
Dim x As Long, y As Long
EUrow = 2
UKrow = 2
USrow = 2
Set UKsh = Sheets("UK")
Set EUsh = Sheets("EU")
Set USsh = Sheets("US")
For Each sh In Worksheets
If Range("a60") = "EU" Then
ActiveSheet.Range("C8:C48").Copy
EUsh.Range("A" & EUrow).PasteSpecial Transpose:=True
Application.CutCopyMode = False
EUrow = EUrow + 1
ElseIf Range("a60") = "UK" Then
ActiveSheet.Range("C8:C48").Copy
UKsh.Range("A" & UKrow).PasteSpecial Transpose:=True
Application.CutCopyMode = False
UKrow = UKrow + 1
Else
ActiveSheet.Range("C8:C48").Copy
USsh.Range("A" & USrow).PasteSpecial Transpose:=True
Application.CutCopyMode = False
USrow = USrow + 1
End If
Next
End Sub
Bookmarks