Loop for multiple selctions

B

Bill101

New Member
Joined
Aug 28, 2014
Messages
3
Hi everyone, I have this macro for doing a special copy/paste which does the trick but I want to be able to select multiple cells and have it process them in order instead of just doing it one at a time. Suggestions? Thanks in advance

ActiveCell.Range("A1:A4").Select
Selection.Copy
Sheets("Sheet3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
ActiveCell.Offset(4, 1).Range("A1:J1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1:J1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(0, 10).Range("A1").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1:J1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(0, 10).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1:J1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(0, 10).Range("A1").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Offset(1, -34).Range("A1").Select

End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Sort by date Sort by votes
Bill,

when you say "I want to be able to select multiple cells and have it process them in order", will these be the same cells (something we can hard code) or do you want to select multiple cells/ranges (highlighted) before running the code?

Can you give a description of what you want the code to do? How about a before and after of your data?

thanks,
FarmerScott
 
Last edited:
Upvote 0
farmerscott said:
Bill,

when you say "I want to be able to select multiple cells and have it process them in order", will these be the same cells (something we can hard code) or do you want to select multiple cells/ranges (highlighted) before running the code?

Can you give a description of what you want the code to do? How about a before and after of your data?

thanks,
FarmerScott
Click to expand...

Thanks Scott for the reply, When I posted this I was thinking I wanted to select multiple ranges and apply the same technique to all of them at once instead of repeating mechanically (with my own hands) if that makes any sense. Basically my data comes from another application that is used to track library books but when it dumps the data onto a SS it is not very usable. My macro converts the blocks of data into rows which is much more useful. I'm looking for a way to do this thousands of times with minimal effort. It has since occurred to me that a LOOP would be more useful but the blocks of data are not all the same size (various numbers of rows). The beginning of each block of data starts with a unique number in the format 11-1111111 (2 digits dash then 7 digits) so optimally if the loop could return to the next cell in Column A ( relatively speaking) that contains such a number it should be able to convert all my data in one fell swoop. Thanks again and I will post a newer version of my macro below.

[TABLE="align: left"]
<tbody>[TR]
[TD]01-1111111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A book about some stuff that a guy wrote[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Pulishing City : year of pub, more pub info
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]201 p. : 1 folded col. map ; 28 cm.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD]
1​
[/TD]
[TD]32354000903677[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD]ZZZZZYT[/TD]
[TD]AGAAAB[/TD]
[TD]BOOK[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]
2​
[/TD]
[TD="bgcolor: transparent"]32354000903743[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]ZZZZZUY[/TD]
[TD="bgcolor: transparent"]AAAAFI[/TD]
[TD="bgcolor: transparent"]BOOK[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD]
3​
[/TD]
[TD]32354001872657[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD]ZZZBG[/TD]
[TD]AAOAF[/TD]
[TD]DISKDISQUE[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]
4​
[/TD]
[TD="bgcolor: transparent"]200117-1004[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]ZXC[/TD]
[TD="bgcolor: transparent"]ABM[/TD]
[TD="bgcolor: transparent"]MICROFICHE[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD]01-1111111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A book about some stuff that a guy wrote[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Pulishing City : year of pub, more pub info
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]201 p. : 1 folded col. map ; 28 cm.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD]
1​
[/TD]
[TD]32354000903677[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD]ZZZZZYT[/TD]
[TD]AGAAAB[/TD]
[TD]BOOK[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]
2​
[/TD]
[TD="bgcolor: transparent"]32354000903743[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]ZZZZZUY[/TD]
[TD="bgcolor: transparent"]AAAAFI[/TD]
[TD="bgcolor: transparent"]BOOK[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD]
3​
[/TD]
[TD]32354001872657[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD]ZZZBG[/TD]
[TD]AAOAF[/TD]
[TD]DISKDISQUE[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]
4​
[/TD]
[TD="bgcolor: transparent"]200117-1004[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]ZXC[/TD]
[TD="bgcolor: transparent"]ABM[/TD]
[TD="bgcolor: transparent"]MICROFICHE[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD]01-1111111[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]A book about some stuff that a guy wrote[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD]Pulishin City : year of pub, more pub info[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]201 p. : 1 folded col. map ; 28 cm.[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD]
1​
[/TD]
[TD]32354000903677[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD]ZZZZZYT[/TD]
[TD]AGAAAB[/TD]
[TD]BOOK[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]
2​
[/TD]
[TD="bgcolor: transparent"]32354000903743[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]ZZZZZUY[/TD]
[TD="bgcolor: transparent"]AAAAFI[/TD]
[TD="bgcolor: transparent"]BOOK[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD]
3​
[/TD]
[TD]32354001872657[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD]ZZZBG[/TD]
[TD]AAOAF[/TD]
[TD]DISKDISQUE[/TD]
[TD]
1​
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD]48-222[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]
4​
[/TD]
[TD="bgcolor: transparent"]200117-1004[/TD]
[TD]LIB[/TD]
[TD]LIB[/TD]
[TD="bgcolor: transparent"]ZXC[/TD]
[TD="bgcolor: transparent"]ABM[/TD]
[TD="bgcolor: transparent"]MICROFICHE[/TD]
[TD="bgcolor: transparent"]
1​
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Here's me newer version


Application.ScreenUpdating = False
ActiveCell.Range("A1:A4").Select
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Sheets("Sheet1").Select
ActiveCell.Offset(4, 1).Range("A1:J1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(0, 4).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1:J1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(0, 10).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1:J1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(0, 10).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
ActiveCell.Offset(1, 0).Range("A1:J1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(0, 10).Range("A1").Select
ActiveSheet.Paste

ActiveCell.Offset(1, -34).Range("A1").Select
Sheets("Sheet1").Select
ActiveCell.Offset(1, -1).Range("A1").Select
End Sub
 
Upvote 0
Hi Bill,

I assume the data above is the before view of your data. Can you show what you want the data to look like. Do you want it exported to another sheet? Do your sheets have names?

Can you also elaborate on "the blocks of data are not all the same size (various numbers of rows)." Can you show examples.

thanks

FarmerScott
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
963
kevin9999
K
G
  • Solved
How do I automatically insert into next blank row..
Replies
2
Views
647
ghrek
G
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
1K
cstuder
C
R
  • Question Question
Formatting using VBA
Replies
0
Views
542
RandyD123
R
D
  • Solved
VBA If statement to continue
2
Replies
17
Views
520
dinunan
D

Forum statistics

Threads
1,222,405
Messages
6,165,867
Members
451,989
Latest member
DannyBoy1977

Share this page

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