Solved: Paste on grouped sheets in VB for Excel

[ksquirt]

I am trying to copy from one sheet and paste on all sheets with a number for the name. I'm stuck on the part where you select the receiving sheets.

[vba]Sub 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 "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(VarArray).FillAcrossSheets Worksheets("Last").Range("BP1O4")
Range("B32").Select


End Sub[/vba]

Edited to add:
I did originally post my question here: at Egghead Cafe with my original code. (see link below)

[ksquirt]

http://www.eggheadcafe.com/software/...ed-sheets.aspx

[lucas]

I don't understand the question. Probably why you aren't getting any hits on this.

Same problem as your other thread. We can't see over your shoulder, you have to tell us what is going on and what you want to do.

I spend more time asking questions than I do answering them....

[ksquirt]

Sorry. This macro stops at
[VBA]Sheets(VarArray).FillAcrossSheets Worksheets("Last").Range("BP1O4")[/VBA]

I have changed this line in several ways with the macro always stopping there. First I had it copy from "Last" the range I wanted, then select the array. As seen below:

[VBA]
Sub copy()

'

Sheets("Last").Select

Range("BP1O4").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[/VBA]

This stopped at the red line. I need a line that tells it where to past on the array of grouped sheets. I hope this helps.

[lucas]

I still don't understand the question.....want to try again to tell me what you are trying to do?

[ksquirt]

Copy a range from one worksheet into several grouped sheets. The sheets that have a number as the tab name are grouped together in the array part of the macro. Since the amount of tabs vary from workbook to workbook I needed the array to be variable. I have that part working (that I know of). I need the copy and paste part to work. In the second macro the copy part worked but it didn't know where to paste it.

I greatly appreciate all your patience as I try to explain this! I am learning macros by trial and error and forums like this if I can find pieces that work. Thanks again!

[lucas]

You will have to do it one sheet at a time and unprotect it first...paste...reprotect.

[lucas]

Since the number of tabs changes. Are there specific sheets that you don't want to take this action on and then do it to the rest?

[ksquirt]

Yes. Instructions, AU Summary, Cost Summary, First, Last, All Data, STAMP Query, Teams, Monthly Totals, Month Summary, and Budget Overview. We have formulas on the Cost Summary page that sum all the sheets between First!:Last! for one cell. I was trying originally to use an array (or something) to utilize the First:Last, but to no avail. Sometimes a user will also add a tab outside of Budget Overview. I didn't know how to account for that.

[lucas]

Then exclude the ones you don't want and do your paste on each sheet one at a time:
[vba] For Each ws In Wkb.Worksheets
Select Case ws.Name
'these are the sheets names which shouldn't be affected
Case "Summary", "Department Hours", "Overtime", "Leave Form", "Log"
Case Else
ws.Unprotect Password:="password"
'your paste code goes on the next line instead of removing formatting
ws.Range("K4:S35").Interior.ColorIndex = xlNone
ws.Protect Password:="password"
End Select
Next ws
[/vba]

[ksquirt]

I can't get it to loop, it stays on the same page. Please help!

[VBA]
Sub NewestInsertColumns()
Dim Wkb As Workbook
Dim ws As Worksheet
Dim filenames As Variant
Application.ScreenUpdating = False
Set Wkb = ActiveWorkbook
For Each ws In Wkb.Worksheets
Select Case ws.Name
'these are the sheets names which shouldn't be affected
Case "AU Summary", "Cost Summary", "All Data", "Instructions", "Teams", "Month Summary", "STAMP Query", "Budget Overview", "Monthly Totals"
Case Else
Columns("BT:BU").Select
Range("BT3").activate
Selection.insert Shift:=xlToRight
Columns("BX:BY").Select
Range("BX3").activate
Selection.insert Shift:=xlToRight
ActiveWindow.LargeScroll ToRight:=1
Columns("CB:CC").Select
Range("CB3").activate
Selection.insert Shift:=xlToRight
Columns("CF:CG").Select
Range("CF3").activate
Selection.insert Shift:=xlToRight
Columns("CJ:CK").Select
Range("CJ3").activate
Selection.insert Shift:=xlToRight
Columns("CN:CO").Select
Range("CN3").activate
Selection.insert Shift:=xlToRight
ActiveWindow.LargeScroll ToRight:=1
Columns("CR:CS").Select
Range("CR3").activate
Selection.insert Shift:=xlToRight
Columns("CV:CW").Select
Range("CV3").activate
Selection.insert Shift:=xlToRight
ActiveWindow.LargeScroll ToRight:=1
Columns("CZA").Select
Range("CZ3").activate
Selection.insert Shift:=xlToRight
Columns("DDE").Select
Range("DD3").activate
Selection.insert Shift:=xlToRight
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=-3
Columns("DHI").Select
Range("DH3").activate
Selection.insert Shift:=xlToRight
Columns("DLM").Select
Range("DL3").activate
Selection.insert Shift:=xlToRight
'copies December's formulas
Range("DJ5K116").Select
Application.CutCopyMode = False
Selection.copy
ActiveWindow.LargeScroll ToRight:=-2
Range("BT5").Select
ActiveSheet.Paste
Range("BX5").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=1
Range("CB5").Select
ActiveSheet.Paste
Range("CF5").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=1
Range("CJ5").Select
ActiveSheet.Paste
Range("CN5").Select
ActiveSheet.Paste
Range("CR5").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=1
Range("CV5").Select
ActiveSheet.Paste
Range("CZ5").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=1
Range("DD5").Select
ActiveSheet.Paste
Range("DH5").Select
ActiveSheet.Paste
ActiveWindow.LargeScroll ToRight:=1
Range("DL5").Select
ActiveSheet.Paste
End Select
Next ws
Application.ScreenUpdating = True
End Sub[/VBA]

