Loop for worbook and worksheet changes

[streub]

I have been supplied with the sub below to make changes to a worksheet in a secondary workbook(BTM) then transfer data to the primary workbook (Initial Load) worksheet 3. How do I modify this code for the additional worksheets representing Feb through Dec?




Sub um_snb()
Workbooks.Open Filename:="d:\mfi documents\mfi applications\mfi test files\btm.xlsm"
For Each ws In Workbooks("BTM").Sheets
With ws
If Right(ws.name, 6) = "Graphs" Then
Range("A1:D1").UnMerge
Range("A1:D1").ClearContents
Range("b1:b2").Value = Application.Transpose(Array("01", "13"))
Range("b1:b33").ClearFormats
Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 34) = Application.Transpose(.Range("b1:b34").Value)
End If
End With
Next
End Sub

[snb]

if you use 'with ... end' with you should also use the necessary dots.

Sub um_snb()
with Workbooks.Open("d:\mfi documents\mfi applications\mfi test files\btm.xlsm")
For Each ws In .Sheets
With ws
If Right(.name, 6) = "Graphs" Then
with .Range("A1:D1")
.UnMerge
.ClearContents
End With
.Range("B1:B2").Value = Application.Transpose(Array("01", "13"))
.Range("b1:b33").ClearFormats
Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 34) = Application.Transpose(.Range("b1:b34").Value)
End If
End With
Next
End With
End Sub

NB. I don't use codetags to prevent it erases the formatting.

[streub]

The red squares represent where I should place a dot, correct?

[snb]

Yes, like:


Sub um_snb()
with Workbooks.Open("d:\mfi documents\mfi applications\mfi test files\btm.xlsm")
For Each ws In .Sheets
With ws
If Right(.name, 6) = "Graphs" Then
With .Range("A1:D1")
.UnMerge
.ClearContents
End With
.Range("B1:B2").Value = Application.Transpose(Array("01", "13"))
.Range("b1:b33").ClearFormats
Sheet3.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 34) = Application.Transpose(.Range("b1:b34").Value)
End If
End With
Next
End With
End Sub

You might dive into your VBA handbook (or the VBEditor's F1) to explore all the details about using 'With ... End With'.

[streub]

I use option explicit and needed to declare ws. I receive a "1004" error at ".unmerge" and "clearcontents". What am I missing?

Additionally, how do I adapt the code to execute the changes to each sheet such as:

.Range("B1:B2").Value = Application.Transpose(Array("01", "13"))
.Range("B1:B2").Value = Application.Transpose(Array("02", "13")) and so on.

[sassora]

yes

[sassora]

Sorry, missed snb's reply