Copy a selection of data and offset 1 cell

[kyriakos kyriakou]

Hello all,

I want to make a macro that goes to another tab find all the cells that have context in it in column A, copy them (without copying the row 1 as i have the titles there) and paste them to a different tab.

Here is my code.

Sheets("Source_previous month").Select
Application.Goto Reference:="R9999C1"
Selection.End(xlUp).Select
Range(Selection, Selection.End(xlUp)).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Copy
Sheets("Pivot").Select
Range("A2").Select
ActiveSheet.Paste



My problem is that, when i offset, it looses the selection, so with this code, it just copy one cell.


Thanks in advance

 

[Fluff]

How about

   With Sheets("Source_previous month")
      .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Copy Sheets("Pivot").Range("A2")
   End With

 

[kyriakos kyriakou]

Wow, that worked perfectly. Thanks

 

[Fluff]

You're welcome & thanks for the feedback.

 

[kyriakos kyriakou]

And if you have some more minutes and can help got another one question.

Now i copied my data using the above code. If i want it to write in B cells, "opening", how do you suggest should i do it

Just to write in the cell opening.

Thanks in advance

 

[Fluff]

Which sheet is that on?

 

[kyriakos kyriakou]

On the pivot sheet

 

[Fluff]

Ok, how about

   With Sheets("Source_previous month")
      With .Range("A2", .Range("A" & Rows.Count).End(xlUp))
         .Copy Sheets("Pivot").Range("A2")
         Sheets("Pivot").Range("B2").Resize(.Count).Value = "Opening"
      End With
   End With

 

[kyriakos kyriakou]

Worked perfectly again, thank for your help very much ?

 

[Fluff]

Glad to help & thanks for the feedback.