Solved

Excel macro only works on one sheet

Posted on 2014-01-12
4
462 Views
Last Modified: 2014-01-12
Hello,
I would like this macro to run on sheets 1-4 but it just repeats itself on Sheet1 four times instead of moving on to Sheet 2 etc...

Sub FillColumns1()

    Dim r1 As Long, r2 As Long, i As Long
    Dim ws As Worksheet
    Dim wsCounter As Long


    'For wsCount = 1 To ActiveWorkbook.Worksheets.Count
      For wsCount = 1 To 4
        Set ws = ActiveWorkbook.Worksheets(wsCount)
        With ws
            r1 = .range("A" & .Rows.Count).End(xlUp).Row
            r2 = .range("H" & .Rows.Count).End(xlUp).Row
            range(Cells(r2, 8), Cells(r2, 22)).AutoFill range(Cells(r2, 8), Cells(r1, 22))
        End With
    Next

End Sub
0
Comment
Question by:CC10
  • 2
4 Comments
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39774729
What is it:
Dim wsCounter As Long
or
For wsCount = 1 To 4
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39774757
Try

            range(ws.Cells(r2, 8), ws.Cells(r2, 22)).AutoFill range(ws.Cells(r2, 8), ws.Cells(r1, 22))
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 300 total points
ID: 39774759
or simply

            range(.Cells(r2, 8), .Cells(r2, 22)).AutoFill range(.Cells(r2, 8), .Cells(r1, 22))
0
 

Author Closing Comment

by:CC10
ID: 39774905
Thats fine. Thanks very much.
CC
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now