Yikes, Help. I have a macro that Cuts and Pastes data from a form(really a
worksheet) to another worksheet. The data from each form is appended to the
data contained in a master worksheet. Columns "C:O" have multiple rows and
are filled first in the paste. Columns "A" only have one row of
corresponding data and need to autofill to the end of data in Column "E". The
number of rows filled with the autofill change with every form. The macro
below but I need "A" to fill to the end of data in column "E" for each
form that is entered.


Sub Summary_To_Data()
'
' Summary_To_Data Macro
Sheets("Summary").Select
Range("B5").Select
Selection.Copy
Sheets("All_Data").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Summary").Select
Range("E5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("All_Data").Select
Range("B65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Summary").Select
Range("I5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("All_Data").Select
Range("C65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Summary").Select
Range("I6").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("All_Data").Select
Range("D65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Summary").Select
Range("A9:K9").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("All_Data").Select
Range("E65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste
Range("A22").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A211") <- HERE. Range changes
lots
Range("A211").Select
End Sub


Thanks

On Jul 23, 2:55*pm, el dee <el d...@discussions.microsoft.com> wrote:
> Yikes, Help. *I have a macro that Cuts and Pastes data from a form(really a
> worksheet) to another worksheet. The data from each form is appended to the
> data contained in a master worksheet. Columns "C:O" have multiple rows and
> are filled first in the paste. Columns "A" only have one row of
> corresponding data and need to autofill to the end of data in Column "E".The
> number of rows filled with the autofill change with every form. The macro
> below but I need *"A" to fill to the end of data in column "E" for each
> form that is entered. * *
>
> Sub Summary_To_Data()
> '
> ' Summary_To_Data Macro
> * * Sheets("Summary").Select
> * * Range("B5").Select
> * * Selection.Copy
> * * Sheets("All_Data").Select
> * * Range("A65536").End(xlUp).Offset(1, 0).Select
> * * ActiveSheet.Paste
> * * Sheets("Summary").Select
> * * Range("E5").Select
> * * Application.CutCopyMode = False
> * * Selection.Copy
> * * Sheets("All_Data").Select
> * * Range("B65536").End(xlUp).Offset(1, 0).Select
> * * ActiveSheet.Paste
> * * Sheets("Summary").Select
> * * Range("I5").Select
> * * Application.CutCopyMode = False
> * * Selection.Copy
> * * Sheets("All_Data").Select
> * * Range("C65536").End(xlUp).Offset(1, 0).Select
> * * ActiveSheet.Paste
> * * Sheets("Summary").Select
> * * Range("I6").Select
> * * Application.CutCopyMode = False
> * * Selection.Copy
> * * Sheets("All_Data").Select
> * * Range("D65536").End(xlUp).Offset(1, 0).Select
> * * ActiveSheet.Paste
> * * Sheets("Summary").Select
> * * Range("A9:K9").Select
> * * Range(Selection, Selection.End(xlDown)).Select
> * * Application.CutCopyMode = False
> * * Selection.Copy
> * * Sheets("All_Data").Select
> * * Range("E65536").End(xlUp).Offset(1, 0).Select
> * * ActiveSheet.Paste
> * * Range("A22").Select
> * * Application.CutCopyMode = False
> * * Selection.AutoFill Destination:=Range("A211") * <- HERE. Range changes
> lots
> * * Range("A211").Select
> End Sub
>
> Thanks

el dee,

I don't really follow your AutoFill comment. You'll need to be more
specific than stating "Range changes"; how does the range change
exactly? Also, which worksheet do you want to AutoFill? I include a
somewhat simplified code block below.

Best,

Matthew Herbert

Sub Summary_To_Data()
Dim wksCopy As Worksheet
Dim wksPaste As Worksheet
Dim rngCopy As Range
Dim rngPaste As Range

With ThisWorkbook
Set wksCopy = .Worksheets("Summary")
Set wksPaste = .Worksheets("All_Data")
End With

Set rngCopy = SetCopyRange(wksCopy, "B5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "A")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "E5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "B")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "I5")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "C")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "I6")
Set rngPaste = SetPasteRangeByColumn(wksPaste, "D")
rngCopy.Copy rngPaste

Set rngCopy = SetCopyRange(wksCopy, "A9:K9").CurrentRegion
Set rngPaste = SetPasteRangeByColumn(wksPaste, "E")
rngCopy.Copy rngPaste

'Which worksheet?
Range("A22").AutoFill Destination:=Range("A211")

End Sub

Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
Set SetCopyRange = Wks.Range(strAddress)
End Function

Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String)
As Range
Dim lngRow As Long
lngRow = Wks.Rows.Count
Set SetPasteRangeByColumn = Wks.Cells(lngRow, strColumn).End
(xlUp).Offset(1, 0)
End Function

Thanks Matthew! As you can see, I am a still VBA amaturish but am working on
it. Sorry about the ambiguous Range Changes.

As per

'Which worksheet? -- The same worksheet, the final master worksheet
"All_Data"

The range changes are something like follows:
Range("A22").AutoFill Destination:=Range("A211")--The start range and
end range of cells may change from something like
("A22").AutoFill Destination:=Range("A211") to
("A1212").AutoFill Destination:=Range ("A1226") to
("A2727").AutoFill Destination:=Range ("A3546")... on so on.
The end of the fill (ie "D") should be determined by the end of entries in
row "E" and the number of rows autofilled is not a constant.

Thanks in advance!



"Matthew Herbert" wrote:

