VBA Coding autofill destination variable

Johns90

New Member
Joined
Feb 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,629
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Welcome to MrExcel Message Board.
How about:
VBA Code:
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

New Member
Joined
Feb 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi thanks for your help,
it's not working :(
Please see the test tab below that corresponding to my needs

STAFFIDTest1Test2Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12Test13Test14Test15Test16Test17Test18Test19Test20Test21RegionDep.Product
ITSTAFF0001Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12Test13
35796​
USD
347,934​
0​
0​
347,934​
NAAA
ITSTAFF0001Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12Test13
35796​
USD
347,934​
0​
0​
347,934​
NAAD
ITSTAFF0001Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12Test13
35796​
USD
347,934​
0​
0​
347,934​
NAAG
ITSTAFF0001Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12Test13
35796​
USD
347,934​
0​
0​
347,934​
NAAL
ITSTAFF0001Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12Test13
35796​
USD
347,934​
0​
0​
347,934​
NAAL
ITSTAFF0001Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12Test13
35796​
USD
347,934​
0​
0​
347,934​
APAL
ITSTAFF0001Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12Test13
35796​
USD
347,934​
0​
0​
347,934​
APFI
ITSTAFF0001Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12Test13
35796​
USD
347,934​
0​
0​
347,934​
APFM
ITSTAFF0001Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12Test13
35796​
USD
347,934​
0​
0​
347,934​
APFM
ITSTAFF0001Test3Test4Test5Test6Test7Test8Test9Test10Test11Test12Test13
35796​
USD
347,934​
0​
0​
347,934​
APSQ
ITSTAFF0019Test14Test15Test16Test17Test18Test19Test20Test21Test22Test23Test24Test25TND
46,4​
0,165​
1200​
1254,056​
NAAA
NAAD
NAAG
NAAL
NAAL
NAAL
NAFI
NAFM
NAFM
NAFN
NALE
NALC
NALE
NALL
NASG
NEAA
NEAD
NEAG
NEAL
NEAL
NEAL


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

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
Try
VBA Code:
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

New Member
Joined
Feb 3, 2021
Messages
11
Office Version
  1. 365
Platform
  1. Windows
Hi, it's working :)
Thanks a lot for your help. This is perfect
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows
You are welcome
And thank you for the feedback
Be happy & safe
 

Watch MrExcel Video

Forum statistics

Threads
1,127,500
Messages
5,625,157
Members
416,075
Latest member
TechJosh

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
Top