PDA

View Full Version : How to alter Paste Range in VBA



Bazman
04-11-2010, 09:01 AM
Hi there,

I recorded this macro because I want to write my own code where I only copy the code down to a line number dependant upon some other parts of the program.

So I need to change this part fo the code:

Selection.AutoFill Destination:=Range("A17:M54"), Type:=xlFillDefault
Range("A17:M54").Select

i.e. I will still be copying down columns A:M but I will only be using rows 17 to what ever the code needs up to a maximum of 54.

But because the Range("A17:M524") part is in parenthasis I don;t know how to change it?



Sub Macro5()
'
' Macro5 Macro
' Macro recorded 09/04/2010 by abgv247
'

'
Range("A17").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("A57").Select
ActiveSheet.Paste
Range("A17").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.SmallScroll Down:=3
Range("A57").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("A17").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A17:M54"), Type:=xlFillDefault
Range("A17:M54").Select
Range("H48").Select
ActiveSheet.Calculate
Range("A51").Select
ActiveWindow.SmallScroll Down:=6
Rows("57:57").Select
Selection.Delete Shift:=xlUp
Range("C8").Select
End Sub

p45cal
04-11-2010, 10:26 AM
I'm fairly sure the following will do the same as your macro but where I have hard-coded a value for LastRow on the first line, you will have calculated this yourself elsewhere:Sub blah()
LastRow = 26 'this value comes from your "what ever the code needs"
Set xxx = Range(Range("A17"), Range("A17").End(xlToRight))
Set yyy = Range(xxx, xxx.End(xlDown))
yyy.Offset(1).Resize(yyy.Rows.Count - 1).ClearContents
xxx.AutoFill Destination:=Range("A17:M" & LastRow), Type:=xlFillDefault
ActiveSheet.Calculate
End Sub

Bazman
04-11-2010, 01:13 PM
I'm fairly sure the following will do the same as your macro but where I have hard-coded a value for LastRow on the first line, you will have calculated this yourself elsewhere:Sub blah()
LastRow = 26 'this value comes from your "what ever the code needs"
Set xxx = Range(Range("A17"), Range("A17").End(xlToRight))
Set yyy = Range(xxx, xxx.End(xlDown))
yyy.Offset(1).Resize(yyy.Rows.Count - 1).ClearContents
xxx.AutoFill Destination:=Range("A17:M" & LastRow), Type:=xlFillDefault
ActiveSheet.Calculate
End Sub

great thank you!