Results 1 to 7 of 7

Trying to clean up & simplefy some VBA

Threaded View

  1. #1
    Forum Contributor
    Join Date
    11-26-2008
    Location
    Ohio
    MS-Off Ver
    2016
    Posts
    326

    Trying to clean up & simplefy some VBA

    I am trying to clean up some code where I have simplified what it does. (original code below)
    Upon a macro button push, (on pg "s") a summary prints info on a series of cells from each of many sheets.
    (see code below)
    The code as written puts the info from each page (F71 through F85) summarized on the "s" page
    on cells BX7 through BX 23.
    Then the info from each page F90 - F107, summarizes on the "S" page starting at BX26.
    I want to change this code, so I am only pulling only one section from each page. In this case, I would
    be pulling info from each page F-71 through F101, to the summary page "S" and the info would show up
    on the "s" page, at cells BX7, down through BX 67.

    Can someone change / simplify this code for me? Every time I try to edit it, I just get errors.
    Also, I am not very good at this code stuff, so hopefully someone can edit it for me.
    Thanks in advance!!!! (I'm using excel 2007)

    Sub copytoS()
    Dim WS As Worksheet
    Dim nrow As Long
    Dim rng2 As Range
    Dim rng3 As Range
    Dim rng4 As Range
    nrow = 7
    nrow2 = 43
    
    Application.ScreenUpdating = False
    
    Set rng4 = Range(Sheets("S").Cells(7, 76), Sheets("S").Cells(43, 76))
    rng4.ClearContents
    Set rng4 = Range(Sheets("S").Cells(26, 76), Sheets("S").Cells(55, 76))
    rng4.ClearContents
    
    For Each WS In ActiveWorkbook.Worksheets
    If WS.Name <> "1" And WS.Name <> "2" And WS.Name <> "T" And WS.Name <> "S" Then
    Set rng2 = WS.Range("F71", "F94")
    For Each F In rng2
    If IsError(F.Value) = False Then
    If F.Value <> "" Then
    F.Copy
    Set rng3 = Range(Sheets("S").Cells(nrow, 76), Sheets("S").Cells(nrow, 76))
    rng3.PasteSpecial Paste:=xlPasteValues
    nrow = nrow + 1
    End If
    End If
    Next F
    End If
    Next WS
    
    For Each WS In ActiveWorkbook.Worksheets
    If WS.Name <> "1" And WS.Name <> "2" And WS.Name <> "T" And WS.Name <> "S" Then
    Set rng2 = WS.Range("F90", "F104")
    For Each F In rng2
    If IsError(F.Value) = False Then
    If F.Value <> "" Then
    F.Copy
    Set rng3 = Range(Sheets("S").Cells(nrow2, 76), Sheets("S").Cells(nrow2, 76))
    rng3.PasteSpecial Paste:=xlPasteValues
    nrow2 = nrow2 + 1
    End If
    End If
    Next F
    End If
    Next WS
    Application.ScreenUpdating = True
    End Sub
    Last edited by Leith Ross; 01-06-2017 at 12:56 PM. Reason: Fixed Code Tags

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Coding Convention Questions #2 (To clean up or not to clean up)
    By mc84excel in forum Excel Programming / VBA / Macros
    Replies: 24
    Last Post: 03-31-2014, 08:18 PM
  2. [SOLVED] VBA Code to optimize and clean data- clean out numerical/ or symbol
    By tracylsr in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-05-2012, 04:21 PM
  3. VBA code clean up help
    By recon427 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 02-17-2012, 01:19 AM
  4. Clean Function to Clean Entire Sheet
    By gema in forum Excel General
    Replies: 7
    Last Post: 11-05-2009, 10:07 AM
  5. clean up help!!
    By manny1975 in forum Excel General
    Replies: 7
    Last Post: 04-24-2008, 01:41 PM
  6. [SOLVED] Clean up help
    By Andy in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-04-2006, 05:10 AM
  7. Help me clean this up...
    By BigDave in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-17-2005, 10:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1