Solved

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

Posted on 2012-03-30
16
230 Views
Last Modified: 2012-03-30
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
0
Comment
Question by:calbais
  • 10
  • 6
16 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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
 

Author Comment

by:calbais
Comment Utility
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
Do you have a postable file?
0
 

Author Comment

by:calbais
Comment Utility
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
 

Author Comment

by:calbais
Comment Utility
I clicked "Attach" but I don't see any indication that the file was sent. Did you get it?
0
 

Author Comment

by:calbais
Comment Utility
Ok, I uploaded it sucessfully on the EE Stuff website just in case it didn't go here!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
That sheet is protected. You need to unprotect it before you can run this macro.
0
 

Author Comment

by:calbais
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
I do not think I understand.
0
 

Author Comment

by:calbais
Comment Utility
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
 

Author Comment

by:calbais
Comment Utility
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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
Comment Utility
Enter this at the beginning of the sub

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

Author Comment

by:calbais
Comment Utility
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
 

Author Closing Comment

by:calbais
Comment Utility
Quick action, stuck with it until a solution was found. I'm am extremely satisfied!!

Thanks for your help!
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
Comment Utility
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
 

Author Comment

by:calbais
Comment Utility
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

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now