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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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
517
Joe4
Joe4
N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
298
mumps
mumps
P
  • Question Question
Pick lines and copy depends to another cell sheet
Replies
2
Views
83
rowebca
rowebca
gymwrecker
  • Solved
Formula to copy data from one sheet to another sheet based on specific criteria
Replies
4
Views
105
Fluff
Fluff
S
  • Question Question
Copy certain column N number of times
Replies
0
Views
235
sm789
S

Forum statistics

Threads
1,223,785
Messages
6,174,539
Members
452,571
Latest member
MarExcelTips

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