Macro to copy and paste sepcial into next blank row

**CharlieZangel** · 06-10-2013, 03:57 PM

I have 12 sheets of data that I would like to collate into one sheet (sheet 13), I must be over looking something because I just cant seem to get this to work. Please review my code below, it hangs at the last line, what am I missing?

Sub Collate_data()

'Clear all content in Sheet 13
Sheets(13).Range("A2:S5000").Clear

Application.ScreenUpdating = False
 
'Collate data
Sheets(1).Range("A3:S5000").Copy
Sheets(13).Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Sheets(2).Range("A3:S5000").Copy
Sheets(13).Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSepcial Paste:=xlPasteValuesAndNumberFormat, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

I hope someone can help me with this.
Thanks in advance for your help

**stnkynts** · 06-10-2013, 04:09 PM

PasteSepcial should be PasteSpecial. There might be more than that wrong but its what i see from first glance.

Note: It appears as though you are going to be copying the same range across all worksheets but number 13. Here is a quick way to do that

Sub Collate_data()
Dim wksht As Worksheet
Dim LR As Long

'Clear all content in Sheet 13
Sheets(13).UsedRange.ClearContents

Application.ScreenUpdating = False

'Collate data
For Each wksht In Worksheets
    If wksht.Index < 13 Then
        LR = wksht.Range("A" & Rows.Count).End(xlUp).Row
        wksht.Range("A3:S" & LR).Copy
        Sheets(13).Range("A2").PasteSpecial Paste:=xlPasteValues
    End If
Next wksht

Application.ScreenUpdating = True

End Sub

**CharlieZangel** · 06-10-2013, 04:12 PM

Thanks stnkynts, Good catch, fixed that but still no luck

**stnkynts** · 06-10-2013, 04:18 PM

where is the rest of your code and what are you trying to do? See added code above to see if it does what you want.

**CharlieZangel** · 06-10-2013, 04:21 PM

Thanks, i think the issue was the paste special function I was using, I tried "xlvalues" instead of "xlPasteValuesAndNumberFormat" and it worked fine. Thanks for your help