VBA Code to change range selection where the row is changing

[AhmedAbbas]

I have a situation where I am writing a macro, What macro is supposed to do is (i have also written If I have done it or not)

1. Select the range from C3:G3 in a specific sheet (Achieved)
2. Copy it and take it another specific sheet (Achieved)
3.Go to the last row (Achieved)
4. Add a row and paste it (Achieved)
5. Loop to go back to the original sheet (Achieved)
6. Copy the next Row (Don't know how to set up the counter, I need help here how to dynamically change it to c4:g4 and than c5:g5 and so on)
7. Repeat Again (Achieved)

 

[Hui]

Can you please post you code?

 

[SirJB7]

Hi, AhmedAbbas!
Here goes a blind shot.

<range>.offset(1,0)

Replace <range> by the proper object or expression.
Regards!

 

[AhmedAbbas]

My sincere apologies I should have replied earlier. However I was able to test few things and it worked perfectly. Here is the code

Sub Comms()
Dim A As Worksheet
Dim I As Integer (This was the variable in the Code)
Dim AA As Integer
Dim B As Integer
AA = 2 (This was the counter setup I was looking for)
B = (Range("g2").End(xlDown).Row) (This was the counter setup I was looking for)
For I = AA To B
Range(("C") & I, ("G") & I).Copy (My problem was here to setup range and bring Variable in for dynamically doing the loops)
Worksheets(Range(("B") & I).Value).Select
Range("a15").Select
ActiveCell.End(xlDown).Select
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.PasteSpecial
Worksheets("CS").Select
Next I
End Sub

 

[Hui]

This can be simplified to:

Sub Comms()
Dim A As Worksheet
Dim I As Integer
Dim AA As Integer
Dim B As Integer

AA = 2
B = Range("G2").End(xlDown).Row

For I = AA To B
  Worksheets("CS").Select
  Range("C" & I, "G" & I).Copy
  Worksheets(Range("B" & I).Value).Select
  Range("A" & Range("A" & Rows.Count).End(xlUp).Row).Offset(1, 0).PasteSpecial
Next I

End Sub

If the values in Column B are the same it can be simplified a lot further?

 

[AhmedAbbas]

Unfortunately that's not the case and hence the reason I need a variable