data transfer with regular interval

Thespeedy20

New Member
Joined
May 8, 2022
Messages
3
Office Version
  1. 2021
Platform
  1. Windows
Hello forum,
I have data that needs to be transferred to a sheet, max 20 rows and skip 48 to copy the rest of the data (another 20 rows)...until all data is copied pictures
Tableau.jpg

The data must be inserted between A22 and A41, then make the jump of 48 and copy again...repeat the operation as long as there is data
VBA Code:
Sub Transfert()

  Dim CelSource As Range
  Dim CelCible As Range
  Dim Compteur As Integer
  Dim NBlignes As Integer
  Dim a As Integer
  Dim b As Integer
 
  Application.ScreenUpdating = False
  Application.EnableEvents = False
 
  Set CelSource = Worksheets("Trimestre_Formulaire").Range("A5")
  Set CelCible = Worksheets("Test").Range("A23")
 
  'NBlignes = Application.CountA(Sheets("Trimestre_Formulaire").Range("A:A")) - 1
  NBlignes = Worksheets("Trimestre_Formulaire").Range("A" & Rows.Count).End(xlUp).Row
  MsgBox NBlignes
    
   X = Application.RoundUp(NBlignes / 20, 0) * 48
    
    
     For i = 1 To X Step 48
        For Compteur = 1 To 20
        
        
    
    CelCible(a, 1).Value = CelSource(1, 1).Value
    CelCible(a, 2).Value = CelSource(1, 2).Value
    CelCible(a, 3).Value = CelSource(1, 3).Value
    CelCible(a, 4).Value = CelSource(1, 4).Value
    CelCible(a, 5).Value = CelSource(1, 5).Value
    CelCible(a, 6).Value = CelSource(1, 6).Value
    CelCible(a, 7).Value = CelSource(1, 7).Value
    
      
    Set CelSource = CelSource(2)
    Set CelCible = CelCible(2)
    
 
  
    Next
  
      Next
    
      
  Application.ScreenUpdating = True
  Application.EnableEvents = True
End Sub

I have this code but it doesn't work...
Thank you for your help.

OLi
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Forum statistics

Threads
1,223,247
Messages
6,171,004
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top