PDA

View Full Version : Solved: Paste on grouped sheets in VB for Excel



ksquirt
01-20-2009, 09:20 AM
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.

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("BP1:DO4")
Range("B32").Select


End Sub

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

ksquirt
01-20-2009, 12:13 PM
http://www.eggheadcafe.com/software/aspnet/33827807/paste-into-grouped-sheets.aspx

lucas
01-20-2009, 12:42 PM
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
01-20-2009, 12:55 PM
Sorry. This macro stops at
Sheets(VarArray).FillAcrossSheets Worksheets("Last").Range("BP1:DO4")

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:


Sub copy()

'

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

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
01-20-2009, 01:12 PM
I still don't understand the question.....want to try again to tell me what you are trying to do?

ksquirt
01-20-2009, 01:17 PM
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
01-20-2009, 01:25 PM
You will have to do it one sheet at a time and unprotect it first...paste...reprotect.

lucas
01-20-2009, 01:29 PM
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
01-20-2009, 01:42 PM
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
01-20-2009, 01:57 PM
Then exclude the ones you don't want and do your paste on each sheet one at a time:
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

ksquirt
01-22-2009, 07:54 AM
I can't get it to loop, it stays on the same page. Please help!


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("CZ:DA").Select
Range("CZ3").activate
Selection.insert Shift:=xlToRight
Columns("DD:DE").Select
Range("DD3").activate
Selection.insert Shift:=xlToRight
ActiveWindow.LargeScroll ToRight:=1
ActiveWindow.SmallScroll ToRight:=-3
Columns("DH:DI").Select
Range("DH3").activate
Selection.insert Shift:=xlToRight
Columns("DL:DM").Select
Range("DL3").activate
Selection.insert Shift:=xlToRight
'copies December's formulas
Range("DJ5:DK116").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

lucas
01-22-2009, 08:24 AM
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

lucas
01-22-2009, 08:27 AM
You didn't qualify which sheet to act upon with ws.columns.......

ksquirt
01-22-2009, 08:45 AM
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
01-22-2009, 08:47 AM
So, do I need to put ws. in front of all the columns? and in the second part where it's copying?

lucas
01-22-2009, 09:01 AM
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
01-22-2009, 09:26 AM
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.

'copies December's formulas
ws.Columns("DJ:DK").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

lucas
01-22-2009, 09:54 AM
Try it like this instead:
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

ksquirt
01-22-2009, 11:42 AM
OMG, it worked! I'm so excited! Now the last piece (and the original question) is how do I copy BR1:DO4 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
01-22-2009, 12:46 PM
I tried a named range at the end, but it didn't work.

ws.Range("MonthHeader").copy
ws.Range("BR1").PasteSpecial

End Select
Next ws
Application.ScreenUpdating = True
End Sub

lucas
01-22-2009, 01:06 PM
What is the named range MonthHeader and what does "it didn't work" mean? error, if so what error, etc.

ksquirt
01-22-2009, 01:21 PM
Run-time error '1004': Insert method of Range class failed.
Ugh... it's highlighting the first column that was inserted in the code way above.

ws.Columns("BT:BU").insert Shift:=xlToRight

