Looping through sheets, loop inside a loop

amitcohen

Board Regular
Joined
Jan 14, 2010
Messages
118
Hi All
I use the following macro to copy a cell from sheets("DB").Select
and paste it to a cell in another sheet.
Code:
Dim ws As Worksheet
For Each ws In Worksheets
  With ws
    Select Case .Name
      Case "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10", "Sheet11"
         Sheets("DB").Select
         [COLOR=Red]Range("A1").Copy[/COLOR]
         ws.Select
         Range("B3").Select
         Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End Select
  End With
Next
Now, I need to select different cell each time
So instead of copy-paste same cell ("A1") every time
Macro will select different cell. (A2, A3, A4 etc')
Something like Range("A" & i).Select, maybe.

I'm not sure how to create the loop for the macro to select the next cell to copy-paste.

Hope you can help.

Many thanks,

Amit.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Hi SteveO59L

Thanks for that,
But I'm not sure where exactly to insert the code.. :confused:

I try and replaced your code with the Range("A1").Copy
but its not working..
 
Upvote 0
Can you explain what you are trying to do

This part of your code seems to be completely redundant

Select Case .Name
Case "Sheet3", "Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9",
 
Upvote 0
Well, the idea is simple..

I got a source sheet, name as sheets("DB")
in column A there is a 36 cells with data ("A1:A36")

Now comes the tricky thingy,
I need to copy "A1", goto to "sheet3" and paste special in cell "B5"
Then,
Go back to sheets("DB") goto cell "A2" copy, then goto sheet4 and paste special in cell "B5".
Then,
Go back to sheets("DB") goto cell "A3" copy, then goto sheet5 and paste special in cell "B5".

Looping like this for 36 times.
So at the end, I got all sheet's "B5" cell, populated with the data that came from the sheets("DB").


The way I see it, its two loops that runs here,
One is looping through column A cells, and the other is looping through the sheets.

Hope I mange to explain myself better this time ;)


Thank you,
Amit
 
Upvote 0
Try
Code:
Sub CopyForNoReason()
Dim ws As Worksheet
Dim lSheetCt As Long
Dim MyCell As Range
lSheetCt = 3
With Sheets("DB")
For Each MyCell In .Range("A1:A36")
 
            MyCell.Copy
 
            Worksheet("sheet" & lSheetCt).Range("B5").Address).PasteSpecial (xlPasteAll)
            lSheetCt = lSheetCt + 1
 
Next MyCell
End With
End Sub
 
Last edited:
Upvote 0
Hi CharlesChuckieCharles

Thanks for the code.
Now I understand it better.

Had to modified your code a bit (It just didn't run properly)

Code:
Dim ws As Worksheet
Dim lSheetCt As Long
Dim MyCell As Range
lSheetCt = 3
With Sheets("DB")
For Each MyCell In .Range("A1:A36")
 
            MyCell.Copy
 
            Sheets("sheet" & lSheetCt).Activate
            Range("B5").Select
            
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
            lSheetCt = lSheetCt + 1
 
Next MyCell
End With
Thanks so much for taking the time to help.

Amit
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,461
Members
448,573
Latest member
BEDE

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top