Auto Fill Columns A and B with varying ranges

[el dee]

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

 

[Matthew Herbert]

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

 

[el dee]

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]

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!


















- 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)

 

[el dee]

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]

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











- 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

 

[el dee]

Thank you so much!!!

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