I removed the named range portion at the end and it's still not working. I don't know what I did for sure. I added two more worksheets to the Case.

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", "First", "Last"
Case Else
ws.Columns("BT:BU").insert Shift:=xlToRight
ws.Columns("BX:BY").insert Shift:=xlToRight
ws.Columns("CB:CC").insert Shift:=xlToRight
ws.Columns("CF:CG").insert Shift:=xlToRight
ws.Columns("CJ:CK").insert Shift:=xlToRight
ws.Columns("CN:CO").insert Shift:=xlToRight
ws.Columns("CR:CS").insert Shift:=xlToRight
ws.Columns("CV:CW").insert Shift:=xlToRight
ws.Columns("CZ:DA").insert Shift:=xlToRight
ws.Columns("DD:DE").insert Shift:=xlToRight
ws.Columns("DH:DI").insert Shift:=xlToRight
ws.Columns("DL:DM").insert Shift:=xlToRight
'copies December's formulas
ws.Range("DJ5:DK116").copy
ws.Range("BT5").PasteSpecial
ws.Range("BX5").PasteSpecial
ws.Range("CB5").PasteSpecial
ws.Range("CF5").PasteSpecial
ws.Range("CJ5").PasteSpecial
ws.Range("CN5").PasteSpecial
ws.Range("CR5").PasteSpecial
ws.Range("CV5").PasteSpecial
ws.Range("CZ5").PasteSpecial
ws.Range("DD5").PasteSpecial
ws.Range("DH5").PasteSpecial
ws.Range("DL5").PasteSpecial


End Select
Next ws
Application.ScreenUpdating = True
End Sub


I named the range BR1: DO4 on sheet "Last" as MonthHeader thinking it might help in the process of copying it.

lucas
01-22-2009, 02:06 PM
I'm not really sure at this point. Can you get your file back to working as it was in post #20

ksquirt
01-22-2009, 02:09 PM
I need two columns moved at one time and the rest of the code should match. I have to insert two columns per month (so, insert times 12) and then copy two columns from December into all the new columns. The first column is for hours, the second is dollars, so the formulas aren't the same. The code worked perfectly before, so I have no idea why it doesn't now. I only added two more sheets for it to ignore. Then, I was trying to add to the macro as eventually it will be quite large.

ksquirt
01-22-2009, 02:13 PM
Ok, it works again... you have got to think I'm the biggest dimwit. sorry. So, now I want to copy a range from "Last" to every sheet.

ksquirt
01-30-2009, 07:39 AM
I did it! Here's the end of my copy/paste macro. I had issues with it copying because the top row is merged four cells at a time.

ws.Range("BR1:DO4").unmerge
Sheets("Last").Range("BR1:DO4").copy
ws.Range("BR1").PasteSpecial

End Select

Next ws

lucas
01-30-2009, 09:21 AM
You should avoid merged cells at all costs if you have control. use center across selection instead.

ksquirt
01-30-2009, 10:31 AM
Would love to follow your advice, but I don't have a clue how when it's all formatted already.

lucas
01-30-2009, 10:44 AM
With a sheet, click on the square above the one and to the left of the A

the headings and row numbers. That will select all cells on the sheet.

go to format - cells.... when the dialog comes up choose the alignment tab and make double sure that merge cells checkbox is cleared.

ksquirt
01-30-2009, 11:19 AM
I know how to merge and unmerge, but not how to center within the selection. I have four columns for each month and the header is the four rows merged to say 'Jan_09', etc. for each month. My macro wouldn't let me copy the entire year of headers and paste to another sheet when they were merged.

lucas
01-30-2009, 11:33 AM
select the cells that were merged and right click on them. Select format cells.

select the alignment tab and then in the text alignment area select horizontal, drop the box and select "center across selection"

ksquirt
01-30-2009, 01:32 PM
OH!!! I learn something new every day! Thanks so much!!!

ksquirt
01-30-2009, 02:43 PM
I want to put a fomula on row 50 that uses cells on the 'AU Summary' sheet. Part of the problem is that I have four columns per month on the AU Summary sheet and only two per month on the Cost Summary tab row 50. So, I can't just fill the formula across. The other problem is that the row I want to use on the AU Summary moves depending on how many other tabs are created. This is part of a code from a large macro I'm working on. The row I want to use has "Totals" in column B. Can I have the macro look for that word?

I guess I can't attach the same workbook twice? I have the same full workbook in my other threads. My full macro adds columns AJ:BK on the Cost Summary tab, but row 50 doesn't fill properly because the "Totals" row isn't always on the same row in every workbook that I'm converting. And that's what I'm trying to achieve. TIA!

lucas
02-05-2009, 08:22 AM
Threads merged