Macros

[manfromearth]

We used the following code to copy particular cells from a worksheet, but we are not able to copy those blank spaces to preserve the integrity of the table...can u pls help?

Function LastColumn() As Long<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Dim ix As Long<o></o>
ix = ActiveSheet.UsedRange.Column - 1 + ActiveSheet.UsedRange.Columns.Count<o></o>
LastColumn = ix<o></o>
<o></o>
End Function<o></o>
Function ColNo2ColRef(colNo As Integer) As String<o></o>
If colNo < 1 Or colNo > 256 Then<o></o>
ColNo2ColRef = "#VALUE!"<o></o>
Exit Function<o></o>
End If<o></o>
ColNo2ColRef = Cells(1, colNo).Address(True, False, xlA1)<o></o>
ColNo2ColRef = Left(ColNo2ColRef, InStr(1, ColNo2ColRef, "$") - 1)<o></o>
End Function<o></o>
<o> </o>
Sub MainMacro()<o></o>
<o> </o>
Dim colNo As Integer<o></o>
Dim i As Integer<o></o>
Dim reftext As String<o></o>
Dim reftext1 As String<o></o>
Dim rCount As Long<o></o>
<o> </o>
rCount = Range("C" & Rows.Count).SpecialCells(xlCellTypeLastCell).Row<o></o>
<o> </o>
For i = 7 To rCount Step 29<o></o>
Sheets("Pinnacle Metrics by Projects").Range("C" & i).Copy Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)<o></o>
Sheets("Pinnacle Metrics by Projects").Range("D" & i).Copy Destination:=Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1)<o></o>
Next i<o></o>
<o> </o>
For i = 11 To rCount Step 29<o></o>
Sheets("Pinnacle Metrics by Projects").Range("H" & i).Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)<o></o>
Sheets("Pinnacle Metrics by Projects").Range("H" & i + 1).Copy Destination:=Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Offset(1)<o></o>
Next i<o></o>
<o> </o>
'Calculating the last cloumn<o></o>
colNo = LastColumn() - 2<o></o>
reftext = ColNo2ColRef(colNo)<o></o>
reftext1 = ColNo2ColRef(colNo - 2)<o></o>
<o> </o>
'Customer Profitability latest month<o></o>
For i = 12 To rCount Step 29<o></o>
Sheets("Pinnacle Metrics by Projects").Range(reftext1 & i).Copy Destination:=Sheets("Sheet1").Range("C" & Rows.Count).End(xlUp).Offset(1)<o></o>
Next i<o></o>
<o> </o>
'Customer Profitability YTD<o></o>
For i = 12 To rCount Step 29<o></o>
Sheets("Pinnacle Metrics by Projects").Range(reftext & i).Copy Destination:=Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Offset(1)<o></o>
Next i<o></o>
'Utilisation Onsite<o></o>
For i = 13 To rCount Step 29<o></o>
Sheets("Pinnacle Metrics by Projects").Range(reftext & i).Copy Destination:=Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Offset(1)<o></o>
Next i<o></o>
'Utilisation Offshore<o></o>
For i = 14 To rCount Step 29<o></o>
Sheets("Pinnacle Metrics by Projects").Range(reftext & i).Copy Destination:=Sheets("Sheet1").Range("F" & Rows.Count).End(xlUp).Offset(1)<o></o>
Next i<o></o>
'Span Onsite<o></o>
For i = 32 To rCount Step 29<o></o>
Sheets("Pinnacle Metrics by Projects").Range(reftext & i).Copy Destination:=Sheets("Sheet1").Range("G" & Rows.Count).End(xlUp).Offset(1)<o></o>
Next i<o></o>
'Span Offshore<o></o>
For i = 31 To rCount Step 29<o></o>
Sheets("Pinnacle Metrics by Projects").Range(reftext & i).Copy Destination:=Sheets("Sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1)<o></o>
Next i<o></o>
<o> </o>
End Sub<o></o>

 

[CharlesChuckieCharles]

Sheets("Pinnacle Metrics by Projects").Range("C" & i).Copy Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>

Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)<o></o>

End(xlUp).Offset(1) would this bit ( which appears to find the bottom used cell in the destination sheet) be by passing empty cell you've just pasted ??