CODEWITHSUNDEEP
excelvba
余星佑
余星佑

Reputation: 71

How to copy rows from one worksheet to another worksheet (start at specfic row) by using VBA?

I have a worksheet which like this:

enter image description here

Now, I have implemented generating different categories worksheets (i.e Internet, Desktop Publishing, Business worksheet), so I need to insert these product with their name and price to their category worksheet from Rows 4, like this: enter image description here

This is my VBA Code:

Sub task()
Dim Start As Integer
Start = 3

While Cells(Start + 1, 2).Value <> ""
    Start = Start + 1
Wend

Dim category_range As Range
Set category_range = Range("B4:B" & Start)

Dim List As Object
Set List = CreateObject("Scripting.Dictionary")
Dim Rng As Range

For Each Rng In category_range
    If Not List.Exists(Rng.Value) Then
        List.Add Rng.Value, Nothing
    End If
Next Rng

Dim arr(), k As Variant
ReDim arr(List.Count)

Dim wdnmd As Integer
wdnmd = 0
For Each k In List.Keys
    arr(wdnmd) = k
    wdnmd = wdnmd + 1
Next k

Dim category_index As Integer
For category_index = 0 To List.Count - 1
    Sheets.Add(After:=Sheets("Data")).Name = arr(category_index)
    With Sheets(arr(category_index))
        .Columns("A").ColumnWidth = Sheets("Data").Columns("A").ColumnWidth
        .Range("A3").Value = "Product"
        .Range("B3").Value = "Price"
        .Range("A1").Value = "Products in the " & arr(category_index) & " Category"
    End With
Next category_index

Dim target As Range
Set target = Range("A4:C" & Start)
Dim entry As Range, i As Integer
Dim target_worksheet As String
For i = 4 To Start
    target_worksheet = Cells(i, 2).Value
Next i
End Sub

By the last line of my code, we will determine which category the product belongs to, i.e we know that which worksheet contains the product. What is difficult for me is how to make the code always insert new entries start at the 4th row of these three worksheets? I think VBA should have a built-in function to complete this operation. I have tried rows.insert and copy paster methods to be puzzled, but I did not know how to implement this part.

Upvotes: 0

Views: 97

Answers (2)

kshkarin
kshkarin

Reputation: 584

This will copy 5 rows from Sheet1 to Sheet2 starting at row #4, just make sure the ranges are equal, e.g. copying 5 rows range and pasting into 5 rows:

Sheets("sheet1").Range("1:5").Copy Sheets("sheet2").Range("4:9")

Upvotes: 1

MNDevelopments
MNDevelopments

Reputation: 239

To cut the rows you need to define what you want to cut and set it to a variabel like "Variabel"

Variabel.EntireRows.Cut

after that you can try this:

ActiveWorkbook.Worksheets("InsertDesiredWorksheet").range(A2).Columns.End(xlDown).Offset(1, 0).EntireRow.Insert

I've done something similar where above code worked, you might need to tweak it so you can use the variabel for category and name the intended worksheet after it.

Upvotes: 1

Related Questions