copy data from one excel and paste it to specific row dynamically using vba

[alex gomes]

I have data in Sheet1 and am trying to copy Range("A1:B8") from sheet1 to Sheet2

But while copying there are some condition
Conditions are as follows

1.First time when i will run the vba code , it should copy the data from sheet1 and paste in sheet2 in Range("E6:F13")
2.Second time when i will run the code ,it should copy the data from sheet1 and paste in Range("H6:I13") in Sheet2
3. Third time when i will run the vba code, it should copy the data from sheet1 and paste in Range("K6:L3") in Sheet2
4.Third time when i will run the vba code it data should be pasted in Range(N6:O13") in Sheet2
5.Third time when i will run the same vba code, it should copy the data from sheet1 and paste in Range("Q6:R13") in Sheet2

Note: Data in row5 in Sheet 2 will be already there

Plz go throught the attached doc

Thanks in Advance!!!

 

[alex gomes]

I am not able to edit the title of this Thread

Correct one is
How to copy Data from one excel sheet to another by criteria

 

[Craig Hatmaker]

I'll let others answer your question. I'm going to provide something else - what I believe is better.

Your data structured is best categorized as a crosstab table. Crosstab tables cannot be pivoted, filtered, sorted, easily summed, or easily expanded. If you want to see the science behind what is 'bad' about crosstab tables, Google "Database normalization". I'm sure you know why you want your crosstab format and have a long list of reasons for it. Everyone always does. I'm not sure you know why something else is better.

A simple (normalized) table can be sorted, filtered, summed (easily), pivoted, expanded (easily), exported, etc. With a simple table the code to add a range is this (see attached).

Sub Post()

  Dim oSource As Range
  Dim lPTE  As Long
 
  lPTE = WorksheetFunction.Max([MyData[PTE]]) + 1
 
  With [MyData].ListObject.ListRows.Add
      Set oSource = Worksheets("Sheet1").Range("A1").CurrentRegion
      oSource.Copy .Range(2)
     .Range(1).Resize(oSource.Rows.Count, 1) = lPTE
  End With
 
End Sub

Sorry for preaching.

 

[alex gomes]

thanks for the suggestion but i don't need this, I need exactly what I posted in the question