Sheet Names

[aalwazeer]

Hi,

I am trying to copy a range from a number of sheets and paste it in a
diifferent range of a different sheet
Ex.: I am trying to copy the range A362 from the Sheet "First" and
paste it in range A564 of the Sheet "Last". Then I need to copy the
range from another sheet and paste it in a range of the "Last" sheet.
and so on. Basically, what I am trying to know is how can I loop (For
Next) between sheets with diffrent names without writting the name of
the sheet every time.

Sheets("First").Select
Range("A362").Select
Selection.Copy
Sheets("Last").Select
Range("A564").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C2").Select

 

[aalwazeer]

Let's say that the destination sheet is always "Last". The code

would look like this:
Sheets("First").Range("A362").Copy Range("A564")

Thanks!. But what about looping a variable sheet name

i.e. something like this:

For i= 1 To N
Sheets(i).Range("A362").Copy Range("A564")
Next i

wher i here need to represent a sheet name or number

 

[Chip Pearson]

Try something like

Dim WS As Worksheet
For Each WS In Worksheets
WS.Range("A362").Copy WS.Range("A564")
Next WS


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[aalwazeer]

Thanks.

Can we loop different range names from one sheet to another

e.g.:

Sheets("aa").Range("B1").Copy Sheets("MM").Range("C3")

Sheets("aa").Range("A362").Copy Sheets("MM").Range("A564")

Sheets("aa").Range("E3:E62").Copy Sheets("MM").Range("F5:F64")

Sheets("aa").Range("F3:F62").Copy Sheets("MM").Range("I5:I64")
Sheets("aa").Range("G3:G62").Copy Sheets("MM").Range("L5:L64")
Sheets("aa").Range("H3:H62").Copy Sheets("MM").Range("O5:O64")

... etc