> On Jul 23, 2:55 pm, el dee <el d...@discussions.microsoft.com> wrote:
> > Yikes, Help. I have a macro that Cuts and Pastes data from a form(really a
> > worksheet) to another worksheet. The data from each form is appended to the
> > data contained in a master worksheet. Columns "C:O" have multiple rows and
> > are filled first in the paste. Columns "A" only have one row of
> > corresponding data and need to autofill to the end of data in Column "E". The
> > number of rows filled with the autofill change with every form. The macro
> > below but I need "A" to fill to the end of data in column "E" for each
> > form that is entered.
> >
> > Sub Summary_To_Data()
> > '
> > ' Summary_To_Data Macro
> > Sheets("Summary").Select
> > Range("B5").Select
> > Selection.Copy
> > Sheets("All_Data").Select
> > Range("A65536").End(xlUp).Offset(1, 0).Select
> > ActiveSheet.Paste
> > Sheets("Summary").Select
> > Range("E5").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Sheets("All_Data").Select
> > Range("B65536").End(xlUp).Offset(1, 0).Select
> > ActiveSheet.Paste
> > Sheets("Summary").Select
> > Range("I5").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Sheets("All_Data").Select
> > Range("C65536").End(xlUp).Offset(1, 0).Select
> > ActiveSheet.Paste
> > Sheets("Summary").Select
> > Range("I6").Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Sheets("All_Data").Select
> > Range("D65536").End(xlUp).Offset(1, 0).Select
> > ActiveSheet.Paste
> > Sheets("Summary").Select
> > Range("A9:K9").Select
> > Range(Selection, Selection.End(xlDown)).Select
> > Application.CutCopyMode = False
> > Selection.Copy
> > Sheets("All_Data").Select
> > Range("E65536").End(xlUp).Offset(1, 0).Select
> > ActiveSheet.Paste
> > Range("A22").Select
> > Application.CutCopyMode = False
> > Selection.AutoFill Destination:=Range("A211") <- HERE. Range changes
> > lots
> > Range("A211").Select
> > End Sub
> >
> > Thanks
>
> el dee,
>
> I don't really follow your AutoFill comment. You'll need to be more
> specific than stating "Range changes"; how does the range change
> exactly? Also, which worksheet do you want to AutoFill? I include a
> somewhat simplified code block below.
>
> Best,
>
> Matthew Herbert
>
> Sub Summary_To_Data()
> Dim wksCopy As Worksheet
> Dim wksPaste As Worksheet
> Dim rngCopy As Range
> Dim rngPaste As Range
>
> With ThisWorkbook
> Set wksCopy = .Worksheets("Summary")
> Set wksPaste = .Worksheets("All_Data")
> End With
>
> Set rngCopy = SetCopyRange(wksCopy, "B5")
> Set rngPaste = SetPasteRangeByColumn(wksPaste, "A")
> rngCopy.Copy rngPaste
>
> Set rngCopy = SetCopyRange(wksCopy, "E5")
> Set rngPaste = SetPasteRangeByColumn(wksPaste, "B")
> rngCopy.Copy rngPaste
>
> Set rngCopy = SetCopyRange(wksCopy, "I5")
> Set rngPaste = SetPasteRangeByColumn(wksPaste, "C")
> rngCopy.Copy rngPaste
>
> Set rngCopy = SetCopyRange(wksCopy, "I6")
> Set rngPaste = SetPasteRangeByColumn(wksPaste, "D")
> rngCopy.Copy rngPaste
>
> Set rngCopy = SetCopyRange(wksCopy, "A9:K9").CurrentRegion
> Set rngPaste = SetPasteRangeByColumn(wksPaste, "E")
> rngCopy.Copy rngPaste
>
> 'Which worksheet?
> Range("A22").AutoFill Destination:=Range("A211")
>
> End Sub
>
> Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
> Set SetCopyRange = Wks.Range(strAddress)
> End Function
>
> Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String)
> As Range
> Dim lngRow As Long
> lngRow = Wks.Rows.Count
> Set SetPasteRangeByColumn = Wks.Cells(lngRow, strColumn).End
> (xlUp).Offset(1, 0)
> End Function
>

