Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shuffle through sheets in a loop?
Hi, I would like to copy an identical selection from multiple sheets automatically, and then pasting to another sheet. How can i code a loop that shuffles through the sheets without knowing their names, and then stopping when it reaches the "paste" sheet? Is there some kind of numerical sheet "category" that i can use? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=562405 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shuffle through sheets in a loop?
Erikhs,
Try this: Sub SheetsLoop() Dim wkb As Workbook Dim wks As Worksheet Dim i As Integer Set wkb = Application.ActiveWorkbook For i = 1 To wkb.Worksheets.Count Set wks = wkb.Worksheets(i) If wks.Name < "Paste" Then 'Add your copy paste code here Else 'Do nothing or add other code here End If Next i End Sub The above code checks the sheet name so you'll need to change the "Paste" name to whatever you're clling your target sheet. Hope that helps Best regards John "erikhs" wrote in message ... Hi, I would like to copy an identical selection from multiple sheets automatically, and then pasting to another sheet. How can i code a loop that shuffles through the sheets without knowing their names, and then stopping when it reaches the "paste" sheet? Is there some kind of numerical sheet "category" that i can use? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=562405 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shuffle through sheets in a loop?
Hi erikhs
Try This ActiveWorkbook.Sheets(x) "x" Refers to the Tabs at the bottom e.g. : If your looking for the third sheet along the bottom the "x" equals 3. Jason Zischke "erikhs" wrote: Hi, I would like to copy an identical selection from multiple sheets automatically, and then pasting to another sheet. How can i code a loop that shuffles through the sheets without knowing their names, and then stopping when it reaches the "paste" sheet? Is there some kind of numerical sheet "category" that i can use? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=562405 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shuffle through sheets in a loop?
Hi, i cant get it to work, it seems it copies the data on the first sheet over and over again, here is my code: Sub Macro1() 'The data i want to copy Dim MyRange As Range ' Dim wkb As Workbook Dim wks As Worksheet Dim i As Integer 'Dynamic range for the separate sheets Range("a6", Range("v6").End(xlDown)).Name = "MyRange" On Error Resume Next 'Add title and "paste" sheet" Rows("5").Copy Worksheets.Add.Name = "Edited" 'Your code Set wkb = Application.ActiveWorkbook For i = 1 To wkb.Worksheets.Count Set wks = wkb.Worksheets(i) If wks.Name < "Redigeret" Then 'My copy/pasté Range("MyRange").Copy Sheets("Edited").Select 'Paste at first blank cell in column A. This doesn't work either by 'the way. Any suggestions? Set firstBlank = Range("A1").End(xlDown).Offset(1, 0) Range("firstblank").Select ActiveCell.PasteSpecial Else End If Next i End Sub What could be wronh? Anyone else? Help would really be appreciated! -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=562405 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shuffle through sheets in a loop?
Jason Zischke Wrote: Hi erikhs Try This ActiveWorkbook.Sheets(x) "x" Refers to the Tabs at the bottom e.g. : If your looking for the third sheet along the bottom the "x" equals 3. Jason Zischke [/color] Thank you for your suggestion, but how can i incorporate this into a loop? Any ideas? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=562405 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shuffle through sheets in a loop?
Sub Foo_Test()
Dim x As Long Dim wkb As Workbook Dim wks As Worksheet Set wkb = ActiveWorkbook For x = 1 To wkb.Worksheets.Count Set wks = wkb.Worksheets(x) ' Do something Next x End Sub -- Ed Chief Chef, Kludge Code Cafe "Spaghetti Is Our Specialty!" ' "erikhs" wrote in message ... Jason Zischke Wrote: Hi erikhs Try This ActiveWorkbook.Sheets(x) "x" Refers to the Tabs at the bottom e.g. : If your looking for the third sheet along the bottom the "x" equals 3. Jason Zischke Thank you for your suggestion, but how can i incorporate this into a loop? Any ideas? -- erikhs ------------------------------------------------------------------------ erikhs's Profile: http://www.excelforum.com/member.php...o&userid=32788 View this thread: http://www.excelforum.com/showthread...hreadid=562405 [/color] |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loop in sheets | Excel Programming | |||
Loop through all sheets in workbook | Excel Programming | |||
Loop through sheets | Excel Programming | |||
excel VBA - for loop for several sheets | Excel Programming | |||
Loop across Sheets and number of sheets | Excel Programming |