A single line code error

Wee Kuang

Board Regular
Joined
Sep 15, 2011
Messages
59
Code:
Selection.AutoFill Destination:=Range("A" & LastRow & ":" & "A" & NoofRw), Type:=xlFillDefault
What I was trying to do was to fill up a cells using the "=" on Sheet1 linking with Sheet2.

Is there something wrong with the syntax?

I got a error saying 'AutoFill method of Range class failed'

Thanks.
 
Last edited:

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Try making it TRIM(STR(LastRow)) and TRIM(STR(NoofRw)). That converts a number to a string and removes the leading spaces that creep in.
 
Upvote 0
Str is unnecessary and actually adds the space that you then Trim, BTW.

What are the values of LastRow and NoofRw and is the selected cell currently the top cell of that range?
 
Upvote 0
The STR may be unneccesary (though good practice), but I think the trim is necessary.

?"A"&1
A 1
 
Upvote 0
Trim is not necessary unless you use Str:
Code:
?"A" & 1
and CStr would be better, but still unnecessary.
 
Upvote 0
Hi guys. Thanks for your feedback.
LastRow = Worksheets("Main").Cells(Rows.Count, "A").End(xlUp).Row
ShtRw = Sheet(WSCount).Cells(Rows.Count, "A").End(xlUp).Row

What I wanted to do was to loop my workbook from 4th to the last worksheet and link all the filled data in column A of the looped worksheets to the "Main" Worksheet column A without a break in data. My code only manage to do it for the first WSCount properly and after that, the linking became incorrect.

For example , for the 2nd Sheet(WSCount=6), the lastrow+1 cell of the Main sheet is suppose to be linked to Range(A2) of Sheet(WSCount=6), instead it was linked to the the A98(note the previous link to WSCount=1 stopped at 97). The worksheet linked was correct except for the number!

Code:
Sub linklinklink()
'
'
'
Dim LastWS As Integer
Dim LastRow As Integer
Dim ShtRow As Integer
Dim MyRange As String

Application.ScreenUpdating = False

'Sheets(1).Select
'Sheets.Add.Name = "Main"
    
LastRow = Worksheets("Main").Cells(Rows.Count, "A").End(xlUp).Row
LastWS = Worksheets.Count

For WSCount = 4 To LastWS

TotalDwgRef = Sheets(WSCount).Cells(1, Columns.Count).End(xlToLeft).Column

If TotalDwgRef = 27 Then

Sheets(WSCount).Select
Range("A2").Select

ShtRow = Sheets(WSCount).Cells(Rows.Count, "A").End(xlUp).Row - 1   '-1 because of heading
Sheets("Main").Select
Range("A" & LastRow + 1).Select 'New data to be added on the next line after the last row in "Main"
ActiveCell.FormulaR1C1 = "='" & Sheets(WSCount).Name & "'!R[]C[]"
MyRange = ActiveCell.Address & ":" & ActiveCell.Offset(ShtRow, 0).Address
Selection.AutoFill Destination:=Range(MyRange), Type:=xlFillDefault

LastRow = LastRow + ShtRow

EndIf

Next WSCount

Application.ScreenUpdating = True
    
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,152
Messages
6,170,388
Members
452,323
Latest member
GoJones

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