Copying Row Heights, or Complete Rows

S

Sam Fowler

Hi All:

I have a master worksheet that holds several types of data. I also have 10
worksheets (in the same workbook) that may, or may not contain data that I
need to copy to the master sheet. I am a bit of a novice with VBA, but have
gotten the following code to work. However, I would like to either copy row
heights, or possibly the entire row. Each secondary worksheet contains 10
pages, each 67 rows long. Columns are the same width as the master sheet
(Some Cells are merged)

Can anyone help me get this to perform the checks and then copy the entire
row?
The current code is as follows:

Sub Data_Ranges_Copy_and_Clear()

Dim vCopySheets As Variant
Dim vCheckPoints As Variant
Dim vCopyRange As Variant
Dim rng As Range
Dim Rng2 As Range
Dim Rng3 As Range

Dim iCounter As Integer
Dim iCounter2 As Integer


vCopySheets = Array("Cores", "NPN", "Est", "GOG", "Fact Claim", "OS
Claim", "Fact PS", "OS PS", "Prepaid", "Sold During")
'Select each sheet in turn
For iCounter = LBound(vCopySheets) To UBound(vCopySheets) Step 1
Sheets(vCopySheets(iCounter)).Select
'Cells on this sheet to test
vCheckPoints = Array("A619", "A552", "A485", "A418", "A351", "A284",
"A217", "A147", "A83", "A16")
'Corresponding ranges to copy
vCopyRange = Array("A1:p670", "A1:p603", "A1:p536", "A1:p469",
"A1:p402", "A1:p335", "A1:p268", "A1:p201", "A1:p134", "A1:p67")
For iCounter2 = LBound(vCheckPoints) To UBound(vCheckPoints) Step 1
Set rng = Range(vCheckPoints(iCounter2))
If Not (IsEmpty(rng)) Then
'set copy area
Set Rng2 = Range(vCopyRange(iCounter2))
'Before copying find pasting point
Set Rng3 = Sheets("INV").Cells(65536, 1).End(xlUp).Offset(1, 0)
'Now copy to other sheet
With Rng2
.Copy Rng3
' .ClearContents

End With

'Items found and copied so get out of (inner)loop
Exit For
End If
Next
'Move on to next sheet
Next

' Now Clear Data Ranges
Dim ws As Worksheet, i As Long
For Each ws In Worksheets(Array("Cores", "NPN", "Est", "GOG", "Fact
Claim", "OS Claim", "Fact PS", "OS PS", "Prepaid", "Sold During"))

For i = 0 To 9
ws.Range("A16:L63").Offset(i * 67).ClearContents
Next i
Next
Sheets("INV").Select

End Sub

Oh, I forgot to mention, when the copy and paste is complete, I need the
original data cleared.

Thanks,
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top