CODEWITHSUNDEEP
excelvba
Dragan
Dragan

Reputation: 39

Excel VBA paste rows one after other

The idea here is to copy rows from one sheet Samples to a specific place in sheet MainTable. It works fine but I can only add on the beginning of the sheet in other words only on the mentioned range. When I press the button I want it to appear in the beginning, or if there is already one product - after that one and so on. This is the code:

Sub AddProduct()


AddProduct Macro
  Sheets("Samples").Select
   Rows("1:11").Select
   Range("N1").Activate

Application.CutCopyMode = False
   Selection.Copy

  Sheets("MainTable").Select
   Range("12:22").Select
   Selection.Insert Shift:=xlDown

End Sub

Basically the range where the content needs to go should be: Range("12:22")->Range("23:33")->Range("34:44") and so on, depending on existing products...

I thought of a possible solution but I don't know how to do it:

If I add a hidden letter x in column V, in range from sheet Samples, than I can do COUNTIF to that column. The number of xes will tell me how many products I have added in MainTable.

Then I put this code in an empty space in MainTable (in this case O5,P5):

O5:
=11*COUNTIF(V:V;"x")+12

P5:
=11*COUNTIF(V:V;"x")+22

Now the part I don't know:

...
Application.CutCopyMode = False
   Selection.Copy

Sheets("MainTable").Select
   Range("O5.Value:P5.Value").Select
   Selection.Insert Shift:=xlDown

   End Sub

How can I achieve this? Getting values from Cells O5 and P5 into VBA

Upvotes: 0

Views: 276

Answers (1)

Sphinx
Sphinx

Reputation: 660

...    
Dim StartRow as long, EndRow as long 

StartRow = Range("O5").value  
EndRow = Range("P5").value
Range(StartRow & ":" & EndRow).Select
....

Upvotes: 1

Related Questions