Here's a quick and easy one..

[grahamhurlburt]

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 |
+-------------------------------------------------------------------+

 

[JMB]

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