Copy 20 rows at a time to new page and delete original rows

I have a worksheet called "Import" that can contain up to 200 rows of data ("C4:I203) although it will vary. I want to copy 20 rows at a time (or whatever remains if less than 20) starting with the bottom row and working to the top) to another sheet called (Data)-{available space ("C4:I23")}. After copying the 20 rows I want it to delete the original 20 rows from the "Import" sheet.

I want it to paste just the data in plain text without any formatting or coloring that may be in the original data in the "Import" sheet. Then I want the bottom right cell ("I23") of the "Data" sheet to be selected. That's where we start to work on it... from the bottom up!

I originally posted this request on MrExcel Forum (http://www.mrexcel.com/forum/showthread.php?p=3100032#post3100032)

and got an answer that seemed to work perfectly but I later discovered it was causing an error in a different part of my worksheet which I can't seem to correct.

Here is the code provided by JoeMo:

Sub Select20()
Dim lRw As Long, Rws As Long

With Sheets("Data")
    .Range("C4", "I23").ClearContents
End With

With Worksheets("Import")
    lRw = WorksheetFunction.Max(.Range("C" & Rows.Count).End(xlUp).Row, .Range("I" & Rows.Count).End(xlUp).Row)
    Rws = .Range("C4:I" & lRw).Rows.Count
    If Rws >= 20 Then
        .Range("C" & lRw, "I" & lRw).Offset(-19, 0).Resize(20, _
            7).Cut Destination:=Sheets("Data").Range("C4")
    Else
        .Range("C4:I" & lRw).Cut Destination:=Sheets("Data").Range("C4")
    End If
End With
Sheets("Data").Activate
Range("I23").Select
End Sub

Open in new window


This will copy the 20 lines, paste them and delete the original but on the "Data" sheet I have formulas to change Province names to Abbreviations:

=(IF(ISNA(VLOOKUP(F4,$Y$4:$Z$25,2,FALSE)),"",VLOOKUP(F4,$Y$4:$Z$25,2,FALSE)))

Open in new window


This formula is beside each of the 20 lines to change the provinces to 2 digit abbreviations but when I run the above subroutine, it changes the "F4" in this formula to "#REF!" every time. Even though the cell with the formula is locked and the sheet is protected!!

Can anyone tell me how to correct this or write a different procedure to do the same thing without causing this error elsewhere?

I would very greatly appreciate your help!!!!

Thanks
calbaisAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Try this...not tested

Sub Select20()
Dim lRw As Long, Rws As Long

With Sheets("Data")
    .Range("C4", "I23").ClearContents
End With

With Worksheets("Import")
    lRw = WorksheetFunction.Max(.Range("C" & Rows.Count).End(xlUp).Row, .Range("I" & Rows.Count).End(xlUp).Row)
    Rws = .Range("C4:I" & lRw).Rows.Count
    If Rws >= 20 Then
        .Range("C" & lRw, "I" & lRw).Offset(-19, 0).Resize(20, _
            7).Copy Destination:=Sheets("Data").Range("C4")
        .Range("C" & lRw, "I" & lRw).Offset(-19, 0).Resize(20, _
            7).Delete Shift:=xlShiftUp
    Else
        .Range("C4:I" & lRw).Copy Destination:=Sheets("Data").Range("C4")
        .Range("C4:I" & lRw).Delete Shift:=xlShiftUp
    End If
End With
Sheets("Data").Activate
Range("I23").Select
End Sub
                                  

Open in new window

0
calbaisAuthor Commented:
It didn't change the "F4" but it stopped at this line when I tested with more than 20 rows:

.Range("C" & lRw, "I" & lRw).Offset(-19, 0).Resize(20, _
            7).Delete Shift:=xlShiftUp

Open in new window


and it stopped at this line when I tested with less than 20 rows:

 .Range("C4:I" & lRw).Delete Shift:=xlShiftUp

Open in new window


Both times I got a "Runtime error '1004'"  -- "Delete Method of range class failed"

But the "F4" stayed intact so we're making progress!!
0
Saqib Husain, SyedEngineerCommented:
Do you have a postable file?
0
CompTIA Network+

Prepare for the CompTIA Network+ exam by learning how to troubleshoot, configure, and manage both wired and wireless networks.

calbaisAuthor Commented:
Yes, I've attached the whole workbook. What I do for the problem I'm having is go to the "Import" sheet and data is pasted from an internet site we use at work. Could be up to 200 rows of data. ( I put some test data there for you to use)

Then on the "Data" sheet, there is a button "Get Data" that contains the procedure posted here. When I click that button it should go to the "Import" page, and select the bottom 20 rows and paste them in the 20 row blank section of the "Data" page. Then it deletes the 20 rows from the "Import" sheet and puts the cursor in the bottom right cell of the "Data" page so we can work from there. (We work from the bottom up checking the appropriate checkboxes)
We repeat this until all the imported data has been processed.

As an afterthought--- when I ran the debugger on this procedure, it kept going to another Function for setting Proper Case. It has nothing to do with this procedure and I don't know why it is running that function. It doesn't seem to hurt anything but if you know why it does it, or how to stop it, go for it!!

I really appreciate your help with this. This is the last problem to solve with this whole project and I can give it to my co-workers to use which will greatly speed up our daily work.
0
calbaisAuthor Commented:
I clicked "Attach" but I don't see any indication that the file was sent. Did you get it?
0
calbaisAuthor Commented:
Ok, I uploaded it sucessfully on the EE Stuff website just in case it didn't go here!
0
Saqib Husain, SyedEngineerCommented:
That sheet is protected. You need to unprotect it before you can run this macro.
0
calbaisAuthor Commented:
ok. I unprotected the "Import" page and that worked. It cleared all the rows and pasted them in the "Data" page as it should. I then accidentally hit the "Get Data" button again and it deleted the next 2 rows of my heading on the "import" page and pasted it to the "Data" page... lol

Is there a way to make it not try to import if there is no data on the "Import" page? In other words, to make row C4 the top row it will try to "Import" from.
0
Saqib Husain, SyedEngineerCommented:
I do not think I understand.
0
calbaisAuthor Commented:
If you have my workbook there, just keep pressing the "Get Data" button on the "Data" page and it should remove row C2 and C3 from the "Import" sheet and paste it in the top of the "Data" page. At least it does when I do that?
0
calbaisAuthor Commented:
I just tried it again. Unprotect the "Import" sheet. Go to the "Data" sheet and click the "Get Data" button 3 times... it should paste the top 2 lines of the "Import" page border (Blue) and it deletes it from the "Import" page... so I need some way to stop it from copying anything above row C4 on the "Import" page.
0
Saqib Husain, SyedEngineerCommented:
Enter this at the beginning of the sub

    If Sheets("Import").Range("C4") = "" Then Exit Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
calbaisAuthor Commented:
PERFECT!!!!

Thank you very, very much!!

Everything is working so Monday, I'll give it to my co-workers... Hopefully, it will survive that!! lol

You are very intelligent... I see why you are one of the top Experts!!
0
calbaisAuthor Commented:
Quick action, stuck with it until a solution was found. I'm am extremely satisfied!!

Thanks for your help!
0
Saqib Husain, SyedEngineerCommented:
Thanks for the grade and the compliments.

Since you appear to be a new user, I would just like to take the opportunity to tell you that these questions and the comments take a permanent place in EE's knowledge base and can be benefited by anyone else. People search for questions bearing their problems and if found try to get to the accepted answers. So for better performance it would be more appropriate to accept an answer which directly addresses the "given" question. In this case the actual question was answered by my first comment and that should have been the accepted answer. All the others are subsequent follow ups.

I hope you enjoy this site for a long time.

Saqib
0
calbaisAuthor Commented:
Hi Saqib

I've discovered a small problem that I know will be easy for you to fix. When data is copied and pasted from the "Import" sheet to the "Data" sheet and then it's deleted from the "Import" sheet, the rows are moving up from the bottom. The program starts out with 200 rows that can be used but for every row that is deleted that number is becoming smaller.
Can the data in the "Import" sheet be deleted without moving the rows up or if not then can the same number of rows be added to keep the total at 200.

Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.