Microsoft Excel - Do while loop

Hi
I'm trying to attach my file but I'm having trouble do it. I click on the attach icon zip file upload manger and try to attach my winzip file but the document manager does not indicate where and how to attach my zip file.

Hi Lionell,
At the top of the Document Manager Dialog Box, there is a button with Upload and a green plus sign on it.  You click that button and it takes you to a browse function where you and navigate to and select the zip file on your computer that you want to upload.  When you have selected the file it appears in a window and you then press a button at the botton that transfers to file to the server.  When it appears in the server list you select it and click Insert.
Hope this helps.
Harry

Hello Lionel,
I have downloaded your file, but I have other things on at the moment so it might take a day or so for me to have a look at it bu I will as soon as I get a chance.
Regards
Harry

Try this if this is what you want...I read from your code, then adjust for proper coding.

Pichart-Completed SC_Monitoring_Tool.zip

Hope this help

pichart

Hi Pichart,

Thank you for taking the time to look at this project. I truly appreciate it. You are on track, but I only need the input boxes to only come up one time, not each time a new template is created. The orginal input boxes information should be applied to each new template. Everything else is great and I thank you again.

Hi Harry,

Thank you so much. I will wait for your reply to see you magic.

Hi great, then

Just move that part to place above the loop..(for each...)

------------------------- so the code will be like this ---------------------------------------

Sub Test1()
SrcSh = "ACW-Participant"
lastRowsSource = Sheets(SrcSh).Range("FE" & Rows.Count).End(xlUp).Row


    Sheets("Template").Copy After:=Sheets("Template")
    ActiveSheet.Name = "Result"
    targetSh = ActiveSheet.Name
 i = 1
 
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
  
    Sheets(SrcSh).Range("A" & cell.Row - 1).Copy
    Sheets(targetSh).Range("E12").PasteSpecial xlPasteValues
    
    Sheets(SrcSh).Range("A" & cell.Row).Copy
    Sheets(targetSh).Range("E14").PasteSpecial xlPasteValues
    
    i = i + 50
        Sheets("Result").Range("A1:R43").Copy
    Range("A" & i).PasteSpecial xlPasteAll
    
    
    End If
Next cell
    
End Sub
--------------------------------------- end of code ---------------------------

attachment -->Pichart-Completed SC_Monitoring_Tool2.zip
pichart Y.

Hi Pichart,

Thanks looks great!!,but for some reason the code paste a duplicate of the first and last template on the result page. This is not a big deal or problem, but I was wondering if it can be eliminated. Thanks again.

Hi Pichart,

I tried to figure it out myself but it did not work. Instead of copying "A" cell in the code below, I need it to copy the information in the "E" cell. I tried to change the "A" to an "E" and remove the -1, but no success. If you have a moment can you update this. I promise I will not bother you no more and thank you again.

[code]Sheets(SrcSh).Range("A" & cell.Row - 1).Copy
Sheets(targetSh).Range("E12").PasteSpecial xlPasteValues[code]

SC_Monitoring_Tool.zip

Hi Harry,

I tried to figure this out myself but it did not work. Instead of copying "A" cell in the code below, I need it to copy the information in the "E" cell. I tried to change the "A" to an "E" and remove the -1, but no success. If you have a moment can you update this. I promise I will not bother you no more and thank you again.

[code]Sheets(SrcSh).Range("A" & cell.Row - 1).Copy
Sheets(targetSh).Range("E12").PasteSpecial xlPasteValues[code]

SC_Monitoring_Tool.zip

Hello Lionell,
I haven't had time to delve into the depths but if the code as such is working for the A cell then you should just have to change it to E.  The -1 would need to stay.
I will try to have a closer look sometime when I get a chance but probably not in the next day and a half.
Regards
Harry

Hi Harry,

I figured it out. Thanks for getting back to me. I'm trying something new to add to it. I want to have a macro that will generate a page number for each template created. If I can't figure it out I will message you for help. Thanks again as always.

Hi,

Is this better way, 
put this new 2 lines, to delete the most upper 50 lines then insert new 4 columns, then the 1st line of data will be on E1

