Sheet Names

  • Thread starter Thread starter aalwazeer
  • Start date Start date
A

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 A3:D62 from the Sheet "First" and
paste it in range A5:D64 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("A3:D62").Select
Selection.Copy
Sheets("Last").Select
Range("A5:D64").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("C2").Select
 
Let's say that the destination sheet is always "Last". The code
would look like this:
Sheets("First").Range("A3:D62").Copy Range("A5:D64")
Click to expand...

Thanks!. But what about looping a variable sheet name

i.e. something like this:

For i= 1 To N
Sheets(i).Range("A3:D62").Copy Range("A5:D64")
Next i

wher i here need to represent a sheet name or number
 
Try something like

Dim WS As Worksheet
For Each WS In Worksheets
WS.Range("A3:D62").Copy WS.Range("A5:D64")
Next WS


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
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("A3:D62").Copy Sheets("MM").Range("A5:D64")

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
 
Back
Top