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. |
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. |
All times are GMT +1. The time now is 12:03 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com