VBA fill range

almagg

Well-known Member
Joined
Aug 21, 2002
Messages
705
so i copy a cell then paste in another workbook.
then i want to fill the range for 25 cells to the right.

here is what i recorded to start with, but the rows are going to change
as i go down the list:

Selection.AutoFill Destination:=Range("S65:AD65"), Type:=xlFillDefault

this is what i thought might work:

Selection.AutoFill Destination:=Range(Cells(0, 25)), Type:=xlFillDefault

i am sure it is just a matter of setting up the 'Range' part right.

thanks
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
How will the rows change?

Perhaps something like this.
Code:
Selection.AutoFill Destination:=Selection.Resize(, 25), Type:=xlFillDefault
 

almagg

Well-known Member
Joined
Aug 21, 2002
Messages
705
that made the row height smaller :)

well say the first fill willbe from:

S65:AD65

the next fill, assuming it is in the next row would be:

S66:AD66

but it could be row 70 or another

Here is what I got:

Sub moreforecastlinks()

Do Until ActiveCell.Value = ""

mygl = ActiveCell.Value
On Error GoTo 5 'If 'mygl' is not found
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:=mygl, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
Selection.Copy
ActiveWindow.ActivateNext
ActiveCell.Offset(0, 4).Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"='[2007 Department Expense Summary - 10329.xls]Summary'! _
R[-11]C[-2]" 'Removes the Absolute References
Selection.AutoFill Destination:=Selection.Cells(0,25), _
Type:=xlFillDefault

ActiveCell.Offset(1, -4).Select

5 Loop

End Sub[/b]
 

almagg

Well-known Member
Joined
Aug 21, 2002
Messages
705
that made the row height smaller :)

well say the first fill willbe from:

S65:AD65

the next fill, assuming it is in the next row would be:

S66:AD66

but it could be row 70 or another

Here is what I got and the problem in question is in bold:

Sub moreforecastlinks()

Do Until ActiveCell.Value = ""

mygl = ActiveCell.Value
On Error GoTo 5 'If 'mygl' is not found
ActiveWindow.ActivateNext
Range("A1").Select
Cells.Find(What:=mygl, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 3).Select
Selection.Copy
ActiveWindow.ActivateNext
ActiveCell.Offset(0, 4).Select
ActiveSheet.Paste Link:=True
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"='[2007 Department Expense Summary - 10329.xls]Summary'! _
R[-11]C[-2]" 'Removes the Absolute References
Selection.AutoFill Destination:=Selection.Cells(0,25), _
Type:=xlFillDefault

ActiveCell.Offset(1, -4).Select

5 Loop

End Sub[/b]
 

Forum statistics

Threads
1,137,199
Messages
5,680,118
Members
419,883
Latest member
overhear

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