move specific data sheet1 to sheet2

[Rogeman]

Hi All,
There are many "move to sheet2" answers on the forum but most depend on the user selecting the data themselves I would like to avoid this if possible.
I have found this code and adapted it to suit my requirements but it stops working at the line in bold, can anyone explain please?
There may be other ways of achieving my goal but I need to use VBA code so that the user (My Wife) does not need to do anything technical, like using filters/cut & Paste etc.
I have a "Membership list" of addresses etc. and monies paid, what I am doing is when they pay their subs I need to move the row of data to Sheet2 from Sheet1.
A "Paid" column has the money in, all zeros until payment is made, I then use that value to move the rows of data for each individual.
Some of the rows have empty cells in does this make a difference or is it ok?

Sub myMOVE()
'Let's start at row 2. Row 1 has headers
x = 2
'Start the loop
Do While Cells(x, 1) <> ""
'Look for data with 'value of 5 or more'
If Cells(x, 1) > "5" Then
'copy the row if it contains '6 or more'
Worksheets("Sheet1").Rows(x).Copy
'Go to sheet2. Activate it. We want the data here
Worksheets("Sheet2").Activate
'Find the first empty row in sheet2
erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'Paste the data here
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
End If
'go to sheet1 again and actvate it
Worksheets("Sheet1").Activate
'Loop through the other rows with data
x = x + 1
Loop
End Sub

EXCEL 2003

I hope my explanation is clear

Thanks in advance

Roger

 

[JLGWhiz]

Sheet2 as it is used in the statement would be a code name for a sheet in the workbook. If you do not have a sheet with that code name, but you want to refer to Sheets("Sheet2") then use the full reference as I just did. Or use the actual code name for the sheet which can be found by opening the VB editor and looking in the projects pane at upper left. It shows both the code name and the common name in parentheses for each sheet.

 

[oldbrewer]

bill

0

click here to move records of people who have paid to page 2 <tbody> </tbody> <tbody> </tbody>

fred

25

fred

25

dan

25

dan

25

sam

25

sam

25

dave

25

dave

25

whenever the yellow box is clicked, ALL the records of people who have paid

will be transferred to sheet 2 (which will be emptied just prior to transfer

the above is a simulated page 2

the macro has been assigned to a yellow rectangle just to the right

of the table of payers and non payers

For k = 1 To 20

Cells(k, 11) = Cells(1, 100)

Cells(k, 12) = Cells(1, 100)

Next k

For j = 1 To 20

If Cells(j, 2) > 0 Then Sum = Sum + 1: Cells(Sum, 11) = Cells(j, 1): Cells(Sum, 12) = Cells(j, 2)

Next j

<colgroup><col span="15"></colgroup><tbody>
</tbody>

 

[JLGWhiz]

Hi All,
There are many "move to sheet2" answers on the forum but most depend on the user selecting the data themselves I would like to avoid this if possible.
I have found this code and adapted it to suit my requirements but it stops working at the line in bold, can anyone explain please?
There may be other ways of achieving my goal but I need to use VBA code so that the user (My Wife) does not need to do anything technical, like using filters/cut & Paste etc.
I have a "Membership list" of addresses etc. and monies paid, what I am doing is when they pay their subs I need to move the row of data to Sheet2 from Sheet1.
A "Paid" column has the money in, all zeros until payment is made, I then use that value to move the rows of data for each individual.
Some of the rows have empty cells in does this make a difference or is it ok?




EXCEL 2003

I hope my explanation is clear

Thanks in advance

Roger

Give this a try and see if it will run for you.

Sub CopiStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, lr As Long, rng As Range, c As Range
Set sh1 = Sheets("Sheet1")
Set sh2 = Sheets("Sheet2")
lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh1.Range("A2:A" & lr)
    For Each c In rng
        If c.Value > 5 Then
            c.EntireRow.Copy sh2.Cells(Rows.Count, 1).End(xlUp)(2)
        End If
    Next
End Sub

 

[JLGWhiz]

ADVERTISEMENT

bill

click here to move records of people who have paid to page 2 <TBODY> </TBODY> <TBODY> </TBODY>

fred

25

fred

25

dan

25

dan

25

sam

25

sam

25

dave

25

dave

25

whenever the yellow box is clicked, ALL the records of people who have paid

will be transferred to sheet 2 (which will be emptied just prior to transfer

the above is a simulated page 2

the macro has been assigned to a yellow rectangle just to the right

of the table of payers and non payers

For k = 1 To 20

Cells(k, 11) = Cells(1, 100)

Cells(k, 12) = Cells(1, 100)

Next k

For j = 1 To 20

If Cells(j, 2) > 0 Then Sum = Sum + 1: Cells(Sum, 11) = Cells(j, 1): Cells(Sum, 12) = Cells(j, 2)

Next j

<TBODY>
</TBODY>

I think oldbrewer might have made an errant post!

 

[oldbrewer]

old brewer meant to post this - it shows the principle of moving data using a simple to understand very basic (geddit) macro...

 

[JLGWhiz]

ADVERTISEMENT

old brewer meant to post this - it shows the principle of moving data using a simple to understand very basic (geddit) macro...

My apologies, JLG

 

[Rogeman]

JLG,

Thanks for your reply, I checked out you code but did not quite understand all of it as I am not too good at VBA.
The code that I originaly posted I have gone back to basics and checked it out on a bare book1, with sheet1, and sheet2.
I entered various numbers in a small table the code worked perfectly it moved the correct rows to sheet2 as expected.
When I tried to use it in my full data base of "members" it worked up the point where it needed to paste into sheet2, the copy bit was ok when I stepped through on F8.
The paste bit did not work though......
I was able to step through ok each line looked good but no paste, yet it worked in the test database.
What could be wrong with the full database? I have deleted some random cell data in the test dbase to see if the spaces were the cause but all worked ok.
I would also like the data that is moved to sheet2 to be removed from the original Sheet1.

Thanks again for you help

Roger

 

[Rogeman]

Oldbrewer,

Thanks for your "Erant" post.
I have not yet tried it but time is against me now, so will try tomorrow.
Will get back to you soon.

Roger

 

[JLGWhiz]

JLG,

When I tried to use it in my full data base of "members" it worked up the point where it needed to paste into sheet2, the copy bit was ok when I stepped through on F8.
The paste bit did not work though......
I was able to step through ok each line looked good but no paste, yet it worked in the test database.
What could be wrong with the full database? I have deleted some random cell data in the test dbase to see if the spaces were the cause but all worked ok.
I would also like the data that is moved to sheet2 to be removed from the original Sheet1.

Thanks again for you help

Roger

Did you change this line from

erow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

To this

erow = Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Also suggest you change this from

ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)

To

ActiveSheet.Paste Destination:=Worksheets("Sheet2").Range("A" & erow)