Insert new row and paste data

izet99

New Member
Joined
Nov 5, 2009
Messages
14
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 cell start from C25, insert new row and paste data in it. Basicly, I have data in C24 and C30, I would 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:
Code:
.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.

Code:
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Watch MrExcel Video

Forum statistics

Threads
1,123,530
Messages
5,602,190
Members
414,513
Latest member
junbuggle

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top