suggest the mistake in this macro part

[Safal Shrestha]

This part of the macro is for pasting in a different sheet. I have the heading in row b3 of sheet named "i".. there is error in the line after the else command.



Sheets("i").Activate
Range("B3").Select
If Range("b3").Offset(1, 0) = "" Then
Selection.Offset(1, 0).Select
Else
Selection.Range(xlDown).Offset(1, 0).Select
End If
ActiveSheet.paste

[Rick Rothstein]

This part of the macro is for pasting in a different sheet. I have the heading in row b3 of sheet named "i".. there is error in the line after the else command.

Sheets("i").Activate
Range("B3").Select
If Range("b3").Offset(1, 0) = "" Then
Selection.Offset(1, 0).Select
Else
Selection.Range(xlDown).Offset(1, 0).Select
End If
ActiveSheet.paste
I am guessing that you meant the code line I highlighted in red to be this instead...

Selection.End(xlDown).Offset(1, 0).Select

Since you didn't post all your code, I cannot rewrite it for you because it ends by selecting a cell, but I would point out that it is rarely necessary to select cells in order to work with them. Whenever you see yourself writing something like this...

Range(..).Select
Selection.something

you can write it like this instead...

Range(..).something

Think about it... what does Selection.something mean? It means do "something" with the object you selected. But what is that object... it is the range you selected in the code line above... so you might as well work directly with the original object instead of working with a different object that references it. In effect, you already knew this because in your code, after you did this...

Range("B3").Select

you wrote this...

If Range("B3").Offset(1, 0) = "" Then

instead of writing this...

If Selection.Offset(1, 0) = "" Then

[Safal Shrestha]

I am a newbie in vba. I am trying to make my own simple macros. Thanks for the suggestions.
I will come back if i have any other problems.

[Safal Shrestha]

Below is the vba code for copying a data and then pasting it at the next empty line after the present data and then deleting the original data so that when i come back next time i can input data in the sheet again and do it again and again.

one problem is when it is my first time it goes to the end of the column b. so i wanted to keep a check and do it accordingly. Can you recommend the code for me? thanks


Sub Macro1()
Dim jpt As Worksheet
Dim i As Worksheet
Dim sheet1 As Worksheet

Sheets("jpt").Activate
Sheets("jpt").Range("b3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("i").Activate
Range("B3").Select
If Selection.Offset(1, 0) = "" Then
ActiveSheet.paste
Else
Selection.End(xlDown).Offset(1, 0).Select
ActiveSheet.paste
End If

Sheets("jpt").Activate
Sheets("jpt").Range("b3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete
End Sub

[Excel Fox]

Try this..


Sub Macro1()

Dim lng As Long
With Sheets(1)
lng = .Range("B3").End(xlToRight).End(xlDown).Row
If lng = Rows.Count Then lng = 3
With .Range(.Range("b3"), .Range("b3").End(xlToRight)).Resize(lng - 2)
.Copy
With Sheets(2).Range("B3")
If .Offset(1).Value = "" Then
.Offset(1).PasteSpecial
Else
.End(xlDown).Offset(1, 0).PasteSpecial
End If
Application.Goto .Cells(1)
End With
.Delete
End With
End With

End Sub

[snb]

this will suffice:


Sub M_snb()
Sheets("jpt").Range("b3").currentregion.cut sheets(i).cells(rows.count,1).end(xlup).offset(1)
End Sub

[Excel Fox]

There are quite a few assumptions that you are taking there when suggesting that snb. For example, that will only suffice if there are NO adjacent columns in the contiguous cells in column A or rows above.... there are many other that can be highlighted....

[snb]

I know, but as long as the TS doesn't provide any more information, the code can be applicable to the question posed. But I only give suggestions, no solutions. The TS may adapt the code ad libitum.

[Safal Shrestha]

Thanks a lot guys. I am working on the suggestions and trying to understand those codes. Thanks again