VBA pasting to wrong location

[riley454]

I have a macro that (in part)
-copies a range (A2:B27) from a source sheet
-selects all sheets except 2 that don't meet my criteria
-pastes the copied range to the same locations as the source on all the selected sheets
-copies a different range (E1:KW28) from the source
-pastes to the still selected destination sheets at the same locations as the source

This almost works perfectly however instead of pasting the 2nd range to range("E1") it pastes to E2 for some reason

I have tried adding 'Selection.Range("E1").Select' prior to pasting to E1 but that offsets the paste by several columns

I have also tried reversing the order of copy/paste using the E1 range first which pastes correctly but then the A2 range is pasted to E3 instead of A2

Sheets("Source").Range("A2:B27").Copy

'selects all sheets except those named
For i = 2 To ThisWorkbook.Sheets.Count
    If Sheets(i).Name <> "mainData" And Sheets(i).Name <> "Source" Then Sheets(i).Select Replace:=False
Next i

Selection.Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Sheets("Source").Range("E1:KW28").Copy
Selection.Range("E1").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats

 

[bebo021999]

Selection.Range("E1")

is quite different with:

Activesheet.Range("E1")

Try to use Activesheet instead of Selection

 

[riley454]

Thanks Bebo

I only changed the 2nd "Selection.Range" to "Activesheet.Range" and this worked perfectly

Just curious why using Selection works ok for the first paste but not the 2nd and why it offsets it incorrectly?

 

[RoryA]

Selection.Range("A2") refers to the cell one row below and in the same column as the first cell of whatever is selected. If A1 happens to be selected, you'll get A2. If it's any other cell, you will not be pasting to A2.

 

[riley454]

Awesome! Thanks for the explanation RoryA. I was trying to goog it but couldn't find anything that related to the usage of "Selection" as I was attempting. It also explains why my original code "worked" but wasn't really doing what I thought it was. Just got lucky

Thanks again to all