Results 1 to 1 of 1

Loop cycle not copying correct cells

Threaded View

  1. #1
    Registered User
    Join Date
    03-10-2013
    Location
    Pennsylvania
    MS-Off Ver
    Excel 2007
    Posts
    26

    Loop cycle not copying correct cells

    Hello Forum,

    I have code that is working but not copying the correct information as it goes through the loop cycle.
    For example: If I want to select "LOVE", "SUNSHINE", and "TOGETHER" from Worksheet ACW cells B4,B6, and B8 and then next loop select "ALWAYS" in ACW Worksheet cell B11 and on next loop select "WONDERFUL" in ACW Worksheet cell B16 after the run of program information on the Template Worksheet is incorrect. The Template Worksheet should have the words "LOVE","SUNSHINE",and "TOGETHER" in Template worksheet cell's E15,E16 and E17 and the word "ALWAYS" by itself only in cell E65 and lastly the word "WONDERFUL" by itself only in cell E115. For some reason the program is adding more information then needed in the Template Worksheet. Please see code below along with example attachment. Thank in advance. This forum is very helpful and I Thank you again.

    Option Explicit
    
    Sub Test1()
    
        Dim SrcSh, targetSh As String
        Dim i, x, lastRowsSource As Integer
        Dim a As Long       '<== Counter
        Dim cell As Range   '<== Counter
        Dim rngCopyFrom As Range
        
        Application.ScreenUpdating = False
        SrcSh = "ACW-Participant"
        lastRowsSource = Sheets(SrcSh).Range("FE" & Rows.Count).End(xlUp).Row
        Sheets("Template").Visible = True
        Sheets("Template").Copy After:=Sheets(SrcSh)
        ActiveSheet.Name = "Result"
        targetSh = ActiveSheet.Name
        Sheets("Template").Visible = False
        i = 1
        x = 0
        Application.ScreenUpdating = True
        Sheets(targetSh).Range("B9") = InputBox("Provider's MA Number")
        Sheets(targetSh).Range("B10") = InputBox("Provider's Agency")
        Sheets(targetSh).Range("B11") = InputBox("Provider's Address")
        Sheets(targetSh).Range("K9") = InputBox("Program Specialist")
        Sheets(targetSh).Range("K11") = InputBox("Contact E-Mail")
        Sheets(targetSh).Range("O10") = InputBox("Monitoring Dates")
        
        For Each cell In Sheets(SrcSh).Range("FE3:FE" & lastRowsSource)
            If cell = "UNMET" Then
            On Error Resume Next
        Set rngCopyFrom = Application.InputBox("Select the range you want to copy from", Type:=8)
        On Error GoTo 0
    
        If Not rngCopyFrom Is Nothing Then
            rngCopyFrom.Copy ThisWorkbook.Sheets("Result").Range("E15")
        End If
                If x > 0 Then
                    i = i + 50
                    Sheets("Result").Range("A1:R45").Copy
                    Range("A" & i).PasteSpecial xlPasteAll
                End If
                    
                Sheets(SrcSh).Range("E" & cell.Row).Copy
                Sheets(targetSh).Range("E12").PasteSpecial xlPasteValues
                
                Sheets(SrcSh).Range("A" & cell.Row).Copy
                Sheets(targetSh).Range("E14").PasteSpecial xlPasteValues
                x = x + 1
                Range("C" & 13 + i) = "Finding # " & x      '<== Finding Counter
                Range("H" & 44 + i) = x                     '<== Finding Counter
            End If
        Next cell
        
        For a = 45 To x * 50 Step 50    '<== Page Counter
            Range("J" & a) = x          '<== Page Counter
        Next a                          '<== Page Counter
        
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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