Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here's a quick and easy one..
Sorry, I know this question isn't even entertaining for you guys...I've tried my best to figure it out but I still need just a little help! I have a table in a spreadsheet (see attached 'exceltable.jpg') that needs to be copied into other worksheets but only the table rows containing values (Range B26:H28 in the image). Of course the range is changing for different orders and such... So how do I get excel to insert the correct amount of rows into the different worksheets in between rows 13 and 14 (see attaced 'exceltable002.jpg') and paste the range with values into the newly created rows???? So it ends up looking like the attached exceltable003.jpg +-------------------------------------------------------------------+ |Filename: exceltable003.jpg | |Download: http://www.excelforum.com/attachment.php?postid=5224 | +-------------------------------------------------------------------+ -- grahamhurlburt ------------------------------------------------------------------------ grahamhurlburt's Profile: http://www.excelforum.com/member.php...o&userid=29878 View this thread: http://www.excelforum.com/showthread...hreadid=572851 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Here's a quick and easy one..
You could probably use SpecialCells to set the range to copy, but I didn't
want to go that route not knowing what your data looked like (formulas, constants, numbers, text, combination of the above), so I took the long way. This should copy the rows from Sheet1!B26:H28 w/data in them to Sheet2 and Sheet3, below cell A13. Change sheet and range references as needed. Sub test() Dim rngData1 As Range Dim rngData2 As Range Dim strDest As String Dim rngRow As Range Dim lngRows As Long Dim varSheets As Variant Dim i As Long Set rngData1 = Sheets("Sheet1").Range("B26"H28") strDest = "A13" varSheets = Array("Sheet2", "Sheet3") For Each rngRow In rngData1.Rows If Application.CountBlank(rngRow) < _ rngRow.Cells.Count Then lngRows = lngRows + 1 If rngData2 Is Nothing Then Set rngData2 = rngRow Else: Set rngData2 = Union(rngData2, _ rngRow) End If End If Next rngRow If Not rngData2 Is Nothing Then For i = LBound(varSheets) To UBound(varSheets) With Sheets(varSheets(i)) With .Range(.Range(strDest)(2, 1), _ .Range(strDest)(1 + lngRows, 1)) .EntireRow.Insert End With rngData2.Copy .Range(strDest)(2, 1) End With Next i End If End Sub "grahamhurlburt" wrote: Sorry, I know this question isn't even entertaining for you guys...I've tried my best to figure it out but I still need just a little help! I have a table in a spreadsheet (see attached 'exceltable.jpg') that needs to be copied into other worksheets but only the table rows containing values (Range B26:H28 in the image). Of course the range is changing for different orders and such... So how do I get excel to insert the correct amount of rows into the different worksheets in between rows 13 and 14 (see attaced 'exceltable002.jpg') and paste the range with values into the newly created rows???? So it ends up looking like the attached exceltable003.jpg +-------------------------------------------------------------------+ |Filename: exceltable003.jpg | |Download: http://www.excelforum.com/attachment.php?postid=5224 | +-------------------------------------------------------------------+ -- grahamhurlburt ------------------------------------------------------------------------ grahamhurlburt's Profile: http://www.excelforum.com/member.php...o&userid=29878 View this thread: http://www.excelforum.com/showthread...hreadid=572851 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Quick/Easy Question? | Excel Discussion (Misc queries) | |||
Quick and easy labeling | Excel Discussion (Misc queries) | |||
Quick and Easy from Checkboxes | Excel Programming | |||
Quick and easy | Excel Worksheet Functions | |||
Quick and Easy! | Excel Programming |