Run Time error '1004':
 

Hi All,
I'm getting the following error in my macro code:

Run Time error '1004':
Application-defined or object-defined error

My Code:
Dim r, nLastRow, ii, cc, nLastNewRow, nLTempA
Sheet4.Activate
With Sheet4
With .Range("A:Z")
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1
For ii = 2 To nLastRow
LColBTestA = "A" & CStr(ii)
If .Range(LColBTestA).Cells.Interior.ColorIndex = xlNone Then
Sheet4.Range(LColBTestA).Cells.Select
Selection.Copy
Sheet5.Activate
If Len(Sheet5.Range("A2").Value) = 0 Then
Sheet5.Range("A2").Select
Application.ActiveSheet.Paste
Else
Set cc = ActiveSheet.UsedRange
nLastNewRow = cc.Rows.count + cc.Row - 1
nLTempA = "A" & CStr(nLastNewRow + 1)
Sheet5.Range(nLTempA).Select
Application.ActiveSheet.Paste
End If
End If
Next
End With
End With

Here i'm getting error on the following line:
Sheet4.Range(LColBTestA).Cells.Select

Plz help me to complete this code.....

Thanks,
JP

Run Time error '1004':
 

Not too sure exactly why you need to select the range but suffice it to say
that in order to select the range you must first select the sheet.

sheet4.select
Sheet4.Range(LColBTestA).Cells.Select
--
HTH...

Jim Thomlinson


"Prabu" wrote:

Hi All,
I'm getting the following error in my macro code:

Run Time error '1004':
Application-defined or object-defined error

My Code:
Dim r, nLastRow, ii, cc, nLastNewRow, nLTempA
Sheet4.Activate
With Sheet4
With .Range("A:Z")
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1
For ii = 2 To nLastRow
LColBTestA = "A" & CStr(ii)
If .Range(LColBTestA).Cells.Interior.ColorIndex = xlNone Then
Sheet4.Range(LColBTestA).Cells.Select
Selection.Copy
Sheet5.Activate
If Len(Sheet5.Range("A2").Value) = 0 Then
Sheet5.Range("A2").Select
Application.ActiveSheet.Paste
Else
Set cc = ActiveSheet.UsedRange
nLastNewRow = cc.Rows.count + cc.Row - 1
nLTempA = "A" & CStr(nLastNewRow + 1)
Sheet5.Range(nLTempA).Select
Application.ActiveSheet.Paste
End If
End If
Next
End With
End With

Here i'm getting error on the following line:
Sheet4.Range(LColBTestA).Cells.Select

Plz help me to complete this code.....

Thanks,
JP

Run Time error '1004':
 

Thanks Jim, Let me try now.

"Jim Thomlinson" wrote:

Not too sure exactly why you need to select the range but suffice it to say
that in order to select the range you must first select the sheet.

sheet4.select
Sheet4.Range(LColBTestA).Cells.Select
--
HTH...

Jim Thomlinson


"Prabu" wrote:

Hi All,
I'm getting the following error in my macro code:

Run Time error '1004':
Application-defined or object-defined error

My Code:
Dim r, nLastRow, ii, cc, nLastNewRow, nLTempA
Sheet4.Activate
With Sheet4
With .Range("A:Z")
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1
For ii = 2 To nLastRow
LColBTestA = "A" & CStr(ii)
If .Range(LColBTestA).Cells.Interior.ColorIndex = xlNone Then
Sheet4.Range(LColBTestA).Cells.Select
Selection.Copy
Sheet5.Activate
If Len(Sheet5.Range("A2").Value) = 0 Then
Sheet5.Range("A2").Select
Application.ActiveSheet.Paste
Else
Set cc = ActiveSheet.UsedRange
nLastNewRow = cc.Rows.count + cc.Row - 1
nLTempA = "A" & CStr(nLastNewRow + 1)
Sheet5.Range(nLTempA).Select
Application.ActiveSheet.Paste
End If
End If
Next
End With
End With

Here i'm getting error on the following line:
Sheet4.Range(LColBTestA).Cells.Select

Plz help me to complete this code.....

Thanks,
JP

Run Time error '1004':
 

Here i'm getting error on the following line:
Sheet4.Range(LColBTestA).Cells.Select

You need to first Activate Sheet4, before you can Select a range in it.

Run Time error '1004':
 

Again Thanks Jim. Your solution is working fine. Actually in this code i'm
checking values from one sheet which has marked in White color. Here i'm
copying them and pasting into another sheet.

Rgds,
JP

"Jim Thomlinson" wrote:

Not too sure exactly why you need to select the range but suffice it to say
that in order to select the range you must first select the sheet.

sheet4.select
Sheet4.Range(LColBTestA).Cells.Select
--
HTH...

Jim Thomlinson


"Prabu" wrote:

Hi All,
I'm getting the following error in my macro code:

Run Time error '1004':
Application-defined or object-defined error

My Code:
Dim r, nLastRow, ii, cc, nLastNewRow, nLTempA
Sheet4.Activate
With Sheet4
With .Range("A:Z")
Set r = ActiveSheet.UsedRange
nLastRow = r.Rows.count + r.Row - 1
For ii = 2 To nLastRow
LColBTestA = "A" & CStr(ii)
If .Range(LColBTestA).Cells.Interior.ColorIndex = xlNone Then
Sheet4.Range(LColBTestA).Cells.Select
Selection.Copy
Sheet5.Activate
If Len(Sheet5.Range("A2").Value) = 0 Then
Sheet5.Range("A2").Select
Application.ActiveSheet.Paste
Else
Set cc = ActiveSheet.UsedRange
nLastNewRow = cc.Rows.count + cc.Row - 1
nLTempA = "A" & CStr(nLastNewRow + 1)
Sheet5.Range(nLTempA).Select
Application.ActiveSheet.Paste
End If
End If
Next
End With
End With

Here i'm getting error on the following line:
Sheet4.Range(LColBTestA).Cells.Select

Plz help me to complete this code.....

Thanks,
JP

Run Time error '1004':
 

Ian,
Already i've activated the sheet before going to select. U could see in my
code. Jim has given solution for that. Anyway thanks to u for spending time
on my query.

"IanKR" wrote:

Here i'm getting error on the following line:
Sheet4.Range(LColBTestA).Cells.Select

You need to first Activate Sheet4, before you can Select a range in it.