On Jul 23, 4:09*pm, el dee <el...@discussions.microsoft.com> wrote:
> Thanks Matthew! As you can see, I am a still VBA amaturish but am workingon
> it. *Sorry about the ambiguous Range Changes.
>
> As per
>
> 'Which worksheet? *-- The same worksheet, the final master worksheet
> "All_Data"
>
> The range changes are something like follows:
> Range("A22").AutoFill Destination:=Range("A211")--The start range and
> end range of cells may change from something like
> ("A22").AutoFill Destination:=Range("A211") to
> ("A1212").AutoFill Destination:=Range ("A1226") *to
> ("A2727").AutoFill Destination:=Range ("A3546")... on so on.
> The end of the fill *(ie "D") should be determined by the end of entries in
> row "E" and the number of rows autofilled is not a constant.
>
> Thanks in advance!
>
>
>
> "Matthew Herbert" wrote:
> > On Jul 23, 2:55 pm, el dee <el d...@discussions.microsoft.com> wrote:
> > > Yikes, Help. *I have a macro that Cuts and Pastes data from a form(really a
> > > worksheet) to another worksheet. The data from each form is appended to the
> > > data contained in a master worksheet. Columns "C:O" have multiple rows and
> > > are filled first in the paste. Columns "A" only have one row of
> > > corresponding data and need to autofill to the end of data in Column "E". The
> > > number of rows filled with the autofill change with every form. The macro
> > > below but I need *"A" to fill to the end of data in column "E" for each
> > > form that is entered. * *
>
> > > Sub Summary_To_Data()
> > > '
> > > ' Summary_To_Data Macro
> > > * * Sheets("Summary").Select
> > > * * Range("B5").Select
> > > * * Selection.Copy
> > > * * Sheets("All_Data").Select
> > > * * Range("A65536").End(xlUp).Offset(1, 0).Select
> > > * * ActiveSheet.Paste
> > > * * Sheets("Summary").Select
> > > * * Range("E5").Select
> > > * * Application.CutCopyMode = False
> > > * * Selection.Copy
> > > * * Sheets("All_Data").Select
> > > * * Range("B65536").End(xlUp).Offset(1, 0).Select
> > > * * ActiveSheet.Paste
> > > * * Sheets("Summary").Select
> > > * * Range("I5").Select
> > > * * Application.CutCopyMode = False
> > > * * Selection.Copy
> > > * * Sheets("All_Data").Select
> > > * * Range("C65536").End(xlUp).Offset(1, 0).Select
> > > * * ActiveSheet.Paste
> > > * * Sheets("Summary").Select
> > > * * Range("I6").Select
> > > * * Application.CutCopyMode = False
> > > * * Selection.Copy
> > > * * Sheets("All_Data").Select
> > > * * Range("D65536").End(xlUp).Offset(1, 0).Select
> > > * * ActiveSheet.Paste
> > > * * Sheets("Summary").Select
> > > * * Range("A9:K9").Select
> > > * * Range(Selection, Selection.End(xlDown)).Select
> > > * * Application.CutCopyMode = False
> > > * * Selection.Copy
> > > * * Sheets("All_Data").Select
> > > * * Range("E65536").End(xlUp).Offset(1, 0).Select
> > > * * ActiveSheet.Paste
> > > * * Range("A22").Select
> > > * * Application.CutCopyMode = False
> > > * * Selection.AutoFill Destination:=Range("A211") * <- HERE.. Range changes
> > > lots
> > > * * Range("A211").Select
> > > End Sub
>
> > > Thanks
>
> > el dee,
>
> > I don't really follow your AutoFill comment. *You'll need to be more
> > specific than stating "Range changes"; how does the range change
> > exactly? *Also, which worksheet do you want to AutoFill? *I includea
> > somewhat simplified code block below.
>
> > Best,
>
> > Matthew Herbert
>
> > Sub Summary_To_Data()
> > Dim wksCopy As Worksheet
> > Dim wksPaste As Worksheet
> > Dim rngCopy As Range
> > Dim rngPaste As Range
>
> > With ThisWorkbook
> > * * Set wksCopy = .Worksheets("Summary")
> > * * Set wksPaste = .Worksheets("All_Data")
> > End With
>
> > Set rngCopy = SetCopyRange(wksCopy, "B5")
> > Set rngPaste = SetPasteRangeByColumn(wksPaste, "A")
> > rngCopy.Copy rngPaste
>
> > Set rngCopy = SetCopyRange(wksCopy, "E5")
> > Set rngPaste = SetPasteRangeByColumn(wksPaste, "B")
> > rngCopy.Copy rngPaste
>
> > Set rngCopy = SetCopyRange(wksCopy, "I5")
> > Set rngPaste = SetPasteRangeByColumn(wksPaste, "C")
> > rngCopy.Copy rngPaste
>
> > Set rngCopy = SetCopyRange(wksCopy, "I6")
> > Set rngPaste = SetPasteRangeByColumn(wksPaste, "D")
> > rngCopy.Copy rngPaste
>
> > Set rngCopy = SetCopyRange(wksCopy, "A9:K9").CurrentRegion
> > Set rngPaste = SetPasteRangeByColumn(wksPaste, "E")
> > rngCopy.Copy rngPaste
>
> > 'Which worksheet?
> > Range("A22").AutoFill Destination:=Range("A211")
>
> > End Sub
>
> > Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
> > * * Set SetCopyRange = Wks.Range(strAddress)
> > End Function
>
> > Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String)
> > As Range
> > Dim lngRow As Long
> > lngRow = Wks.Rows.Count
> > Set SetPasteRangeByColumn = Wks.Cells(lngRow, strColumn).End
> > (xlUp).Offset(1, 0)
> > End Function- Hide quoted text -
>
> - Show quoted text -

el dee,

I'm not sure how you define your first range, i.e. the address to the
left of the colon in the range preceeding .AutoFill, but getting the
last range isn't too tough because you told me how. You can get the
end of the entires in column "E" by still using the Function I
provided; however, I'm not sure how you are getting the start of
entries (hence why this will still be outstanding). Some additional
code is added below with comments/questions.

Best,

Matt

Dim rngFill As Range
Dim rngFillDst As Range
Dim lngStartRow As Long

'how do you determine the row for the .AutoFill range?
lngStartRow = 2

'also, is it always Axx?
Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow)

'end of entries in E Offset -1 rows and -1 columns
Set rngFillDst = SetPasteRangeByColumn(wksPaste, "E").Offset(-1, -1)

'how do you get the starting range for the .AutoFill destination?
Set rngFillDst = Union(?StartOfTheRangeHere?, rngFillDst)

Thanks again Matt, I appreciate your help.

Here are some additional details:

'how do you determine the row for the .AutoFill range?
lngStartRow = 2

The start row is determined by a new entry… So, data gets transferred from
Sheet(�Summary�) to Sheet(“All_Data�). Then sheet(“summary�) is cleared
using the following: (Sorry, I am working on using more block code.)

Sub ClearForm()

Sheets("Summary").Select
Range("B5").Select
ActiveCell.FormulaR1C1 = ""
Range("E5").Select
ActiveCell.FormulaR1C1 = ""
Range("I5").Select
ActiveCell.FormulaR1C1 = ""
Range("I6").Select
ActiveCell.FormulaR1C1 = ""



For InputRowIndex = 10 To MAX_ROW
Range("A" & InputRowIndex).Select
If ActiveCell.FormulaR1C1 = "" Then
Exit For
Else
ActiveCell.FormulaR1C1 = ""
Range("B" & InputRowIndex).Select
ActiveCell.FormulaR1C1 = ""
Range("C" & InputRowIndex).Select
ActiveCell.FormulaR1C1 = ""
Range("D" & InputRowIndex).Select
ActiveCell.FormulaR1C1 = ""
Range("E" & InputRowIndex).Select
ActiveCell.FormulaR1C1 = ""
Range("F" & InputRowIndex).Select
ActiveCell.FormulaR1C1 = ""
Range("G" & InputRowIndex).Select
ActiveCell.FormulaR1C1 = ""
Range("H" & InputRowIndex).Select
ActiveCell.FormulaR1C1 = ""
Range("I" & InputRowIndex).Select
ActiveCell.FormulaR1C1 = ""
Range("J" & InputRowIndex).Select
ActiveCell.FormulaR1C1 = ""
Range("K" & InputRowIndex).Select
ActiveCell.FormulaR1C1 = ""

End If
Next

Range("A3").Select
End Sub

New data is then entered into sheet ("Summary") and transferred to Sheet
"(All_Data") which will be the start of a new (Axx).autofill

'also, is it always Axx? Yep.
Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow)


