Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Paste into grouped sheets using VB
I am trying to paste data from one sheet into an array of grouped sheets, but using "Selection" or other like words isn't working. Please help! I am new to this board, so please pardon any formatting issues. Here's what I have:
Sub copy() ' ' copy Macro ' Macro recorded 1/16/2009 by n893581x ' ' Sheets("Last").Select Range("BP1:DO4").Select Selection.copy Dim MyArray() As String 'Dimension an array as string type Dim iLoop As Integer 'Dimension a variable as integer to be used for a loop Dim Cnt As Integer 'Dimension a variable as integer for a counter for each sheet named group ' Start a loop through all sheets in the workbook For iLoop = 1 To Sheets.Count ' Check if the sheets has the name "Group" and then a number If Worksheets(iLoop).Name Like "#*" Then ' Sheet Match Found So Count Cnt = Cnt + 1 ' Redimension the array and preserve any existing entrys ReDim Preserve MyArray(Cnt) ' Add the sheet to the array MyArray(Cnt) = Worksheets(iLoop).Name End If Next iLoop ' Check if the array contains any sheets and if so select them If UBound(MyArray) 0 Then Worksheets(MyArray).Select Sheets("48643").Select Range("BP1").Select .PasteSpecial Paste:=xlPasteValues Range("B32").Select End Sub EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com/default.aspx?ref=ng |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Paste into grouped sheets using VB
I'd just loop through the sheets and paste when I found a match in names:
Option Explicit Sub myCopy() Dim RngToCopy As Range Dim wks As Worksheet Set RngToCopy = Worksheets("Last").Range("bp1:do4") For Each wks In ActiveWorkbook.Worksheets If LCase(wks.Name) Like LCase("#*") Then RngToCopy.copy wks.Range("bp1").PasteSpecial Paste:=xlPasteValues End If Next wks End Sub kara, stevenson wrote: I am trying to paste data from one sheet into an array of grouped sheets, but using "Selection" or other like words isn't working. Please help! I am new to this board, so please pardon any formatting issues. Here's what I have: Sub copy() ' ' copy Macro ' Macro recorded 1/16/2009 by n893581x ' ' Sheets("Last").Select Range("BP1:DO4").Select Selection.copy Dim MyArray() As String 'Dimension an array as string type Dim iLoop As Integer 'Dimension a variable as integer to be used for a loop Dim Cnt As Integer 'Dimension a variable as integer for a counter for each sheet named group ' Start a loop through all sheets in the workbook For iLoop = 1 To Sheets.Count ' Check if the sheets has the name "Group" and then a number If Worksheets(iLoop).Name Like "#*" Then ' Sheet Match Found So Count Cnt = Cnt + 1 ' Redimension the array and preserve any existing entrys ReDim Preserve MyArray(Cnt) ' Add the sheet to the array MyArray(Cnt) = Worksheets(iLoop).Name End If Next iLoop ' Check if the array contains any sheets and if so select them If UBound(MyArray) 0 Then Worksheets(MyArray).Select Sheets("48643").Select Range("BP1").Select .PasteSpecial Paste:=xlPasteValues Range("B32").Select End Sub EggHeadCafe - .NET Developer Portal of Choice http://www.eggheadcafe.com/default.aspx?ref=ng -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Export all grouped information to different sheets | Excel Discussion (Misc queries) | |||
create alert when in sheets are grouped | Excel Worksheet Functions | |||
Grouped Sheets - Windows Arrange | Excel Discussion (Misc queries) | |||
Replace with Grouped Sheets | Excel Discussion (Misc queries) | |||
Grouped Sheets and Formating | Excel Discussion (Misc queries) |