If statement macro question

reeser

Board Regular
Joined
May 19, 2006
Messages
215
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?
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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

Board Regular
Joined
May 19, 2006
Messages
215
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

try this

Code:
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

Board Regular
Joined
May 19, 2006
Messages
215

ADVERTISEMENT

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
Code:
Range("A" & MY_ROWS - 9)
does? Thanks
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

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

reeser

Board Regular
Joined
May 19, 2006
Messages
215

ADVERTISEMENT

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

Board Regular
Joined
May 19, 2006
Messages
215
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

Is this any nearer to what you require?

Code:
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?
 

Forum statistics

Threads
1,136,350
Messages
5,675,246
Members
419,557
Latest member
razlevav

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
Top