VBA copy cells to another sheet

[linkdemz]

Hi,

I am wanting a code that will copy any cells within the Range of A10 to A30 that have any data in them, to another worksheet, but ignore any cells that are blank and not copy the blank cells over.

I have managed to get cells to copy when i push the macro button but what I want to happen which is currently not for example is... If I only fill the A10 to A15 I only want it to check the cells A10 to A30 recognize it stops at A15 and only copy A10 to A15.

I hope that makes sense.

Thanks for any help that can be given.

 

[gallen]

Hello and welcome.

Would help if you posted what code you have. You also don't state where you want the copied cell pasted to.

This code copies and pastes to next blank row in the 2nd sheet

Sub CopyCells()
    Dim c As Range
    Dim nr As Long 'next row
    Dim wsPaste As Worksheet, wsCopy As Worksheet
    
    'change sheet names as required
    Set wsCopy = Sheets("Sheet1")
    Set wsPaste = Sheets("Sheet2")
    
    nr = wsPaste.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    For Each c In wsCopy.Range("A10:A30")
        If c <> "" Then 'only copy if not blank
            c.Copy wsPaste.Range("A" & nr)
            Application.CutCopyMode = False
            nr = nr + 1
        End If
    Next c


End Sub

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub linkdemz()
   Range("A10:A30").SpecialCells(xlConstants).Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

This assumes the cells are hard values, rather than formulae

 

[gallen]

@Fluff

Well that's infinitely better

 

[Fluff]

Thanks

 

[linkdemz]

Hello and welcome.

Would help if you posted what code you have. You also don't state where you want the copied cell pasted to.

This code copies and pastes to next blank row in the 2nd sheet

Sub CopyCells()
    Dim c As Range
    Dim nr As Long 'next row
    Dim wsPaste As Worksheet, wsCopy As Worksheet
    
    'change sheet names as required
    Set wsCopy = Sheets("Sheet1")
    Set wsPaste = Sheets("Sheet2")
    
    nr = wsPaste.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    For Each c In wsCopy.Range("A10:A30")
        If c <> "" Then 'only copy if not blank
            c.Copy wsPaste.Range("A" & nr)
            Application.CutCopyMode = False
            nr = nr + 1
        End If
    Next c


End Sub

Hi,

Thankyou so much that worked amazing!.

Just to push the envelope a little further however, how would i modify that to copy from two different columns of sheet1 and paste to two different colums of sheet2.

For example if i wanted to only copy cells that contain data in row A + X of sheet1 and past them into rows C + E of sheet 2?

Thank you so much for your help!!!

 

[Fluff]

How about

Sub linkdemz()
   Range("A10:A30").SpecialCells(xlConstants).Copy Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1)
   Range("X10:X30").SpecialCells(xlConstants).Copy Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1)
End Sub

 

[linkdemz]

How about

Sub linkdemz()
   Range("A10:A30").SpecialCells(xlConstants).Copy Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1)
   Range("X10:X30").SpecialCells(xlConstants).Copy Sheets("Sheet2").Range("E" & Rows.Count).End(xlUp).Offset(1)
End Sub

Hi Fluff,

Thanks for the reply. I have tried the code you posted but i am getting an error message saying "Copy method of range class failed" for the second line.

Could you help resolve it please?

 

[Fluff]

Are the values in X10:X30 hard values or formulae?
Is there any sheet protection?
Do you have any merged cells?