[lucas]

[VBA]Option Explicit
Sub NewestInsertColumns()
Dim Wkb As Workbook
Dim ws As Worksheet
Dim filenames As Variant
Application.ScreenUpdating = False
Set Wkb = ActiveWorkbook
For Each ws In Wkb.Worksheets
Select Case ws.Name
'these are the sheets names which shouldn't be affected
Case "AU Summary", "Cost Summary", "All Data", "Instructions", "Teams", "Month Summary", "STAMP Query", "Budget Overview", "Monthly Totals"
Case Else
ws.Columns("I:I").Insert Shift:=xlToRight

End Select
Next ws
Application.ScreenUpdating = True
End Sub
[/VBA]

[lucas]

You didn't qualify which sheet to act upon with ws.columns.......

[ksquirt]

I need it to work on all the sheets with a number as the tab name. I copied your macro in (and kept the middle of mine, as messy as it is) and it's still not looping.

[ksquirt]

So, do I need to put ws. in front of all the columns? and in the second part where it's copying?

[lucas]

You need to get rid of all of the selects as I have asked you to do before and you promised me you would.......

You also need to take a look at how I am inserting columns........

see attached....it inserts a column on all three sheets.

[ksquirt]

Ok, I removed all the selects and may have killed the paste code. It says object is not supported. I had ws.Range and changed it to columns and it doesn't like either one.

[VBA]'copies December's formulas
ws.Columns("DJK").copy
ws.Columns("BT").Paste
ws.Columns("BX").Paste
ws.Columns("CB").Paste
ws.Columns("CF").Paste
ws.Columns("CJ").Paste
ws.Columns("CN").Paste
ws.Columns("CR").Paste
ws.Columns("CV").Paste
ws.Columns("CZ").Paste
ws.Columns("DD").Paste
ws.Columns("DH").Paste
ws.Columns("DL").Paste[/VBA]

[lucas]

Try it like this instead:
[VBA]Option Explicit
Sub NewestInsertColumns()
Dim Wkb As Workbook
Dim ws As Worksheet
Dim filenames As Variant
Application.ScreenUpdating = False
Set Wkb = ActiveWorkbook
For Each ws In Wkb.Worksheets
Select Case ws.Name
'these are the sheets names which shouldn't be affected
Case "AU Summary", "Cost Summary", "All Data", "Instructions", "Teams", "Month Summary", "STAMP Query", "Budget Overview", "Monthly Totals"
Case Else
ws.Columns("H:I").Copy
ws.Range("N1").PasteSpecial
ws.Range("B1").PasteSpecial
End Select
Next ws
Application.ScreenUpdating = True
End Sub[/VBA]

[ksquirt]

OMG, it worked! I'm so excited! Now the last piece (and the original question) is how do I copy BR1O4 on "Last" and paste to all the same sheets? When I do this manually it asks if I want to replace destination cells too.

[ksquirt]

I tried a named range at the end, but it didn't work.

[VBA] ws.Range("MonthHeader").copy
ws.Range("BR1").PasteSpecial

End Select
Next ws
Application.ScreenUpdating = True
End Sub
[/VBA]