JackDanIce
Well-known Member
- Joined
- Feb 3, 2010
- Messages
- 9,922
- Office Version
- 365
- Platform
- Windows
Hi,
Inherited some code that I'm working through to optimise and update e.g.
Can be replaced by:
However, what I'd like to know is how to apply this, when you don't know the last row number and the starting row number is different in each sheet e.g.
With this example, Sheet2.K1 is given the value of Sheet1.A1 i.e. not what I want
This works but it's not quite what I want:
I have a feeling .Resize may be the answer and ashamed to say, I've still not got around to understanding exactly how this property works.
Any comments, suggestions?
Thanks,
Jack
Inherited some code that I'm working through to optimise and update e.g.
Rich (BB code):
Sheet1.Range("A1:A200").Copy
Sheet2.Range("B1:B200").Pastespecial Paste:=xlPasteValues
Application.CutCopyMode = False
Rich (BB code):
Sheet2.Range("B1:B200").Value = Sheet1.Range("A1:A200").Value
However, what I'd like to know is how to apply this, when you don't know the last row number and the starting row number is different in each sheet e.g.
Rich (BB code):
Dim i as Long
i = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Sheet2.Range("K1").Value = Sheet1.Range("A30:B" & i).Value
This works but it's not quite what I want:
Rich (BB code):
Dim i as Long
i = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
Sheet2.Range("K1:L" & i - 29).Value = Sheet1.Range("A30:B" & i).Value
Any comments, suggestions?
Thanks,
Jack