Excel: Loop through different worksheets

Question

I have written a macro that pastes values in a column of each worksheet. When I run the code it just loops through the active worksheet a couple of times. I have tried adding ws. to the Range in the code but I get a compile error.

Can anybody help me with this?

Sub Aanvullen_adminitsratie_data()

    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets

With ws
   Range("K2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("AA1").Copy
    Range("K2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("J1048576").Select
    Selection.End(xlUp).Select
    Selection.Offset(0, 1).Select
    Range(Selection, Selection.End(xlUp)).Select
    Selection.FillDown
End With

    Next ws


   End Sub

Welcome. You've got several issues with your code. The first thing is to avoid using select statements to set values. http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros — peege, Dec 18 '14 at 11:01
Avoid using Select and Selection, [see here](http://stackoverflow.com/a/10717999/445425) — chris neilsen, Dec 18 '14 at 11:02
If you could revise your question to explain further what you are trying to clear, copy, etc. Specifically, with sheet names and ranges. If you can break it down into simple steps, like, Go through Column A on sheet1 and see if the value matches any row in sheet2. If it does, I want to clear this RANGE.. — peege, Dec 18 '14 at 11:03
What i am trying to do is to add a certain value to a column in all the sheets in my workbook. The sheets all have the same layout and the value that needs to be pasted in the column is located in cel AA1. The first step is to delete the contents in column K (except K1, because it contains a header. Since the set of data has a variable set of rows I paste the value in K2 and select the rest of the colum by finding the last row in colum J. offset to the right and filldown — user4373888, Dec 18 '14 at 11:11
Thanks for the info, i have been doing some reading on the select & selection articles and I am not totally sure of how it works but I have found out a method that works. It's the following code: — user4373888, Dec 18 '14 at 12:55

score 0 · Accepted Answer · answered Dec 18 '14 at 12:56

0

Thanks for the info, i have been doing some reading on the select & selection articles and I am not totally sure of how it works but I have found out a method that works. It's the following code:

Sub Aanvullen_adminitsratie_datatest()

    Dim ws As Worksheet
    Dim lastRow As Integer
    For Each ws In ActiveWorkbook.Worksheets

With ws
   lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
   ws.Range("AA1").Copy
   ws.Range("K2:K" & lastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

 End With

    Next ws


   End Sub

answered Dec 18 '14 at 12:56

user4373888

53
1
12

When you add a With statement like `With ws`, you do not need to qualify each property of the worksheet with `ws` again. Your lines could be `lastRow = .Cells(Rows.Count, 1).End(xlUp).Row`, `.Range("AA1").Copy`, & `.Range("K2:K" & lastRow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False`. – TheEngineer Dec 18 '14 at 14:00
Or you could remove your `With ws` and `End With` lines and qualify each line individually as you have them. – TheEngineer Dec 18 '14 at 14:02

Excel: Loop through different worksheets

1 Answers1