Solved

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

Posted on 2012-03-30
16
235 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 6
16 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37786827
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
ID: 37787626
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
ID: 37787656
Do you have a postable file?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:calbais
ID: 37788011
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
ID: 37788018
I clicked "Attach" but I don't see any indication that the file was sent. Did you get it?
0
 

Author Comment

by:calbais
ID: 37788084
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
ID: 37788522
That sheet is protected. You need to unprotect it before you can run this macro.
0
 

Author Comment

by:calbais
ID: 37788729
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 37788745
I do not think I understand.
0
 

Author Comment

by:calbais
ID: 37788817
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
ID: 37788835
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
ID: 37788921
Enter this at the beginning of the sub

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

Author Comment

by:calbais
ID: 37788953
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
ID: 37788964
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
ID: 37789011
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
ID: 37789663
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

749 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