• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro Not working

D

delta

Member
in below code debug error will show in blue color Text.
what is in wrong


Sub copyNonBlankData()
Dim erow As Long, lastrow As Long, i As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Sheet1.Cells(i, 1) <> “” Then

Sheets(“sheet1”).Range(Cells(i, 1), Cells(i, 2)).Copy

Sheets(“sheet2”).Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Sheets(“sheet2”).Range(Cells(erow, 1), Cells(erow, 2))
Sheets(“sheet1”).Activate
End If
Next i
Application.CutCopyMode = False
End Sub
 
Code: 
Option Explicit

Sub copyNonBlankData()
Dim erow As Long, lastrow As Long, i As Long
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
If Sheet1.Cells(i, 1) <> "" Then

Sheets("sheet1").Range(Cells(i, 1), Cells(i, 2)).Copy

Sheets("sheet2").Activate
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ActiveSheet.Paste Destination:=Sheets("sheet2").Range(Cells(erow, 1), Cells(erow, 2))
Sheets("sheet1").Activate
End If
Next i
Application.CutCopyMode = False
End Sub

I am presuming you obtained the macro code from the Internet ? The quotation marks used in the code are common (from my knowledge) with European countries. The quotation marks slant inwards - which is different from the quotation marks used in the US, which display vertically.

I edited the macro to the above and it works here. Does it work there ?
 
Delta

Simply retype all the characters that look like "

A lot of Blog sites change them to opposing quotes which is the problem here
 
Hi !

Without the error code (is it so difficult to post a complete explanation ?‼)
I presume it's just an obvious misunderstanding of the EOM
(Excel object model) as the Cells without any worksheet reference
are linked to the active sheet and not to the worksheet Sheet1 ! :rolleyes:
(As I saw any link with some european countries …)
​
 
You must log in or register to reply here.
Back
Top