How to select the autofilter pasted range?

[anirudh2012]

​

I have the following macro to autofilter and then paste it into another excel. Now I want to change some data for the new pasted range. How can I select the range as shown in my comment: because it is a for loop and every time the data will be different <code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">For i =2To lastRow bCell = wb.Sheets("User1").Range("C"& i).Value wb2.Sheets("SubWPRole").Range("G1").AutoFilter Field:=7, Criteria1:=bCell wb2.Sheets("SubWPRole").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Copy wb2.Sheets("Sub").Range("A"& wb2.Sheets("Sub").Rows.Count).End(xlUp).Offset(1) ' this is the line I am referring to Set newrange = wb2.Sheets("Sub").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible) ForEach cell In newrange wb2.Sheets("Sub").Range("B"& cell.Row).Value = wb.Sheets("User1").Range("B"& i).Value Next cell</code>

<tbody>
</tbody>

 

[starl]

crosspost: https://stackoverflow.com/questions/47331197/how-to-select-the-autofilter-pasted-range#

 

[starl]

I would recommend setting a variable to the top leftmost cell that you want to paste to, before you do the copy/paste

Set myCell = wbs.sheets("Sub").Range("A" & wb2.sheets("Sub").rows.count).end(xlup).offset(1)

then, change your copy/paste to:
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">wb2.Sheets("SubWPRole").AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Copy mycell

And for the next line, calculate the new last row on the Sub sheet (replace #ofColumns with the actual number of columns):
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">Set newrange = mycell.resize(,<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;"><code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;"><code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">#ofColumns </code></code></code>).offset(<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">wb2.Sheets("Sub").Range("A"& wb2.Sheets("Sub").Rows.Count).End(xlUp)-mycell.row)</code>
</code>
</code>note: I haven't tested this - will do so and correct if there's any issues.

 

[anirudh2012]

yeah so?

 

[anirudh2012]

@starl : thanx bro