CODEWITHSUNDEEP
excelvba
The Wolf
The Wolf

Reputation: 11

Copying a row, inserting multiple new rows with the pasted info on multiple sheets Excel VBA

Data This is where all the items are with each region code

I have a "New Item" worksheet, a "Data" worksheet, and a "View" worksheet in the same workbook. In my "New Item" worksheet, I have a section B2:H2 for inputing new items and click a button to run the macro. I need help fixing the error. It runs but adds a "true" to each point instead of the copied section.

I need the macro to add a line, copy the region code (in column A; there are over 30 regions but every item is added to a region by default), insert the B2:H2 in the B:H columns of the new line on the "Data" Sheet and also just add it to the end of the "View" sheet. It keeps inputting true now. Why does it do this? Also, why does the code not copy the B2:H2? I am fairly new to VBA.

Also if possible, if there was a way to adjust the chart's Selected Data on the View sheet to add a line to the viewed data, that would be great. Ex: the chart shows 50 lines, and it adds another line to the selected data through the macro.

Thank you in advance for all suggestions. I can clarify anything or upload the excel if needed. Here is the code so far:

Sub AddItem()
   Sheets("Data").Select
        Dim lastRow As Long
        Dim rowPtr As Long
        lastRow = Range("A" & Rows.Count).End(xlUp).Row
    For rowPtr = lastRow To 2 Step -1
        If Not IsEmpty(Range("A" & rowPtr)) Then
          If Range("A" & rowPtr) <> Range("A" & rowPtr - 1) Then
            Range("A" & rowPtr).EntireRow.Insert
          End If
        End If
  Next
   Sheets("New Item").Select
   Application.ScreenUpdating = False
   Dim copySheet As Worksheet
   Dim pasteSheet1 As Worksheet

   Set copySheet = Worksheets("New Item")
   Set pasteSheet1 = Worksheets("Data")

   copySheet.Range("B2:H2").Copy
   pasteSheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 1).PasteSpecial xlPasteValues
   Application.CutCopyMode = False
   Application.ScreenUpdating = True
End Sub

Upvotes: 1

Views: 810

Answers (1)

Plagon
Plagon

Reputation: 3139

So im not sure if this is what you want. 

Sub Add()
Dim Data As Worksheet, NItem As Worksheet, View As Worksheet
Dim lRow As Long

Application.ScreenUpdating = False

Set NItem = Worksheets("New Item")
Set Data = Worksheets("Data")
Set View = Worksheets("View")

lRow = View.Cells(View.Rows.Count, 2).End(xlUp).Row

Data.Range("A2").EntireRow.Insert
NItem.Range("B2:H2").Copy
Data.Range("B2").PasteSpecial xlPasteValues
View.Cells(lRow + 1, 2).PasteSpecial xlPasteValues

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

But this will add a line in Row 2 in the Datasheet, paste in the values. (Your For Loop inserts a new line after every entry, why?). It also pastes it in the viewsheet. Other than that please avoid using .Select and objectify .Range, .Cells etc. like you've done in the second part of the code.

Upvotes: 0

Related Questions