View Full Version : [SOLVED:] Loop for worbook and worksheet changes
streub
12-06-2013, 12:01 PM
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
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
12-06-2013, 07:59 PM
The red squares represent where I should place a dot, correct?
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
12-07-2013, 06:06 AM
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
12-07-2013, 10:12 AM
yes
sassora
12-07-2013, 10:16 AM
Sorry, missed snb's reply
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.