For Loop in vbScript

[MAMDO]

Hello Experts,

Need a little help. Main aim is to use a "for loop" for sheet 1 for copying rows to sheet 2 while the condition met but failed to do so.
Here The condition is, If E1 Cell will find value "A" the whole row from A1 to AH1 will be copied to Sheet 2 row (A3 to AH3) and this is how it continues till E100 cell. I believe the code is ok but need to run a loop which I constantly failed.

Public Sub CopyPaste()
Dim wbNew As Workbook
Dim strFolder, val
val = WeekdayName(Weekday(Now))

If Worksheets("Sheet1").Range("E1") = "A" Then
Worksheets("Sheet1").Range("A1:AH1").Copy Destination:=Worksheets("Sheet2").Range("A3")
Worksheets("Sheet2").Copy
MsgBox "Your Workbook has saved as Worksheet-" & FormatDateTime(Now, vbShortDate)
Set wbNew = ActiveWorkbook
If Len(Dir("E:VA\" & Day(Date) & "-" & MonthName(Month(Date), True) & "-" & Year(Date), vbDirectory)) = 0 Then
    MkDir "E:VA\" & Day(Date) & "-" & MonthName(Month(Date), True) & "-" & Year(Date)
End If
wbNew.SaveAs ("C:\NEW\" & Format(Now(), "DD-MMM-YYYY") & "\NEW-WorkSheet- " & "Sheet2" & ".xls")
End Sub

 

[NdNoviceHlp]

Hi MAMDO. You can trial this code. HTH. Dave

Public Sub CopyPaste()
Dim wbNew As Workbook
Dim strFolder, val
Dim LastRow As Integer, Cnt As Integer
val = WeekdayName(Weekday(Now))
With Sheets("Sheet1")
LastRow = .Range("E" & .Rows.Count).End(xlUp).Row
End With
For Cnt = 1 To LastRow
If Worksheets("Sheet1").Range("E" & Cnt) = "A" Then
Worksheets("Sheet1").Range("A" & Cnt & ":AH" & Cnt).Copy Destination:=Worksheets("Sheet2").Range("A" & Cnt + 2)
'Worksheets("Sheet2").Copy
Application.CutCopyMode = False
End If
Next Cnt
MsgBox "Your Workbook has saved as Worksheet-" & FormatDateTime(Now, vbShortDate)
Set wbNew = ActiveWorkbook
If Len(Dir("E:VA\" & Day(Date) & "-" & MonthName(Month(Date), True) & "-" & Year(Date), vbDirectory)) = 0 Then
MkDir "E:VA\" & Day(Date) & "-" & MonthName(Month(Date), True) & "-" & Year(Date)
End If
wbNew.SaveAs ("C:\NEW\" & Format(Now(), "DD-MMM-YYYY") & "\NEW-WorkSheet- " & "Sheet2" & ".xls")
End Sub

ps. please use code tags

 

[RobP]

or here, I just added the loop to the top of your code as such:

Public Sub CopyPaste()
Dim wbNew As Workbook
Dim strFolder, val
Dim x, y As Long
val = WeekdayName(Weekday(Now))

y = 3 'set first row for sheet2 ?

For x = 1 To 100
    If Worksheets("Sheet1").Range("E" & x) = "A" Then
        Worksheets("Sheet1").Range(Cells(x, 1), Cells(x, 34)).Copy Destination:=Worksheets("Sheet2").Range("A" & y)
        y = y + 1
    End If
Next x

    Worksheets("Sheet2").Copy
    MsgBox "Your Workbook has saved as Worksheet-" & FormatDateTime(Now, vbShortDate)
    Set wbNew = ActiveWorkbook
    
    If Len(Dir("E:VA\" & Day(Date) & "-" & MonthName(Month(Date), True) & "-" & Year(Date), vbDirectory)) = 0 Then
        MkDir "E:VA\" & Day(Date) & "-" & MonthName(Month(Date), True) & "-" & Year(Date)
    End If

    wbNew.SaveAs ("C:\NEW\" & Format(Now(), "DD-MMM-YYYY") & "\NEW-WorkSheet- " & "Sheet2" & ".xls")



End Sub

 

[Joe4]

I think your title is a bit confusing/misleading.
It says you are looking for "vbscript", but it looks like you clearly want "VBA".
Note that "vbscript" is something entirely different from VBA.
It is whole different a programming language unto itself, and is outside of Excel (see: VBScript - Wikipedia).

Just an FYI for posting future questions...

 

[MAMDO]

Thank you

NdNoviceHlp &​

RobP. Both the script works for me. I really appreciate your help.​

Joe4 - sorry, its my mistake that I mentioned VBscript instead of VBA. Cause I was too much into trouble of solving this. Thats why mistakenly I mentioned that.​

 

[Joe4]

Thank you

NdNoviceHlp &​

RobP. Both the script works for me. I really appreciate your help.​

Joe4 - sorry, its my mistake that I mentioned VBscript instead of VBA. Cause I was too much into trouble of solving this. Thats why mistakenly I mentioned that.​

No worries!

Just wanted to make sure that you understood the difference (to avoid any confusion down the road!).
Many people are not aware that VBA, VB, and vbScript are three different things (and not all the same).

 

[RobP]

you're welcome.. thanks for the feedback.

Rob