Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range from Sheet1 into empty range in Sheet2
I am always copying range C2:P5 from Sheet1, however the range I am pasting
it into in Sheet 2 will be any one of these ranges. Ranges C5:P8 C9:P12 C13:P16 C17:P20 C21:P24 C25:P28 C33:P36 C37:P40 C41:P44 C45:P48 C49:C52 C53:C56 C57:C60 C61:C64 C65:C67 Can you help me add to the macro below so that it searches these ranges from top to bottom and pastes the contents into the first empty range without contents? Sub FindEmptyRange() Sheets("Sheet2").Range("C5:P8").Value = _ Sheets("Sheet1").Range("C2:P5).Value Sheets("Sheet2").Activate End Sub For example ideally the macro will check range C5:P8 on Sheet2 first. If that range already has contents inside it then C9:P12 will be checked. If that range is empty the contents will be pasted into it, if not, the macro will go to the next range C13:C16, and so on. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy range from Sheet1 into empty range in Sheet2
Dim arrRng As Variant
Dim vItem As Variant arrRng = Array("C5:P8", "C9:P12", "C13:P16") ' Add the other addresses For Each vItem In arrRng If Application.CountA(Worksheets("Sheet2").Range(vIte m)) = 0 Then Worksheets("Sheet2").Range(vItem).Value = _ Worksheets("Sheet1").Range("C2:P5").Value Exit For End If Next -- Jim Cone Portland, Oregon USA "Buddy" wrote in message ... I am always copying range C2:P5 from Sheet1, however the range I am pasting it into in Sheet 2 will be any one of these ranges. Ranges C5:P8 C9:P12 C13:P16 C17:P20 C21:P24 C25:P28 C33:P36 C37:P40 C41:P44 C45:P48 C49:C52 C53:C56 C57:C60 C61:C64 C65:C67 Can you help me add to the macro below so that it searches these ranges from top to bottom and pastes the contents into the first empty range without contents? Sub FindEmptyRange() Sheets("Sheet2").Range("C5:P8").Value = _ Sheets("Sheet1").Range("C2:P5).Value Sheets("Sheet2").Activate End Sub For example ideally the macro will check range C5:P8 on Sheet2 first. If that range already has contents inside it then C9:P12 will be checked. If that range is empty the contents will be pasted into it, if not, the macro will go to the next range C13:C16, and so on. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Simple? View named range from sheet1 on sheet2 | Excel Worksheet Functions | |||
Populate growing range of cells from Sheet1 to Sheet2 | New Users to Excel | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
copy cell B19:J19 Range datas from sheet1, and to past in sheet2 | Excel Programming | |||
A1 Sheet2 is linked to A1 sheet1 so that user enters value(abc123) a1 sheet1 and A1 sheet2 is updated | Excel Programming |