adjusting autofill range by referring to last row/column containing data in another sheet

heathball

Board Regular
Joined
Apr 6, 2017
Messages
135
Office Version
  1. 365
Platform
  1. Windows
Im trying to find an addition to the code blow, which adjusts the 'Selection.AutoFill Destination:=Range("A1:JS5338")' options on sheets 2 and 3

is there a way to identify the last column and last row containing data in "sheet 1" and use this range automatically as the autofill option on sheets 2 and 3?

So if on 'sheet 1' the range of columns containing data is A:JS
and the range of rows containing data is 1:5476

the autofill options on sheets 2 and 3 will automatically use the range A1:JS5476

thanks in advance


Code:
    Sheets("2 copy check").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!RC"
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:JS1"), Type:=xlFillDefault
    Range("A1:JS1").Select
    Selection.AutoFill Destination:=Range("A1:JS5338"), Type:=xlFillDefault
    Range("A1:JS5338").Select
    Application.CutCopyMode = False
    Sheets("3 copy paste").Select
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "=Sheet1!RC"
    Range("A1").Select
    Selection.AutoFill Destination:=Range("A1:JS1"), Type:=xlFillDefault
    Range("A1:JS1").Select
    Selection.AutoFill Destination:=Range("A1:JS5338"), Type:=xlFillDefault
    Range("A1:JS5338").Select
    Selection.Copy
 
Last edited:

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
This will fill the formulas on the two destination sheets.

Code:
    [color=darkblue]Dim[/color] strRng [color=darkblue]As[/color] [color=darkblue]String[/color]
    strRng = Sheets("Sheet1").Range("A1").CurrentRegion.Address
    Sheets("2 copy check").Range(strRng).FormulaR1C1 = "=Sheet1!RC"
    Sheets("3 copy paste").Range(strRng).FormulaR1C1 = "=Sheet1!RC"

This will fill the values on the two destination sheets.

Code:
    [color=darkblue]Dim[/color] strRng [color=darkblue]As[/color] [color=darkblue]String[/color]
    strRng = Sheets("Sheet1").Range("A1").CurrentRegion.Address
    Sheets("2 copy check").Range(strRng).Value = Sheets("Sheet1").Range(strRng).Value
    Sheets("3 copy paste").Range(strRng).Value = Sheets("Sheet1").Range(strRng).Value
 
Upvote 0
I see
I have some blank columns in Sheet 1
what would this be changed to include empty columns and extend to the last used column?
strRng = Sheets("Sheet1").Range("A1").CurrentRegion.Address
 
Upvote 0
Thats awesome. Works great! Thanks for your help

Usedrange.....it all kind of makes sense, but a bit hard to find:)
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

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