Set Copy range to depth on on one column of data, then copy extended range width

[Kerry Newman]

In the attached picture the data in G11:J25 will change in rows deep when a slicer changes the data selection. Also the data in columns K,L,M which are VLOOKUPs, will change too.
I want to copy and paste only G11 to M25 (in this case) to cell T11. But my macro is copying G11:M39 (or further actually M20000).
When I select another option in the slicer, the list may be G11: M20. or maybe G11:M35....... I only want ROWS with data in column G,H,I or J but through column M my code is not doing this.....

Sub Macro3()
'
' Macro3 Macro
'

'
Range("H11:M20000" & Cells(Rows.Count, "C").End(xlUp).Row).Select
Selection.Copy
Range("T11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("T8").Select
End Sub

Any suggestions?

 

[mrshl9898]

Try removing the 20000 (the Select and Selection can also go)

Sub Macro3()
'
' Macro3 Macro
'

'
Range("H11:M" & Cells(Rows.Count, "C").End(xlUp).Row).Copy
Range("T11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("T8").Select

 

[Peter_SSs]

Would this also work for you?

Sub Macro3_v2()
  Range("T11:Z" & Range("G" & Rows.Count).End(xlUp).Row).Value = Range("G11:M" & Range("G" & Rows.Count).End(xlUp).Row).Value
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Kerry Newman]

Would this also work for you?

Sub Macro3_v2()
  Range("T11:Z" & Range("G" & Rows.Count).End(xlUp).Row).Value = Range("G11:M" & Range("G" & Rows.Count).End(xlUp).Row).Value
End Sub

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks, that worked perfect

 

[Kerry Newman]

Try removing the 20000 (the Select and Selection can also go)

Sub Macro3()
'
' Macro3 Macro
'

'
Range("H11:M" & Cells(Rows.Count, "C").End(xlUp).Row).Copy
Range("T11").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("T8").Select

Thanks

 

[Peter_SSs]

Thanks, that worked perfect

You're welcome. (Don't forget about updating your account details. )

 

[Kerry Newman]

You're welcome. (Don't forget about updating your account details. )

I tried to figure that out but not very successful, I'll try again.

 

[Peter_SSs]

I'll try again.

Looks like it worked out this time. Thanks.