Loop for multiple selctions

[Bill101]

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

 

[farmerscott]

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

 

[Bill101]

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

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.

01-1111111
A book about some stuff that a guy wrote
Pulishing City : year of pub, more pub info
201 p. : 1 folded col. map ; 28 cm.
	48-222	LIB	1​	32354000903677	LIB	LIB	ZZZZZYT	AGAAAB	BOOK	1​
	48-222	LIB	2​	32354000903743	LIB	LIB	ZZZZZUY	AAAAFI	BOOK	1​
	48-222	LIB	3​	32354001872657	LIB	LIB	ZZZBG	AAOAF	DISKDISQUE	1​
	48-222	LIB	4​	200117-1004	LIB	LIB	ZXC	ABM	MICROFICHE	1​
01-1111111
A book about some stuff that a guy wrote
Pulishing City : year of pub, more pub info
201 p. : 1 folded col. map ; 28 cm.
	48-222	LIB	1​	32354000903677	LIB	LIB	ZZZZZYT	AGAAAB	BOOK	1​
	48-222	LIB	2​	32354000903743	LIB	LIB	ZZZZZUY	AAAAFI	BOOK	1​
	48-222	LIB	3​	32354001872657	LIB	LIB	ZZZBG	AAOAF	DISKDISQUE	1​
	48-222	LIB	4​	200117-1004	LIB	LIB	ZXC	ABM	MICROFICHE	1​
01-1111111
A book about some stuff that a guy wrote
Pulishin City : year of pub, more pub info
201 p. : 1 folded col. map ; 28 cm.
	48-222	LIB	1​	32354000903677	LIB	LIB	ZZZZZYT	AGAAAB	BOOK	1​
	48-222	LIB	2​	32354000903743	LIB	LIB	ZZZZZUY	AAAAFI	BOOK	1​
	48-222	LIB	3​	32354001872657	LIB	LIB	ZZZBG	AAOAF	DISKDISQUE	1​
	48-222	LIB	4​	200117-1004	LIB	LIB	ZXC	ABM	MICROFICHE	1​

<tbody>
</tbody>

 

[Bill101]

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

 

[farmerscott]

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