'how do you get the starting range for the .AutoFill destination?
Set rngFillDst = Union((Range"A65536").End(xlUp).Offset(1,
0).SelectrngFillDst))

Ie.. the first empty cell below the last filled cell in “A�

Much thanks,
Leah aka el dee



"Matthew Herbert" wrote:

> On Jul 23, 4:09 pm, el dee <el...@discussions.microsoft.com> wrote:
> > Thanks Matthew! As you can see, I am a still VBA amaturish but am working on
> > it. Sorry about the ambiguous Range Changes.
> >
> > As per
> >
> > 'Which worksheet? -- The same worksheet, the final master worksheet
> > "All_Data"
> >
> > The range changes are something like follows:
> > Range("A22").AutoFill Destination:=Range("A211")--The start range and
> > end range of cells may change from something like
> > ("A22").AutoFill Destination:=Range("A211") to
> > ("A1212").AutoFill Destination:=Range ("A1226") to
> > ("A2727").AutoFill Destination:=Range ("A3546")... on so on.
> > The end of the fill (ie "D") should be determined by the end of entries in
> > row "E" and the number of rows autofilled is not a constant.
> >
> > Thanks in advance!
> >
> >
> >
> > "Matthew Herbert" wrote:
> > > On Jul 23, 2:55 pm, el dee <el d...@discussions.microsoft.com> wrote:
> > > > Yikes, Help. I have a macro that Cuts and Pastes data from a form(really a
> > > > worksheet) to another worksheet. The data from each form is appended to the
> > > > data contained in a master worksheet. Columns "C:O" have multiple rows and
> > > > are filled first in the paste. Columns "A" only have one row of
> > > > corresponding data and need to autofill to the end of data in Column "E". The
> > > > number of rows filled with the autofill change with every form. The macro
> > > > below but I need "A" to fill to the end of data in column "E" for each
> > > > form that is entered.
> >
> > > > Sub Summary_To_Data()
> > > > '
> > > > ' Summary_To_Data Macro
> > > > Sheets("Summary").Select
> > > > Range("B5").Select
> > > > Selection.Copy
> > > > Sheets("All_Data").Select
> > > > Range("A65536").End(xlUp).Offset(1, 0).Select
> > > > ActiveSheet.Paste
> > > > Sheets("Summary").Select
> > > > Range("E5").Select
> > > > Application.CutCopyMode = False
> > > > Selection.Copy
> > > > Sheets("All_Data").Select
> > > > Range("B65536").End(xlUp).Offset(1, 0).Select
> > > > ActiveSheet.Paste
> > > > Sheets("Summary").Select
> > > > Range("I5").Select
> > > > Application.CutCopyMode = False
> > > > Selection.Copy
> > > > Sheets("All_Data").Select
> > > > Range("C65536").End(xlUp).Offset(1, 0).Select
> > > > ActiveSheet.Paste
> > > > Sheets("Summary").Select
> > > > Range("I6").Select
> > > > Application.CutCopyMode = False
> > > > Selection.Copy
> > > > Sheets("All_Data").Select
> > > > Range("D65536").End(xlUp).Offset(1, 0).Select
> > > > ActiveSheet.Paste
> > > > Sheets("Summary").Select
> > > > Range("A9:K9").Select
> > > > Range(Selection, Selection.End(xlDown)).Select
> > > > Application.CutCopyMode = False
> > > > Selection.Copy
> > > > Sheets("All_Data").Select
> > > > Range("E65536").End(xlUp).Offset(1, 0).Select
> > > > ActiveSheet.Paste
> > > > Range("A22").Select
> > > > Application.CutCopyMode = False
> > > > Selection.AutoFill Destination:=Range("A211") <- HERE.. Range changes
> > > > lots
> > > > Range("A211").Select
> > > > End Sub
> >
> > > > Thanks
> >
> > > el dee,
> >
> > > I don't really follow your AutoFill comment. You'll need to be more
> > > specific than stating "Range changes"; how does the range change
> > > exactly? Also, which worksheet do you want to AutoFill? I include a
> > > somewhat simplified code block below.
> >
> > > Best,
> >
> > > Matthew Herbert
> >
> > > Sub Summary_To_Data()
> > > Dim wksCopy As Worksheet
> > > Dim wksPaste As Worksheet
> > > Dim rngCopy As Range
> > > Dim rngPaste As Range
> >
> > > With ThisWorkbook
> > > Set wksCopy = .Worksheets("Summary")
> > > Set wksPaste = .Worksheets("All_Data")
> > > End With
> >
> > > Set rngCopy = SetCopyRange(wksCopy, "B5")
> > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "A")
> > > rngCopy.Copy rngPaste
> >
> > > Set rngCopy = SetCopyRange(wksCopy, "E5")
> > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "B")
> > > rngCopy.Copy rngPaste
> >
> > > Set rngCopy = SetCopyRange(wksCopy, "I5")
> > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "C")
> > > rngCopy.Copy rngPaste
> >
> > > Set rngCopy = SetCopyRange(wksCopy, "I6")
> > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "D")
> > > rngCopy.Copy rngPaste
> >
> > > Set rngCopy = SetCopyRange(wksCopy, "A9:K9").CurrentRegion
> > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "E")
> > > rngCopy.Copy rngPaste
> >
> > > 'Which worksheet?
> > > Range("A22").AutoFill Destination:=Range("A211")
> >
> > > End Sub
> >
> > > Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
> > > Set SetCopyRange = Wks.Range(strAddress)
> > > End Function
> >
> > > Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String)
> > > As Range
> > > Dim lngRow As Long
> > > lngRow = Wks.Rows.Count
> > > Set SetPasteRangeByColumn = Wks.Cells(lngRow, strColumn).End
> > > (xlUp).Offset(1, 0)
> > > End Function- Hide quoted text -
> >
> > - Show quoted text -
>
> el dee,
>
> I'm not sure how you define your first range, i.e. the address to the
> left of the colon in the range preceeding .AutoFill, but getting the
> last range isn't too tough because you told me how. You can get the
> end of the entires in column "E" by still using the Function I
> provided; however, I'm not sure how you are getting the start of
> entries (hence why this will still be outstanding). Some additional
> code is added below with comments/questions.
>
> Best,
>
> Matt
>
> Dim rngFill As Range
> Dim rngFillDst As Range
> Dim lngStartRow As Long
>
> 'how do you determine the row for the .AutoFill range?
> lngStartRow = 2
>
> 'also, is it always Axx?
> Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow)
>
> 'end of entries in E Offset -1 rows and -1 columns
> Set rngFillDst = SetPasteRangeByColumn(wksPaste, "E").Offset(-1, -1)
>
> 'how do you get the starting range for the .AutoFill destination?
> Set rngFillDst = Union(?StartOfTheRangeHere?, rngFillDst)
>
>

