VBA Excel, Copy-Paste while looping

Question

why can not i copy the range from sheet 1 to sheet 3? any suggestion would be appreciated.

For i = 4 To 20 Step 1
   ChemSheet.cshCalc       
   Worksheets("Tabelle1").Range(Cells(32, 7), Cells(32, 31)).Value = Worksheets("Tabelle1").Range(Cells(9, 7), Cells(9, 31)).Value
 

  '(here where i get error 1004 ):
   Worksheets("Tabelle3").Range(Cells(i, 1), Cells(i, 99)).Value = Worksheets("Tabelle1").Range(Cells(32, 7), Cells(32, 105)).Value

   Worksheets("Tabelle1").Range(Cells(32, 7), Cells(32, 31)).Value = Worksheets("Tabelle1").Range(Cells(9, 7), Cells(9, 31)).Value
Next i

Is "Tabelle3" a valid name? Just checking since all the other ones are "Tabelle1". — Christofer Weber, Apr 26 '21 at 12:36
If you want to copy in all 20 rows the same value `Worksheets("Tabelle1").Range(Cells(32, 7), Cells(32, 105)).Value` no need of any iteration. It can be copied at once. Of course, if these sheets really exist (named as you do). Then, why copying from 40 times that range of 'Tabelle1' worksheet? Can you better explain **in words** what you need accomplishing? I cannot understand from your code, sorry... — FaneDuru, Apr 26 '21 at 12:42
1. Copying the values from the destinations Worksheets("Tabelle1").Range(Cells(32, 7), Cells(32, 105)) [ range of values] 2. paste it at Worksheets("Tabelle3").Range(Cells(i, 1), Cells(i, 99)).Value. this cant be accomplished in one step, due to the number of steps for the solution, thats why i need For loop. shall i explain more? the orksheets("Tabelle1").Range(Cells(32, 7), Cells(32, 105)).Value it is Oki, it works. — Mahmoued Salem, Apr 26 '21 at 12:48
You need to [qualify the `Cells` calls with the worksheet](https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet). — BigBen, Apr 26 '21 at 12:57
@Christofer Weber : yes it is a valid name, tabelle 3(sheet 3) is where i want to copy those values from Tabelle1(sheet 1)! — Mahmoued Salem, Apr 26 '21 at 13:00

VBA Excel, Copy-Paste while looping

0 Answers0