For Loop

[dev.sutradhar1987]

Hi All,


I Hsve created a code with a loop


Sub CountPaste()


Dim i, cnt As Integer


Dim wb As Workbooks

On Error GoTo ErrHandler:

'This Code has been created by Dev Sutradhar


'Workbooks.Open ("D:UsersdsutradhDesktopConfidentialNew folderMaster File_23072013.xlsx")


'Workbooks.Open ("D:UsersdsutradhDesktopConfidentialNew folderMaster File_23072013.xlsx")

For i = 6 To 1000

Workbooks("Master File_23072013.xlsx").Activate

If Not Sheets("Consolidated").Cells(i, 1).Value = "" Then

Sheets("Consolidated").Cells(i, 1).Select

Selection.Copy

Workbooks("Template for VP information_base file.xlsm").Activate

Sheets("Template for approval").Range("E16").Select

ActiveCell.PasteSpecial

Call NewSheet

Call Hyperlink


Else

Exit Sub

End If

Next i

ErrHandler: MsgBox Err.Number & vbCrLf & Err.Description, vbCritical, "Error!"

End Sub


But the problem it i am geeting an error that "Select method of Range Class Failed".


Could anyone help me please..

 

[Marc L]

Hi !


You don't need to use these awful  Activate  /  Select  to copy cells ‼


Just read the  Range.Copy  method's help & example and use its  Destination  argument …