On Jul 23, 5:01*pm, el dee <el...@discussions.microsoft.com> wrote:
> Thanks again Matt, I appreciate your help.
>
> Here are some additional details:
>
> 'how do you determine the row for the .AutoFill range?
> lngStartRow = 2
>
> * The start row is determined by a new entry… So, data gets transferred from
> Sheet(”Summary”) to Sheet(“All_Data”). *Then sheet(“summary”) is cleared
> using the following: (Sorry, I am working on using more block code.)
>
> Sub ClearForm()
>
> * * Sheets("Summary").Select
> * * Range("B5").Select
> * * ActiveCell.FormulaR1C1 = ""
> * * Range("E5").Select
> * * ActiveCell.FormulaR1C1 = ""
> * * Range("I5").Select
> * * ActiveCell.FormulaR1C1 = ""
> * * Range("I6").Select
> * * ActiveCell.FormulaR1C1 = ""
>
> * * For InputRowIndex = 10 To MAX_ROW
> * * * * Range("A" & InputRowIndex).Select
> * * * * If ActiveCell.FormulaR1C1 = "" Then
> * * * * * * Exit For
> * * * * Else
> * * * * * * ActiveCell.FormulaR1C1 = ""
> * * * * * * Range("B" & InputRowIndex).Select
> * * * * * * ActiveCell.FormulaR1C1 = ""
> * * * * * * Range("C" & InputRowIndex).Select
> * * * * * * ActiveCell.FormulaR1C1 = ""
> * * * * * * Range("D" & InputRowIndex).Select
> * * * * * * ActiveCell.FormulaR1C1 = ""
> * * * * * * Range("E" & InputRowIndex).Select
> * * * * * * ActiveCell.FormulaR1C1 = ""
> * * * * * * Range("F" & InputRowIndex).Select
> * * * * * * ActiveCell.FormulaR1C1 = ""
> * * * * * * Range("G" & InputRowIndex).Select
> * * * * * * ActiveCell.FormulaR1C1 = ""
> * * * * * * Range("H" & InputRowIndex).Select
> * * * * * * ActiveCell.FormulaR1C1 = ""
> * * * * * * Range("I" & InputRowIndex).Select
> * * * * * * ActiveCell.FormulaR1C1 = ""
> * * * * * * Range("J" & InputRowIndex).Select
> * * * * * * ActiveCell.FormulaR1C1 = ""
> * * * * * * Range("K" & InputRowIndex).Select
> * * * * * * ActiveCell.FormulaR1C1 = ""
>
> * * * * End If
> * * Next
>
> * * Range("A3").Select
> End Sub
>
> New data is then entered into sheet ("Summary") and transferred to Sheet
> "(All_Data") which will be the start of a new (Axx).autofill
>
> 'also, is it always Axx? *Yep.
> Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow)
>
> 'how do you get the starting range for the .AutoFill destination?
> Set rngFillDst = Union((Range"A65536").End(xlUp).Offset(1,
> 0).SelectrngFillDst))
>
> Ie.. the first empty cell below the last filled cell in “A”
>
> Much thanks,
> Leah *aka el dee
>
>
>
> "Matthew Herbert" wrote:
> > On Jul 23, 4:09 pm, el dee <el...@discussions.microsoft.com> wrote:
> > > Thanks Matthew! As you can see, I am a still VBA amaturish but am working on
> > > it. *Sorry about the ambiguous Range Changes.
>
> > > As per
>
> > > 'Which worksheet? *-- The same worksheet, the final master worksheet
> > > "All_Data"
>
> > > The range changes are something like follows:
> > > Range("A22").AutoFill Destination:=Range("A211")--The start range and
> > > end range of cells may change from something like
> > > ("A22").AutoFill Destination:=Range("A211") to
> > > ("A1212").AutoFill Destination:=Range ("A1226") *to
> > > ("A2727").AutoFill Destination:=Range ("A3546")... on so on.
> > > The end of the fill *(ie "D") should be determined by the end of entries in
> > > row "E" and the number of rows autofilled is not a constant.
>
> > > Thanks in advance!
>
> > > "Matthew Herbert" wrote:
> > > > On Jul 23, 2:55 pm, el dee <el d...@discussions.microsoft.com> wrote:
> > > > > Yikes, Help. *I have a macro that Cuts and Pastes data from a form(really a
> > > > > worksheet) to another worksheet. The data from each form is appended to the
> > > > > data contained in a master worksheet. Columns "C:O" have multiplerows and
> > > > > are filled first in the paste. Columns "A" only have one row of
> > > > > corresponding data and need to autofill to the end of data in Column "E". The
> > > > > number of rows filled with the autofill change with every form. The macro
> > > > > below but I need *"A" to fill to the end of data in column "E" for each
> > > > > form that is entered. * *
>
> > > > > Sub Summary_To_Data()
> > > > > '
> > > > > ' Summary_To_Data Macro
> > > > > * * Sheets("Summary").Select
> > > > > * * Range("B5").Select
> > > > > * * Selection.Copy
> > > > > * * Sheets("All_Data").Select
> > > > > * * Range("A65536").End(xlUp).Offset(1, 0).Select
> > > > > * * ActiveSheet.Paste
> > > > > * * Sheets("Summary").Select
> > > > > * * Range("E5").Select
> > > > > * * Application.CutCopyMode = False
> > > > > * * Selection.Copy
> > > > > * * Sheets("All_Data").Select
> > > > > * * Range("B65536").End(xlUp).Offset(1, 0).Select
> > > > > * * ActiveSheet.Paste
> > > > > * * Sheets("Summary").Select
> > > > > * * Range("I5").Select
> > > > > * * Application.CutCopyMode = False
> > > > > * * Selection.Copy
> > > > > * * Sheets("All_Data").Select
> > > > > * * Range("C65536").End(xlUp).Offset(1, 0).Select
> > > > > * * ActiveSheet.Paste
> > > > > * * Sheets("Summary").Select
> > > > > * * Range("I6").Select
> > > > > * * Application.CutCopyMode = False
> > > > > * * Selection.Copy
> > > > > * * Sheets("All_Data").Select
> > > > > * * Range("D65536").End(xlUp).Offset(1, 0).Select
> > > > > * * ActiveSheet.Paste
> > > > > * * Sheets("Summary").Select
> > > > > * * Range("A9:K9").Select
> > > > > * * Range(Selection, Selection.End(xlDown)).Select
> > > > > * * Application.CutCopyMode = False
> > > > > * * Selection.Copy
> > > > > * * Sheets("All_Data").Select
> > > > > * * Range("E65536").End(xlUp).Offset(1, 0).Select
> > > > > * * ActiveSheet.Paste
> > > > > * * Range("A22").Select
> > > > > * * Application.CutCopyMode = False
> > > > > * * Selection.AutoFill Destination:=Range("A211") * <- HERE.. Range changes
> > > > > lots
> > > > > * * Range("A211").Select
> > > > > End Sub
>
> > > > > Thanks
>
> > > > el dee,
>
> > > > I don't really follow your AutoFill comment. *You'll need to be more
> > > > specific than stating "Range changes"; how does the range change
> > > > exactly? *Also, which worksheet do you want to AutoFill? *I include a
> > > > somewhat simplified code block below.
>
> > > > Best,
>
> > > > Matthew Herbert
>
> > > > Sub Summary_To_Data()
> > > > Dim wksCopy As Worksheet
> > > > Dim wksPaste As Worksheet
> > > > Dim rngCopy As Range
> > > > Dim rngPaste As Range
>
> > > > With ThisWorkbook
> > > > * * Set wksCopy = .Worksheets("Summary")
> > > > * * Set wksPaste = .Worksheets("All_Data")
> > > > End With
>
> > > > Set rngCopy = SetCopyRange(wksCopy, "B5")
> > > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "A")
> > > > rngCopy.Copy rngPaste
>
> > > > Set rngCopy = SetCopyRange(wksCopy, "E5")
> > > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "B")
> > > > rngCopy.Copy rngPaste
>
> > > > Set rngCopy = SetCopyRange(wksCopy, "I5")
> > > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "C")
> > > > rngCopy.Copy rngPaste
>
> > > > Set rngCopy = SetCopyRange(wksCopy, "I6")
> > > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "D")
> > > > rngCopy.Copy rngPaste
>
> > > > Set rngCopy = SetCopyRange(wksCopy, "A9:K9").CurrentRegion
> > > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "E")
> > > > rngCopy.Copy rngPaste
>
> > > > 'Which worksheet?
> > > > Range("A22").AutoFill Destination:=Range("A211")
>
> > > > End Sub
>
> > > > Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
> > > > * * Set SetCopyRange = Wks.Range(strAddress)
> > > > End Function
>
> > > > Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String)
> > > > As Range
> > > > Dim lngRow As Long
> > > > lngRow = Wks.Rows.Count
> > > > Set SetPasteRangeByColumn = Wks.Cells(lngRow, strColumn).End
> > > > (xlUp).Offset(1, 0)
> > > > End Function- Hide quoted text -
>
> > > - Show quoted text -
>
> > el dee,
>
> > I'm not sure how you define your first range, i.e. the address to the
> > left of the colon in the range preceeding .AutoFill, but getting the
> > last range isn't too tough because you told me how. *You can get the
> > end of the entires in column "E" by still using the Function I
> > provided; however, I'm not sure how you are getting the start of
> > entries (hence why this will still be outstanding). *Some additional
> > code is added below with comments/questions.
>
> > Best,
>
> > Matt
>
> > Dim rngFill As Range
> > Dim rngFillDst As Range
> > Dim lngStartRow As Long
>
> > 'how do you determine the row for the .AutoFill range?
> > lngStartRow = 2
>
> > 'also, is it always Axx?
> > Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow)
>
> > 'end of entries in E Offset -1 rows and -1 columns
> > Set rngFillDst = SetPasteRangeByColumn(wksPaste, "E").Offset(-1, -1)
>
> > 'how do you get the starting range for the .AutoFill destination?
> > Set rngFillDst = Union(?StartOfTheRangeHere?, rngFillDst)- Hide quoted text -
>
> - Show quoted text -

