discogs
asked on
vba autofill range selection and fill
All,
I keep getting errors when trying to execute the below code. Can anyone see my problem?
I keep getting errors when trying to execute the below code. Can anyone see my problem?
With shtTo
Dim from As Range
Dim too As Range
'specifying addresses
copXrow = xRows - 1
copxRows = shtTo.Cells(1).CurrentRegion.Columns(1).SpecialCells(xlCellTypeVisible).Count
'setting from range
Set from = Range("M" & copXrow & ":" & "EA" & copXrow)
'setting to range
Set too = Range("M" & lastrow & ":" & "EA" & lastrow)
'autofill from too
Range("from").AutoFill Destination:=shtTo.Range("too")
End WithASKER
On line 17. The error "Method 'Range' of object'_Worksheet failed
Does that help?
It seems so stupid to me...
Does that help?
It seems so stupid to me...
ASKER
I even changed line 17 to read:
Range("from").AutoFill Destination:=Range("too")
OK I see the error. when you declare "from" it is a range object not a named range
Change the line to
Change the line to
from.AutoFill Destination:=shtTo.too
Change line 17 to
from.AutoFill Destination:=Range(from, too)
to get rid of the error but I do not think this is what you would want. Can you explain what you want to achieve?
from.AutoFill Destination:=Range(from, too)
to get rid of the error but I do not think this is what you would want. Can you explain what you want to achieve?
ASKER
Okay.
I have not changed anything because you might be able to tell me how to do it better.
Essentially, I copy a large dynamic dataset into a spreadsheet within one workbook.
In doing so, I know the row number to paste to, I also know the number of records I am pasting. For example, rngTo = A150 and record count = 78.
After pasting the records, I then select all records including and out to the right of column(M). After this, I autofill the row I selected (in the above example it would be M149 to EA149) and copy the whole lot down to the bottom of my records as displayed in columns(A through L).
Is there a better way?
I have not changed anything because you might be able to tell me how to do it better.
Essentially, I copy a large dynamic dataset into a spreadsheet within one workbook.
In doing so, I know the row number to paste to, I also know the number of records I am pasting. For example, rngTo = A150 and record count = 78.
After pasting the records, I then select all records including and out to the right of column(M). After this, I autofill the row I selected (in the above example it would be M149 to EA149) and copy the whole lot down to the bottom of my records as displayed in columns(A through L).
Is there a better way?
Can you make my life easier by uploading a few-row fake sample?...unless Michael74 or anyone else has understood it well and can set up his own example.
ASKER
Done.
Please note that I have highlighted and inserted a formula in row 150 as an example.
All formulas from column(M) through to column(EA) require copying all the way to the bottom of my data located in columns(A ~ L) respectively.
Although column M is a static starting point, the end column (EA) is not. So it would be really beneficial if the solution could be dynamic enough to compensate for growth.
TA
Book1.xlsx
Please note that I have highlighted and inserted a formula in row 150 as an example.
All formulas from column(M) through to column(EA) require copying all the way to the bottom of my data located in columns(A ~ L) respectively.
Although column M is a static starting point, the end column (EA) is not. So it would be really beneficial if the solution could be dynamic enough to compensate for growth.
TA
Book1.xlsx
This would find the row 150 using the formula
Sub copym2()
Dim strtSrc As Range
Dim endSrc As Range
Dim strtTgt As Range
Dim endTgt As Range
Set strtSrc = Range("M1").End(xlDown)
Set endSrc = Range("M1").End(xlDown).En
Set strtTgt = Range("M1").End(xlDown).Of
Set endTgt = Range("A1").End(xlDown).Of
Range(strtSrc, endSrc).Copy Range(strtTgt, endTgt)
End Sub
Set strtSrc = Range("M1").End(xlDown)Set endSrc = Range("M1").End(xlDown).End(xlToRight)Sub copym2()
Dim strtSrc As Range
Dim endSrc As Range
Dim strtTgt As Range
Dim endTgt As Range
Set strtSrc = Range("M1").End(xlDown)
Set endSrc = Range("M1").End(xlDown).En
Set strtTgt = Range("M1").End(xlDown).Of
Set endTgt = Range("A1").End(xlDown).Of
Range(strtSrc, endSrc).Copy Range(strtTgt, endTgt)
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Outstanding assistance. Thanks heaps it does exactly what I expected. TA
Michael