?
Solved

Copy Data to external sheet with criteria

Posted on 2012-08-20
5
Medium Priority
?
325 Views
Last Modified: 2012-09-08
Excel vba 2010


I have code that copies data , but bascially all the data.

I need to only select data where "Column H" cells are blank, null or empty.
the amount of rows will always vary from project to project.


current code here:
Set curWks = ActiveSheet
With curWks

Set rngToCopy = .Range("A6:AU65453", .Range("A6").End(xlToRight).End(xlDown))



End With


Thanks
fordraiders
0
Comment
Question by:Fordraiders
  • 2
  • 2
5 Comments
 
LVL 15

Accepted Solution

by:
Simon Ball earned 2000 total points
ID: 38315275
Sub test()
Set curwks = ActiveSheet
With curwks

Set rngtocopy = ActiveSheet.UsedRange
End With
'MsgBox rngtocopy.AddressLocal
rngtocopy.Select
Selection.Copy
Sheets("sheet2").Activate
ActiveSheet.Paste
End Sub

Open in new window


I used auto filter to filter column H for blanks, then ran the copy and paste code above..only the filtered records were pasted..

I don;t like using exceltoright etc, it gave me out of memory exception on a small data set
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 38315285
please see sample attached
excelfilterpaste.xlsm
0
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38315849
Here's a special cells approach.

    With curwks
        Set rngToCopy = .Range("H1:H" & .Cells(Rows.Count, 8).End(xlUp).Row).SpecialCells(xlCellTypeBlanks)
    End With

Open in new window

0
 
LVL 3

Author Comment

by:Fordraiders
ID: 38320343
techfanatic, Will this copy all rows ?
starting in column a and going to A6:AU65453A6:AU65453

but only taking rows that are blank cells in column H ?
0
 
LVL 3

Author Closing Comment

by:Fordraiders
ID: 38379511
Thanks
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

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.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

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