VBA Coding autofill destination variable

[Johns90]

Hi everyone,

Please I would need your help

My case : I have the column A to S filled, the number of lines is variable monthly but this is the same information (duplicate) and i put news lines into the column A to S (end of variable lines), and I would like also duplicate the new lines until empty cell from my column W (who is eveytime filled with other info). The issue with my code it's working until COPY. the end of the code for autofill destination who is variable is not working

Please see my code :

Range("A1048576").End(xlUp).Offset(0, 0).Select
Range(ActiveCell, ActiveCell.Offset(0, 19)).Copy
DernLigne = Range("W" & Rows.Count).End(xlUp).Row
Range("A1048576").End(xlUp).Offset(1, 0).AutoFill Destination:=Range("A2:A" & DernLigne)

what the issue is and the solution according to you ?

Thanks for your help

 

[maabadi]

Welcome to MrExcel Message Board.
How about:

Sub Autofilling()
Dim Lr1 As Long, Lr2 As Long
Lr1 = Range("A" & Rows.Count).End(xlUp).Row
Lr2 = Range("W" & Rows.Count).End(xlUp).Row
Range("A" & Lr1).Offset(0, 22).AutoFill Destination:=Range("A" & Lr1 & ":V" & Lr2)
End Sub

 

[Johns90]

Hi thanks for your help,
it's not working
Please see the test tab below that corresponding to my needs

STAFFID

Test1

Test2

Test3

Test4

Test5

Test6

Test7

Test8

Test9

Test10

Test11

Test12

Test13

Test14

Test15

Test16

Test17

Test18

Test19

Test20

Test21

Region

Dep.

Product

ITSTAFF0001

Test3

Test4

Test5

Test6

Test7

Test8

Test9

Test10

Test11

Test12

Test13

35796​

USD

347,934​

0​

0​

347,934​

NA

A

A

ITSTAFF0001

Test3

Test4

Test5

Test6

Test7

Test8

Test9

Test10

Test11

Test12

Test13

35796​

USD

347,934​

0​

0​

347,934​

NA

A

D

ITSTAFF0001

Test3

Test4

Test5

Test6

Test7

Test8

Test9

Test10

Test11

Test12

Test13

35796​

USD

347,934​

0​

0​

347,934​

NA

A

G

ITSTAFF0001

Test3

Test4

Test5

Test6

Test7

Test8

Test9

Test10

Test11

Test12

Test13

35796​

USD

347,934​

0​

0​

347,934​

NA

A

L

ITSTAFF0001

Test3

Test4

Test5

Test6

Test7

Test8

Test9

Test10

Test11

Test12

Test13

35796​

USD

347,934​

0​

0​

347,934​

NA

A

L

ITSTAFF0001

Test3

Test4

Test5

Test6

Test7

Test8

Test9

Test10

Test11

Test12

Test13

35796​

USD

347,934​

0​

0​

347,934​

AP

A

L

ITSTAFF0001

Test3

Test4

Test5

Test6

Test7

Test8

Test9

Test10

Test11

Test12

Test13

35796​

USD

347,934​

0​

0​

347,934​

AP

F

I

ITSTAFF0001

Test3

Test4

Test5

Test6

Test7

Test8

Test9

Test10

Test11

Test12

Test13

35796​

USD

347,934​

0​

0​

347,934​

AP

F

M

ITSTAFF0001

Test3

Test4

Test5

Test6

Test7

Test8

Test9

Test10

Test11

Test12

Test13

35796​

USD

347,934​

0​

0​

347,934​

AP

F

M

ITSTAFF0001

Test3

Test4

Test5

Test6

Test7

Test8

Test9

Test10

Test11

Test12

Test13

35796​

USD

347,934​

0​

0​

347,934​

AP

S

Q

ITSTAFF0019

Test14

Test15

Test16

Test17

Test18

Test19

Test20

Test21

Test22

Test23

Test24

Test25

TND

46,4​

0,165​

1200​

1254,056​

NA

A

A

NA

A

D

NA

A

G

NA

A

L

NA

A

L

NA

A

L

NA

F

I

NA

F

M

NA

F

M

NA

F

N

NA

L

E

NA

L

C

NA

L

E

NA

L

L

NA

S

G

NE

A

A

NE

A

D

NE

A

G

NE

A

L

NE

A

L

NE

A

L

the first 10 lines are variable each time (example line with ITSTAFF0001). I import at the end of the line a new line (example line with ITSTAFF0019 ; column A to S) and i would like to diplcated not all the line but information of the ITSTAFF0019 to 1254.056 (from colum A to S) unti empty cell from colum region

i hope it's helping you

Thanks

 

[mohadin]

Try

Sub darligton()
    Range("A1048576").End(xlUp).Offset(0, 0).Select
    a = Range(ActiveCell, ActiveCell.Offset(0, 19))
    DernLigne = Range("W" & Rows.Count).End(xlUp).Row
    DernLigne1 = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1048576").End(xlUp).Offset(1, 0).Resize(DernLigne - DernLigne1, 19) = a
End Sub

 

[Johns90]

Hi, it's working
Thanks a lot for your help. This is perfect

 

[mohadin]

You are welcome
And thank you for the feedback
Be happy & safe