izet99
09-26-2014, 11:55 AM
Hi, have a little marco code below that work great but I would like to modify destination...
For example, how do I set destination to specific cell, let say cell start from C25, insert new row and paste data in it. Basicly, I have data in C24 and C30, I need code below to insert copied data between C24:C30 range. Data that will be inserted varies in size/number of row so I would need push down existing used row in C30. Anybody have any idea to to modify existing code?
Destination code, where I have issue with:
.Range("B" & i).EntireRow.Copy _
Destination:=ws.Range("C65536").End(xlUp).Offset(1, 0)
Full code below... it distribution data from one sheet to multiple sheet if value in column match.
Sub DistributeData()
Dim i As Long
Dim LastRow As Long
Dim ws As Worksheet
Dim ErrorLog As String
With Sheets("all")
LastRow = .Range("C65536").End(xlUp).Row
For i = 40 To LastRow
On Error Resume Next
Set ws = Sheets(.Range("B" & i).Text)
On Error GoTo 0
If ws Is Nothing Then
ErrorLog = ErrorLog & vbNewLine & _
"Row:" & i & " Sheet Name: " & .Range("C" & i).Text
Else
.Range("B" & i).EntireRow.Copy _
Destination:=ws.Range("C65536").End(xlUp).Offset(1, 0)
End If
Set ws = Nothing
Next i
End With
Set ws = Nothing
End Sub
Regards,
Izet
For example, how do I set destination to specific cell, let say cell start from C25, insert new row and paste data in it. Basicly, I have data in C24 and C30, I need code below to insert copied data between C24:C30 range. Data that will be inserted varies in size/number of row so I would need push down existing used row in C30. Anybody have any idea to to modify existing code?
Destination code, where I have issue with:
.Range("B" & i).EntireRow.Copy _
Destination:=ws.Range("C65536").End(xlUp).Offset(1, 0)
Full code below... it distribution data from one sheet to multiple sheet if value in column match.
Sub DistributeData()
Dim i As Long
Dim LastRow As Long
Dim ws As Worksheet
Dim ErrorLog As String
With Sheets("all")
LastRow = .Range("C65536").End(xlUp).Row
For i = 40 To LastRow
On Error Resume Next
Set ws = Sheets(.Range("B" & i).Text)
On Error GoTo 0
If ws Is Nothing Then
ErrorLog = ErrorLog & vbNewLine & _
"Row:" & i & " Sheet Name: " & .Range("C" & i).Text
Else
.Range("B" & i).EntireRow.Copy _
Destination:=ws.Range("C65536").End(xlUp).Offset(1, 0)
End If
Set ws = Nothing
Next i
End With
Set ws = Nothing
End Sub
Regards,
Izet