If statement macro question

[reeser]

I'm working on an easy If macro. I have 13 cells (H10-H22). I just need a macro that says if there is data in H10 copy and paste to another sheet. If data is in H11 copy and past....etc. Sometimes there may only be something in H10, others H10-H22 will all be filled out. The other sheet would just be Sheet2 cell A1-A13. Any suggestions?

 

[onlyadrafter]

Hello,

Just to clarify, you want to copy H10:H22 to the other sheet, only if they have data in them?

or, you only want to copy the first cell with data in?

 

[reeser]

Sorry I only want to copy the cells H10-H22 that have data in them. So if H10, H11, and H12 have data in them, I want all three copied. It will always go in order like that. There will never be a case where H10 and H12 have data but not H11. Sorry if that confuses you more, but wanted to give you all the info.

 

[onlyadrafter]

Hello,

try this

Sub copy_to_sheet()
For MY_ROWS = 10 To 22
    If Not (IsEmpty(Range("H" & MY_ROWS))) Then
        Range("H" & MY_ROWS).Copy
        Sheets("Sheet2").Range("A" & MY_ROWS - 9).PasteSpecial (xlValues)
    End If
Next MY_ROWS
End Sub

change refs as required.

 

[reeser]

I'm not getting anything to paste. I substituted Report (the name of my sheet) in for MY_ROWS. Is this correct? Then I also had a question on what the line

Range("A" & MY_ROWS - 9)

does? Thanks

 

[onlyadrafter]

Hello,

What is the sheet (tab) name of the source sheet and what is the sheet (tab) name of the destination sheet ?

 

[reeser]

The source sheet is Report and the destination sheet is Storage


Hope that helps. Also I just realized that I need more information copied. If there is something in H10, I need cells H10:L10 copied and pasted into Storage A1:A5. Sorry I thought I'd only be dealing w/ 1 cell. Again the example applies to the others as well. So if H11 has data, then H11:L11 needs copied.

The report is for data entry purposes and the info. gets copied to storage. The storage keeps a running tally of the data entered. So today I may enter 3 lines of data, tomorrow 2, etc. Once the data is copied to storage it serves as a historical reference to possibly be placed in a database. Hope this helps as well. What started out as a simple project has added a few more twists. Thanks

 

[reeser]

Anyone else w/ more suggestions? I've played several times w/ new If statements for the macro but continue to get the macro to copy all the data even when there is no information in the cells.

 

[onlyadrafter]

Hello,

Is this any nearer to what you require?

Sub copy_to_sheet()
For MY_ROWS = 10 To 22
    If Not (IsEmpty(Sheets("Report").Range("H" & MY_ROWS))) Then
        Sheets("Report").Range("H" & MY_ROWS & ":L" & MY_ROWS).Copy
        Sheets("Storage").Range("A" & MY_ROWS - 9).PasteSpecial (xlValues)
    End If
Next MY_ROWS
End Sub

IS the data in H10:H22 manually entered, or are they formulas?