Trying to copy dynamic column to dynamic location using pastespecial

[lk4772]

Hello, I'm have a workbook that I have several sheets in for users to enter data that I turn into SQL Insert statements. I'm now wanting to copy all the SQL Insert statements to 1 Sheet titled "SQL" but I'm not able to get the PasteSpecial to work. Nothing ever pastes. Below is my code - any help would be great. TIA! :D



Public Sub CopySQL()


On Error Resume Next

Dim ws As Worksheet
Dim strNm As String
Dim colX As String
Dim intY As Integer
Dim y As String
Dim r As Range
Dim r2 As Range
Dim x As Integer

For Each ws In Worksheets
If ws.Name <> "SQL" And Right(ws.Name, 6) <> "tables" And ws.Visible = xlSheetVisible Then

ws.Activate
strNm = ws.Name

'find column where insert statements start
Range("P1").Activate
If Left(ActiveCell.Value, 6) <> "INSERT" Then
Range("V1").Activate
If Left(ActiveCell.Value, 6) <> "INSERT" Then
Range("AI1").Activate
End If
End If

'if values are there, copy column
If ActiveCell.Offset(1, 0).Value <> "" Then

'get current cell address and range
y = ActiveCell.Offset(1, 0).Address
Set r = Range(y, Range.End(xlDown))
r.Copy
x = r.Count

'get cell to paste to on SQL sheet
Sheets("SQL").Activate
Range("A1").Activate

'check if a1 cell already has statement and move to bottom of column if it does
If ActiveCell.Value <> "" Then
ActiveCell.End(xlDown).Offset(1, 0).Activate
End If

'set range to copy to
colX = ActiveCell.Column
intY = ActiveCell.Row
Set r2 = Range(colX & intY, colX & (intY + x))

'pastespecial
r2.PasteSpecial Paste:=xlPasteFormulas, operation:=xlPasteSpecialOperationAdd

'clear clipboard
Application.CutCopyMode = False

End If

End If
Next


End Sub