4

HI I have this code which works well to copy and append data to a separate worksheet however I want it to paste to a specific cell range in the destination sheet, how do I go about ammending it?

Sub SummurizeSheets()
    Dim ws As Worksheet

    Application.ScreenUpdating = False
    Sheets("Summary").Activate

    For Each ws In Worksheets
        If ws.Name <> "Summary" Then
            ws.Range("D2:D6, D8:D15").Copy
            Worksheets("Summary").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
        End If
    Next ws
End Sub
Kazimierz Jawor
  • 18,861
  • 7
  • 35
  • 55
Steve
  • 67
  • 1
  • 1
  • 8

1 Answers1

3

Modify this line so that it refers to the desired Worksheet and cell(s) address:

Worksheets("Summary").Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)

For example, this puts it on a worksheet named "Another Sheet Name" and in column F, instead of column C:

Worksheets("Another Sheet Name").Cells(Rows.Count, 6).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)

Update: You are using a somewhat dynamic range already, in conjunction with the Offset method. If you have trouble getting this to paste the values to the desired location, let me know what that location is and I can give more detailed answer.

David Zemens
  • 53,033
  • 11
  • 81
  • 130
  • Thanks for the quick reply I want the data to paste into E1, at present it vacates row 1 I am not sure why, I need to easily edit this for subsequent workbooks that all have 12 worksheets to summarise into named columns hope this makes sense – Steve Oct 21 '13 at 20:05
  • The `.Offset` method is taking the destination range and "moving" it down to `E2`. You should be able to just remove `.Offset(1, 0)` and I think that should do it. – David Zemens Oct 21 '13 at 20:09
  • Thanks David you were correct now working and i can change the rows.count for subsequent sheets to place the results. Thanks again – Steve Oct 21 '13 at 20:14
  • You're welcome, please do mark this answer "Accepted" if it's solved your question :) – David Zemens Oct 21 '13 at 20:15