Skip blank rows while copying and retain format

[rrocker1405]

Hi team,

Attached is the sheet I'm working on presently. The basic idea is to update the risk sheet with dependencies data. Risk sheet will have a command button when clicked it would get the data in sheet3 (mirror data of dependency sheet if they are not already registered as risk).

I've put together a macro which would copy data from sheet3 and paste it in risk sheet. however, the macro does not delete/skip blank row while pasting data and also changes the format in risk sheet in lines with sheet3 which I don't want.I've also tried to locate the last updated row and paste data even then it does not work.

Any suggestions? Thanks in advance.

Regards.

 

[Luke M]

I think this will do what you want.

Sub CopyPaste()
Dim r As Range
 
'Turn off the screen updates so we don't flash the user with a bunch of movement
Application.ScreenUpdating = False
With Worksheets("Sheet3")
    'First, need some way to limit what cells we will look at
    'We'll use the time stamp column and tell the code to grab only the cells
    'that are showing a number
    Set r = .Range("O7", .Cells(.rows.Count, "O").End(xlUp)).SpecialCells(xlCellTypeFormulas, xlNumbers)
    
    'Next, loop through each of those cells...
    For Each c In r
        'Grab the entire row that we need (going from col C to S)
        c.Offset(, -12).Resize(, 17).Copy
        'Paste it into the Risk worksheet, values only of course
        Worksheets("Risk").Range("C2").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
        'Clear the Clipboard
        Application.CutCopyMode = False
    Next c
End With
'Make sure we turn this back on
Application.ScreenUpdating = True
 
End Sub

 

[rrocker1405]

wow...that worked like magic. However, I didn't get the code specially you had set the range for 'o7'. could you please let me know.

Many thanks, Regards.

 

[Luke M]

I was looking at your worksheet, and noticed that most of the cells have formula that either spit out text, or "". Since the latter is classified as Text in VB, that didn't help me. However, the time stamp column (col O) will either be the "" or a date (which is a formatted number). That let me tell VB how to separate the blank rows from the rows with data.

 

[rrocker1405]

whao...thats why i was always getting a row/column with text and when deleted the content it went off. Thanks a ton for making it simpler and explaining it for me Luke.

Many thanks, Regards.

 

[Luke M]

You're very welcome; always glad to help.