Leah,

Your "ClearForm" can be greatly simplified to something like the
following:

With Sheets("Summary")
.Range("B5,E5,I5,I6").Clear
If .Range("A10").Value <> "" Then
.Range("A10:K" & MAX_ROW).Clear
End If
End With

Your fill section can look something like the following:

lngStartRow = SetPasteRangeByColumn(wksPaste, "A").Offset(-1, 0)
Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow)
Set rngFillDst = SetPasteRangeByColumn(wksPaste, "E").Offset(-1, -1)
Set rngFillDst = Union(rngFill.Cells(1), rngFillDst)
rngFill.AutoFill rngFillDst

Also, you'll want to take advantage of the debugging tools (Debug menu
in VBE). Particuallarly, you'll want to step through your programs by
hitting F8 repeatedly (Debug | Step Into). As the yellow line moves
from one line to the next, you can place your cursor over variable
names to see how the variable is behaving. With objects, you can
print object properties to the Immediate Window (View | Immediate
Window), among other things. For example, if you wanted to make sure
that rngFill is the correct range you could insert a line of code in
the procedure that will print the Address of the range object to the
Immediate Window, i.e. Debug.Print rngFill.Address. .Address also has
very useful parameters such as setting the relative reference of the
Address and returning the External Address, e.g. Debug.Print
rngFill.Address(External:=True). As you type a defined range object,
such as rngFill, once you type "." you'll notice that the Intellisense
window pops up with a number of properties and methods available to
you. Once you select (via the arrow keys) or type a name, you can hit
TAB to AutoComplete the name. Also, adding Breakpoints (Debug |
Toggle Breakpoint - F9) will pause the code execution until you tell
the program to continue running (Run | Continue - F5, or the Green
Play button on the Standard Toolbar).

