Copy data from one sheet to another while skipping blanks and certain rows

T

tlc1980

New Member
Joined
Feb 13, 2013
Messages
19
Hi. I'm trying to copy data from one sheet to another, but I want it to skip blanks and a row that has data on it.

I want C9:C12, C14:C17, C19:C22, C24:C27, and C29:C34 from Sheet1 to be moved to A8:A33 on Sheet 2.

Rows 13, 18, 23, and 28 on Sheet1 need to be left out.

Thank you in advance for your help!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
Maybe something like this...

Code: 
[color=darkblue]Sub[/color] Copy_Stuff()
    [color=darkblue]Dim[/color] rng [color=darkblue]As[/color] Range, lRow [color=darkblue]As[/color] [color=darkblue]Long[/color]
    Application.ScreenUpdating = [color=darkblue]False[/color]
    [color=darkblue]For[/color] [color=darkblue]Each[/color] rng [color=darkblue]In[/color] Sheets("Sheet1").Range("C9:C12, C14:C17, C19:C22, C24:C27, C29:C34").Areas
        rng.Copy
        Sheets("Sheet2").Range("A8").Offset(lRow).PasteSpecial xlPasteValues
        [color=green]'rng.ClearContents      'Clear source data[/color]
        lRow = lRow + 5
    [color=darkblue]Next[/color]
    Application.CutCopyMode = [color=darkblue]True[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Hi! Thanks for replying. I tried out the code, but it's not showing up on Sheet2 how I want it to. Below is what my sheet looks like and how I want it to look like.


<tbody>
[TD="class: xl72, colspan: 2, align: center"] Sheet1 [/TD]
[TD="class: xl73, width: 64, align: center"][/TD]
[TD="class: xl72, width: 130, colspan: 2, align: center"] Sheet2 [/TD]
[TD="class: xl73, width: 64, align: center"][/TD]
[TD="class: xl72, width: 146, colspan: 2, align: center"] How I want it on Sheet2 [/TD]

[TD="class: xl72, align: center"]C9[/TD]
[TD="class: xl69, width: 194, align: center"]Roast Pork[/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A8[/TD]
[TD="class: xl71, width: 100, align: center"]Roast Pork[/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A8[/TD]
[TD="class: xl70, width: 82, align: center"]Roast Pork[/TD]

[TD="class: xl72, align: center"]C10[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A9[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A9[/TD]
[TD="class: xl70, width: 82, align: center"]Hamburger[/TD]

[TD="class: xl72, align: center"]C11[/TD]
[TD="class: xl69, width: 194, align: center"]Hamburger[/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A10[/TD]
[TD="class: xl71, width: 100, align: center"]Hamburger[/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A10[/TD]
[TD="class: xl70, width: 82, align: center"]Steamed Brown Rice[/TD]

[TD="class: xl72, align: center"]C12[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A11[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A11[/TD]
[TD="class: xl70, width: 82, align: center"]Orange Glazed Sweet Potatoes[/TD]

[TD="class: xl72, align: center"]C13[/TD]
[TD="class: xl69, width: 194, align: center"]M/MA: [ oz] G/WG: [0/][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A12[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A12[/TD]
[TD="class: xl70, width: 82, align: center"]Grapes[/TD]

[TD="class: xl72, align: center"]C14[/TD]
[TD="class: xl69, width: 194, align: center"]Steamed Brown Rice[/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A13[/TD]
[TD="class: xl71, width: 100, align: center"]Steamed Brown Rice[/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A13[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C15[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A14[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A14[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C16[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A15[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A15[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C17[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A16[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A16[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C18[/TD]
[TD="class: xl69, width: 194, align: center"]M/MA: [ oz] G/WG: [0/][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A17[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A17[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C19[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A18[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A18[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C20[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A19[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A19[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C21[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A20[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A20[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C22[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A21[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A21[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C23[/TD]
[TD="class: xl69, width: 194, align: center"]M/MA: [ oz] G/WG: [0/][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A22[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A22[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C24[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A23[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A23[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C25[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A24[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A24[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C26[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A25[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A25[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C27[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A26[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A26[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C28[/TD]
[TD="class: xl69, width: 194, align: center"]M/MA: [ oz] G/WG: [0/][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A27[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A27[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C29[/TD]
[TD="class: xl69, width: 194, align: center"]Orange Glazed Sweet Potatoes[/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A28[/TD]
[TD="class: xl71, width: 100, align: center"]Orange Glazed Sweet Potatoes[/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A28[/TD]
[TD="class: xl72, align: center"][/TD]

[TD="class: xl72, align: center"]C30[/TD]
[TD="class: xl69, width: 194, align: center"]Grapes[/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A29[/TD]
[TD="class: xl71, width: 100, align: center"]Grapes[/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A29[/TD]
[TD="class: xl72, align: center"][/TD]

[TD="class: xl72, align: center"]C31[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A30[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A30[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C32[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A31[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A31[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C33[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A32[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A32[/TD]
[TD="class: xl70, width: 82, align: center"][/TD]

[TD="class: xl72, align: center"]C34[/TD]
[TD="class: xl69, width: 194, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A33[/TD]
[TD="class: xl71, width: 100, align: center"][/TD]
[TD="class: xl73, align: center"][/TD]
[TD="class: xl72, align: center"]A33[/TD]

</tbody>
 
Upvote 0
Code: 
[color=darkblue]Sub[/color] Copy_Stuff2()
    Application.ScreenUpdating = [color=darkblue]False[/color]
    Sheets("Sheet2").Range("A8:A33").ClearContents
    [color=darkblue]With[/color] Sheets("Sheet1").Range("C9:C12, C14:C17, C19:C22, C24:C27, C29:C34").SpecialCells(xlCellTypeConstants)
        .Copy
        Sheets("Sheet2").Range("A8").PasteSpecial xlPasteValues
        [color=green]'ClearContents      'Clear source data[/color]
    [color=darkblue]End[/color] [color=darkblue]With[/color]
    Application.CutCopyMode = [color=darkblue]True[/color]
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Last edited:
Upvote 0
Sorry, but after playing around with it some more, I can't get it to work with my data because the cells in column c are referenced from another worksheet. I have a vlookup formula in those cells. Can you please change the code a bit to get it to work with my data? Thanks!
 
Upvote 0
Code: 
[color=darkblue]Sub[/color] Copy_Stuff2()
    [color=darkblue]Dim[/color] cell [color=darkblue]As[/color] Range, r [color=darkblue]As[/color] [color=darkblue]Long[/color]
    r = 8
    Application.ScreenUpdating = [color=darkblue]False[/color]
    Sheets("Sheet2").Range("A8:A33").ClearContents
    [color=darkblue]For[/color] [color=darkblue]Each[/color] cell [color=darkblue]In[/color] Sheets("Sheet1").Range("C9:C12, C14:C17, C19:C22, C24:C27, C29:C34")
        [color=darkblue]If[/color] Len(cell) > 0 [color=darkblue]Then[/color]
            Sheets("Sheet2").Range("A" & r).Value = cell.Value
            r = r + 1
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] cell
    Application.ScreenUpdating = [color=darkblue]True[/color]
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
 
Upvote 0
Sub Copy_Stuff1()
Dim cell As Range, r As Long
r = 2
Application.ScreenUpdating = False
Sheets("Sheet4").Range("A2:A3000").ClearContents
For Each cell In Sheets("dps").Range("AHR282:AHR388, AHT282:AHT388, AHV282:AHV388, AHX282:AHX388, AHZ282:AHZ388, AIB282:AIB388, AID282:AID388, AIF282:AIF388, AIH282:AIH388, AIJ282:AIJ388, AIL282:AIL388, AIN282:AIN388, AIP282:AIP388, AIR282:AIR388, AIT282:AIT388, AIV282:AIV388, AIX282:AIX388 ")
If Len(cell) > 0 Then
Sheets("Sheet4").Range("A" & r).Value = cell.Value
r = r + 1
End If
Next cell
Application.ScreenUpdating = True
End Sub

Sub Copy_Stuff2()
Dim cell As Range, s As Long
s = 2
Application.ScreenUpdating = False

For Each cell In Sheets("dps").Range("AIZ282:AIZ388, AJB282:AJB388, AJD282:AJD388, AJF282:AJF388, AJH282:AJH388, AJJ282:AJJ388, AJL282:AJL388, AJN282:AJN388, AJP282:AJP388, AJR282:AJR388, AJT282:AJT388, AJV282:AJV388, AJX282:AJX388, AJZ282:AJZ388, AKB282:AKB388, AKD282:AKD388, AKF282:AKF388 ")
If Len(cell) > 0 Then
Sheets("Sheet4").Range("A" & s).Value = cell.Value
s = s + 1
End If
Next cell
Application.ScreenUpdating = True
End Sub

Sub Copy_Stuff3()
Dim cell As Range, t As Long
t = 2
Application.ScreenUpdating = False

For Each cell In Sheets("dps").Range("AKH282:AKH388, AKJ282:AKJ388, AKL282:AKL388, AKN282:AKN388 ")
If Len(cell) > 0 Then
Sheets("Sheet4").Range("A" & t).Value = cell.Value
t = t + 1
End If
Next cell
Application.ScreenUpdating = True
End Sub
Click to expand...

1st thank you for the above and cool stuff 1 works but other two not working can any help me with this :confused:
 
Upvote 0
aamir said:
... but other two not working
Click to expand...

"Not working" is hardly a description of the problem.
Help Us Help You

If any of the cells you want to copy have a formula that returns an error, the code (as it is now) would error on that cell. But of course, I have no idea if this is at all related to your "not working" situation.
 
Upvote 0
You must log in or register to reply here.

Similar threads

Y
  • Question Question
Copy data to another sheet last row
Replies
1
Views
620
Joe4
Joe4
N
  • Question Question
Copy a template if Master is Yes, rename with cell data
Replies
3
Views
282
Alex Blakenburg
A
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
630
mumps
mumps
B
  • Question Question
vba to copy selected cells and some cells adjacent to next blank row in different workbook
Replies
4
Views
202
Peter_SSs
Peter_SSs
S
  • Question Question
Need a way to copy and paste previous rows after a dropdown selection is made on the same sheet
2
Replies
12
Views
255
Candyman8019
C

Forum statistics

Threads
1,226,730
Messages
6,192,708
Members
453,748
Latest member
akhtarf3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top