Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Excel macro only works on one sheet

Posted on 2014-01-12
4
508 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 27

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

856 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