Sub Test1()
SrcSh = "ACW-Participant"
lastRowsSource = Sheets(SrcSh).Range("FE" & Rows.Count).End(xlUp).Row


    Sheets("Template").Copy After:=Sheets("Template")
    ActiveSheet.Name = "Result"
    targetSh = ActiveSheet.Name
 i = 1
 
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
  
    Sheets(SrcSh).Range("A" & cell.Row - 1).Copy
    Sheets(targetSh).Range("E12").PasteSpecial xlPasteValues
    
    Sheets(SrcSh).Range("A" & cell.Row).Copy
    Sheets(targetSh).Range("E14").PasteSpecial xlPasteValues
    
    i = i + 50
        Sheets("Result").Range("A1:R43").Copy
    Range("A" & i).PasteSpecial xlPasteAll
    
    
    End If
Next cell
Range("1:50").EntireRow.Delete
Range("A:D").EntireColumn.Insert
End Sub

this new attachment ---> Pichart-Completed SC_Monitoring_Tool2.zip

Hi Pichart y.

Thank you once again for your response and help. I figured out the how to do it. However, I got one last part that I'm trying to put together. I have another forum assisting in this project but so far no one has figured it out.

Whenever a row is "UNMET" in the SC_Monitoring Tool the Macro Copies and Paste the determine sections to the Template "Finding" section, however there are sub sections rows on the SC_Monitoring Tool for instance row 4,5,6,7,8 ect, ect that may need to be added to the template section "Finding" section if needed. Is there a way to have an message box pop up whenever a row is "UNMET" and say "You are at row number ( ) Are there any additional sub rows" if so the user can type the rows that need to be added into the input box and those row will be copied and pasted to that "Finding" section of the template and continue on to the next "UNMET" row. I know this part seems very difficult and I can't imagine how to do it, but I know that you like a challenge and can solve anything. If you can make this happen. Thank you in advance.

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
    
    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
        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

SC_Monitoring_Tool Revised.zip
Sub-section Example Page.zip

Hi Pichart,
Found this. Please see code below. The number 3 for example is not constant it will be variable according to the row. If the user selects yes a new input box will ask to enter the rows to be copied and paste in the template and continue to look for the next "UNMET", if the user selects no the macro will continue to look for the next "UNMET"
Hope this helps. Thank you again.

Sub Macro1()
'
' Macro1 Macro
'

'

response = MsgBox("You are at row number 3 are there any additional subset?", vbYesNo)
 
If response = vbNo Then
    MsgBox ("Macro Ending")
    Exit Sub
End If
 
'rest of code here.
End Sub

Sorry, so busy I am, in these days...

I want to help so, for quick I use my previous file...with some new feature..

----------------------------------
Sub Test1()
SrcSh = "ACW-Participant"
lastRowsSource = Sheets(SrcSh).Range("FE" & Rows.Count).End(xlUp).Row


    Sheets("Template").Copy After:=Sheets("Template")
    ActiveSheet.Name = "Result"
    targetSh = ActiveSheet.Name
 i = 1
 
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
    Sheets(SrcSh).Range("A" & cell.Row - 1).Copy
    Sheets(targetSh).Range("E12").PasteSpecial xlPasteValues
    Sheets(SrcSh).Range("A" & cell.Row).Copy
    Sheets(targetSh).Range("E14").PasteSpecial xlPasteValues
    
response = MsgBox("You are at row number" & cell.Row & " are there any additional subset?", vbYesNo)
If response = vbYes Then
    subsetRw = InputBox("Please specify the Additional Subset")
    MsgBox ("You had enter the line number" & subsetRw & " to be additional Info--- you can replace this msgbox with your code")
    
End If


i = i + 50
        Sheets("Result").Range("A1:R43").Copy
    Range("A" & i).PasteSpecial xlPasteAll
    
    
    End If
Next cell
Range("1:50").EntireRow.Delete
Range("A:D").EntireColumn.Insert
End Sub
----------------------------------



hope this help.

pichart Y.