Best,

Matt

Thank you so much!!!


"Matthew Herbert" wrote:

> On Jul 23, 5:01 pm, el dee <el...@discussions.microsoft.com> wrote:
> > Thanks again Matt, I appreciate your help.
> >
> > Here are some additional details:
> >
> > 'how do you determine the row for the .AutoFill range?
> > lngStartRow = 2
> >
> > The start row is determined by a new entry… So, data gets transferred from
> > Sheet(�Summary�) to Sheet(“All_Data�). Then sheet(“summary�) is cleared
> > using the following: (Sorry, I am working on using more block code.)
> >
> > Sub ClearForm()
> >
> > Sheets("Summary").Select
> > Range("B5").Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("E5").Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("I5").Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("I6").Select
> > ActiveCell.FormulaR1C1 = ""
> >
> > For InputRowIndex = 10 To MAX_ROW
> > Range("A" & InputRowIndex).Select
> > If ActiveCell.FormulaR1C1 = "" Then
> > Exit For
> > Else
> > ActiveCell.FormulaR1C1 = ""
> > Range("B" & InputRowIndex).Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("C" & InputRowIndex).Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("D" & InputRowIndex).Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("E" & InputRowIndex).Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("F" & InputRowIndex).Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("G" & InputRowIndex).Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("H" & InputRowIndex).Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("I" & InputRowIndex).Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("J" & InputRowIndex).Select
> > ActiveCell.FormulaR1C1 = ""
> > Range("K" & InputRowIndex).Select
> > ActiveCell.FormulaR1C1 = ""
> >
> > End If
> > Next
> >
> > Range("A3").Select
> > End Sub
> >
> > New data is then entered into sheet ("Summary") and transferred to Sheet
> > "(All_Data") which will be the start of a new (Axx).autofill
> >
> > 'also, is it always Axx? Yep.
> > Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow)
> >
> > 'how do you get the starting range for the .AutoFill destination?
> > Set rngFillDst = Union((Range"A65536").End(xlUp).Offset(1,
> > 0).SelectrngFillDst))
> >
> > Ie.. the first empty cell below the last filled cell in “A�
> >
> > Much thanks,
> > Leah aka el dee
> >
> >
> >
> > "Matthew Herbert" wrote:
> > > On Jul 23, 4:09 pm, el dee <el...@discussions.microsoft.com> wrote:
> > > > Thanks Matthew! As you can see, I am a still VBA amaturish but am working on
> > > > it. Sorry about the ambiguous Range Changes.
> >
> > > > As per
> >
> > > > 'Which worksheet? -- The same worksheet, the final master worksheet
> > > > "All_Data"
> >
> > > > The range changes are something like follows:
> > > > Range("A22").AutoFill Destination:=Range("A211")--The start range and
> > > > end range of cells may change from something like
> > > > ("A22").AutoFill Destination:=Range("A211") to
> > > > ("A1212").AutoFill Destination:=Range ("A1226") to
> > > > ("A2727").AutoFill Destination:=Range ("A3546")... on so on.
> > > > The end of the fill (ie "D") should be determined by the end of entries in
> > > > row "E" and the number of rows autofilled is not a constant.
> >
> > > > Thanks in advance!
> >
> > > > "Matthew Herbert" wrote:
> > > > > On Jul 23, 2:55 pm, el dee <el d...@discussions.microsoft.com> wrote:
> > > > > > Yikes, Help. I have a macro that Cuts and Pastes data from a form(really a
> > > > > > worksheet) to another worksheet. The data from each form is appended to the
> > > > > > data contained in a master worksheet. Columns "C:O" have multiple rows and
> > > > > > are filled first in the paste. Columns "A" only have one row of
> > > > > > corresponding data and need to autofill to the end of data in Column "E". The
> > > > > > number of rows filled with the autofill change with every form. The macro
> > > > > > below but I need "A" to fill to the end of data in column "E" for each
> > > > > > form that is entered.
> >
> > > > > > Sub Summary_To_Data()
> > > > > > '
> > > > > > ' Summary_To_Data Macro
> > > > > > Sheets("Summary").Select
> > > > > > Range("B5").Select
> > > > > > Selection.Copy
> > > > > > Sheets("All_Data").Select
> > > > > > Range("A65536").End(xlUp).Offset(1, 0).Select
> > > > > > ActiveSheet.Paste
> > > > > > Sheets("Summary").Select
> > > > > > Range("E5").Select
> > > > > > Application.CutCopyMode = False
> > > > > > Selection.Copy
> > > > > > Sheets("All_Data").Select
> > > > > > Range("B65536").End(xlUp).Offset(1, 0).Select
> > > > > > ActiveSheet.Paste
> > > > > > Sheets("Summary").Select
> > > > > > Range("I5").Select
> > > > > > Application.CutCopyMode = False
> > > > > > Selection.Copy
> > > > > > Sheets("All_Data").Select
> > > > > > Range("C65536").End(xlUp).Offset(1, 0).Select
> > > > > > ActiveSheet.Paste
> > > > > > Sheets("Summary").Select
> > > > > > Range("I6").Select
> > > > > > Application.CutCopyMode = False
> > > > > > Selection.Copy
> > > > > > Sheets("All_Data").Select
> > > > > > Range("D65536").End(xlUp).Offset(1, 0).Select
> > > > > > ActiveSheet.Paste
> > > > > > Sheets("Summary").Select
> > > > > > Range("A9:K9").Select
> > > > > > Range(Selection, Selection.End(xlDown)).Select
> > > > > > Application.CutCopyMode = False
> > > > > > Selection.Copy
> > > > > > Sheets("All_Data").Select
> > > > > > Range("E65536").End(xlUp).Offset(1, 0).Select
> > > > > > ActiveSheet.Paste
> > > > > > Range("A22").Select
> > > > > > Application.CutCopyMode = False
> > > > > > Selection.AutoFill Destination:=Range("A211") <- HERE.. Range changes
> > > > > > lots
> > > > > > Range("A211").Select
> > > > > > End Sub
> >
> > > > > > Thanks
> >
> > > > > el dee,
> >
> > > > > I don't really follow your AutoFill comment. You'll need to be more
> > > > > specific than stating "Range changes"; how does the range change
> > > > > exactly? Also, which worksheet do you want to AutoFill? I include a
> > > > > somewhat simplified code block below.
> >
> > > > > Best,
> >
> > > > > Matthew Herbert
> >
> > > > > Sub Summary_To_Data()
> > > > > Dim wksCopy As Worksheet
> > > > > Dim wksPaste As Worksheet
> > > > > Dim rngCopy As Range
> > > > > Dim rngPaste As Range
> >
> > > > > With ThisWorkbook
> > > > > Set wksCopy = .Worksheets("Summary")
> > > > > Set wksPaste = .Worksheets("All_Data")
> > > > > End With
> >
> > > > > Set rngCopy = SetCopyRange(wksCopy, "B5")
> > > > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "A")
> > > > > rngCopy.Copy rngPaste
> >
> > > > > Set rngCopy = SetCopyRange(wksCopy, "E5")
> > > > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "B")
> > > > > rngCopy.Copy rngPaste
> >
> > > > > Set rngCopy = SetCopyRange(wksCopy, "I5")
> > > > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "C")
> > > > > rngCopy.Copy rngPaste
> >
> > > > > Set rngCopy = SetCopyRange(wksCopy, "I6")
> > > > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "D")
> > > > > rngCopy.Copy rngPaste
> >
> > > > > Set rngCopy = SetCopyRange(wksCopy, "A9:K9").CurrentRegion
> > > > > Set rngPaste = SetPasteRangeByColumn(wksPaste, "E")
> > > > > rngCopy.Copy rngPaste
> >
> > > > > 'Which worksheet?
> > > > > Range("A22").AutoFill Destination:=Range("A211")
> >
> > > > > End Sub
> >
> > > > > Function SetCopyRange(Wks As Worksheet, strAddress As String) As Range
> > > > > Set SetCopyRange = Wks.Range(strAddress)
> > > > > End Function
> >
> > > > > Function SetPasteRangeByColumn(Wks As Worksheet, strColumn As String)
> > > > > As Range
> > > > > Dim lngRow As Long
> > > > > lngRow = Wks.Rows.Count
> > > > > Set SetPasteRangeByColumn = Wks.Cells(lngRow, strColumn).End
> > > > > (xlUp).Offset(1, 0)
> > > > > End Function- Hide quoted text -
> >
> > > > - Show quoted text -
> >
> > > el dee,
> >
> > > I'm not sure how you define your first range, i.e. the address to the
> > > left of the colon in the range preceeding .AutoFill, but getting the
> > > last range isn't too tough because you told me how. You can get the
> > > end of the entires in column "E" by still using the Function I
> > > provided; however, I'm not sure how you are getting the start of
> > > entries (hence why this will still be outstanding). Some additional
> > > code is added below with comments/questions.
> >
> > > Best,
> >
> > > Matt
> >
> > > Dim rngFill As Range
> > > Dim rngFillDst As Range
> > > Dim lngStartRow As Long
> >
> > > 'how do you determine the row for the .AutoFill range?
> > > lngStartRow = 2
> >
> > > 'also, is it always Axx?
> > > Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow)
> >
> > > 'end of entries in E Offset -1 rows and -1 columns
> > > Set rngFillDst = SetPasteRangeByColumn(wksPaste, "E").Offset(-1, -1)
> >
> > > 'how do you get the starting range for the .AutoFill destination?
> > > Set rngFillDst = Union(?StartOfTheRangeHere?, rngFillDst)- Hide quoted text -
> >
> > - Show quoted text -
>
> Leah,
>
> Your "ClearForm" can be greatly simplified to something like the
> following:
>
> With Sheets("Summary")
> .Range("B5,E5,I5,I6").Clear
> If .Range("A10").Value <> "" Then
> .Range("A10:K" & MAX_ROW).Clear
> End If
> End With
>
> Your fill section can look something like the following:
>
> lngStartRow = SetPasteRangeByColumn(wksPaste, "A").Offset(-1, 0)
> Set rngFill = wksPaste.Range("A" & lngStartRow, "D" & lngStartRow)
> Set rngFillDst = SetPasteRangeByColumn(wksPaste, "E").Offset(-1, -1)
> Set rngFillDst = Union(rngFill.Cells(1), rngFillDst)
> rngFill.AutoFill rngFillDst
>
> Also, you'll want to take advantage of the debugging tools (Debug menu
> in VBE). Particuallarly, you'll want to step through your programs by
> hitting F8 repeatedly (Debug | Step Into). As the yellow line moves
> from one line to the next, you can place your cursor over variable
> names to see how the variable is behaving. With objects, you can
> print object properties to the Immediate Window (View | Immediate
> Window), among other things. For example, if you wanted to make sure
> that rngFill is the correct range you could insert a line of code in
> the procedure that will print the Address of the range object to the
> Immediate Window, i.e. Debug.Print rngFill.Address. .Address also has
> very useful parameters such as setting the relative reference of the
> Address and returning the External Address, e.g. Debug.Print
> rngFill.Address(External:=True). As you type a defined range object,
> such as rngFill, once you type "." you'll notice that the Intellisense
> window pops up with a number of properties and methods available to
> you. Once you select (via the arrow keys) or type a name, you can hit
> TAB to AutoComplete the name. Also, adding Breakpoints (Debug |
> Toggle Breakpoint - F9) will pause the code execution until you tell
> the program to continue running (Run | Continue - F5, or the Green
> Play button on the Standard Toolbar).
>
> Best,
>
> Matt
>