ProgramUser
Board Regular
- Joined
- Apr 15, 2014
- Messages
- 75
Currently using W10, Excel Pro 2013 (Both 64Bit)
Dear Excellers!!!!!
With only a few hairs left upon my head, and after ‘days!’ of searching and trying to work out ‘why’ I’m getting silly errors with my code….I come to you for your assistance.
What I have thus far;
I have two workbooks, Book1.xlsm and Book2.xlsm
Book1 contains references to products with some reporting guidelines via data validation etc (trivial) located in range Columns A through to Column K and the list is dynamic and regularly is appended to;
I can already achieve this via my own working code;
Within Book1 - I take the information from sheet tab “Source1”, copy it to sheet tab “Sorted”, then sort it via another macro (this works well)
Requirement;
So…
Book2 (would be closed at this point) and is the book that I need to copy data to, from Book1 (which is open). The copy process involves taking a range (found at book1.xlsm, sheet “Sorted”), taking the last 5 rows from that found range, then pasting it into book2 into sheet named “Vendor_List_Report”.
Where I’m falling over is ascertaining the ‘range’ to Search through (A:K). For Example;
I have my code attached but am constantly receiving a runtime error ‘13’, type mismatch. I have tried numerous methods but if I manage to get another code approach to work, then I can’t get the loop to work (not seen here), or if the looping code is good, I can’t get past error 423 workbook cannot be found or similar….very frustrating.
I am showing you the type of code I’ve modified rather than all the code snippets I’ve attempted.
Ideally, it would be nice to have the code assess to see if Book2 was open (workbook.open = No) or not first, if not open, then open it, perform the copy/paste, then close book2. More importantly, it’s the last 5 rows that are crucial to the end-game here.
If you feel that I’d be better to look at another post that I may have missed, then please suggest this….if not and you can assist me directly, then I’d be just as happy for someone to edit my code accordingly. Again, you never stop learning however, sometimes you can’t see the forest for the trees.
Begin Book1 HTML
(sheet tab – “Source1”)
Excel 2012
<tbody>
</tbody>
<tbody>
</tbody>
END HTML – Book1-Source1
Begin Book1 HTML
(sheet tab – “Sorted”)
Excel 2012
<tbody>
</tbody>
<tbody>
</tbody>
End HTML – Book1-Sorted
Begin Code
Thanks in advance.
ProgramUser
Dear Excellers!!!!!
With only a few hairs left upon my head, and after ‘days!’ of searching and trying to work out ‘why’ I’m getting silly errors with my code….I come to you for your assistance.
What I have thus far;
I have two workbooks, Book1.xlsm and Book2.xlsm
Book1 contains references to products with some reporting guidelines via data validation etc (trivial) located in range Columns A through to Column K and the list is dynamic and regularly is appended to;
I can already achieve this via my own working code;
Within Book1 - I take the information from sheet tab “Source1”, copy it to sheet tab “Sorted”, then sort it via another macro (this works well)
Requirement;
So…
Book2 (would be closed at this point) and is the book that I need to copy data to, from Book1 (which is open). The copy process involves taking a range (found at book1.xlsm, sheet “Sorted”), taking the last 5 rows from that found range, then pasting it into book2 into sheet named “Vendor_List_Report”.
Where I’m falling over is ascertaining the ‘range’ to Search through (A:K). For Example;
Code:
Set r1Last5Rows = wsFrom.Range("A" & r1LastRow - 5 & ":K" & r1LastRow)I have my code attached but am constantly receiving a runtime error ‘13’, type mismatch. I have tried numerous methods but if I manage to get another code approach to work, then I can’t get the loop to work (not seen here), or if the looping code is good, I can’t get past error 423 workbook cannot be found or similar….very frustrating.
I am showing you the type of code I’ve modified rather than all the code snippets I’ve attempted.
Ideally, it would be nice to have the code assess to see if Book2 was open (workbook.open = No) or not first, if not open, then open it, perform the copy/paste, then close book2. More importantly, it’s the last 5 rows that are crucial to the end-game here.
If you feel that I’d be better to look at another post that I may have missed, then please suggest this….if not and you can assist me directly, then I’d be just as happy for someone to edit my code accordingly. Again, you never stop learning however, sometimes you can’t see the forest for the trees.
Begin Book1 HTML
(sheet tab – “Source1”)
Excel 2012
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SKU | RAMTYPE | Freq | Socket | Brand | Fail_Rate | Temp | Rail_Voltage | Installation | Network_Service | Date_Last_Visited |
| 2 | 1110838 | 2 | 1333 | DDR3 | G.Skill | 101 | Below 0 | 1.1 | Res. | Network | Today |
| 3 | 1110983 | 4 | 2400 | DDR4 | Corsair | 101 | Below 0 | 1.1 | Res. | Network | Today |
<tbody>
</tbody>
Source1
Worksheet Formulas
<tbody> </tbody> |
<tbody>
</tbody>
END HTML – Book1-Source1
Begin Book1 HTML
(sheet tab – “Sorted”)
Excel 2012
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SKU | RAMTYPE | Freq | Socket | Brand | Fail_Rate | Temp | Rail_Voltage | Installation | Network_Service | Date_Last_Visited |
| 2 | 1110983 | 4 | 2400 | DDR4 | Corsair | 101 | Below 0 | 1.1 | Res. | Network | Today |
| 3 | 1111024 | 3 | 2000 | DDR4 | Corsair | 104 | Hot | 2.15 | Machine | House | Yesterday |
<tbody>
</tbody>
Sorted
Worksheet Formulas
<tbody> </tbody> |
<tbody>
</tbody>
End HTML – Book1-Sorted
Begin Code
Code:
' Credits go to Duly noted authors below
' Inspiration from: From https://stackoverflow.com/questions/38693039/selecting-last-8-rows-in-excel-vba
' &
' Inspiration from: http://www.vbaexpress.com/forum/archive/index.php/t-45117.html
' &
' Inspiration from: https://stackoverflow.com/questions/38693039/selecting-last-8-rows-in-excel-vba/38693171
Sub CopyLast5Rowstest()
Dim wsFrom As Worksheet, wsTo As Worksheet
Set wsFrom = Workbooks("Book1.xlsm").Worksheets("Sorted") ' Copying from here
Set wsTo = Workbooks("Book2.xlsm").Worksheets("Vendor_List_Report") ' pasting to here
Call CopyPaste(wsFrom.Range("A:A"), wsTo.Range("A:A"))
Call CopyPaste(wsFrom.Range("B:B"), wsTo.Range("B:B"))
Call CopyPaste(wsFrom.Range("C:C"), wsTo.Range("C:C"))
Call CopyPaste(wsFrom.Range("D:D"), wsTo.Range("D:D"))
Call CopyPaste(wsFrom.Range("E:E"), wsTo.Range("E:E"))
Call CopyPaste(wsFrom.Range("F:F"), wsTo.Range("F:F"))
Call CopyPaste(wsFrom.Range("G:G"), wsTo.Range("G:G"))
Call CopyPaste(wsFrom.Range("H:H"), wsTo.Range("H:H"))
Call CopyPaste(wsFrom.Range("I:I"), wsTo.Range("I:I"))
Call CopyPaste(wsFrom.Range("J:J"), wsTo.Range("J:J"))
Call CopyPaste(wsFrom.Range("K:K"), wsTo.Range("K:K"))
'Call CopyPaste(wsFrom.Range("L:L"), wsTo.Range("L:L"))
End Sub
Sub CopyPaste(RangePaste As Range, wsFrom As Workbook)
Dim r1LastRow As Range, r2 As Range, r1Last5Rows As Range
Set wsFrom = Workbook
Set r1LastRow = wsFrom.Range("A" & wsFrom.Rows.Count).End(xlUp) ' from this sheet range
Set r1Last5Rows = wsFrom.Range("A" & r1LastRow - 5 & ":K" & r1LastRow) 'from this sheet range
Set r2 = RangePaste.Cells(2, 1) ' paste to
r1Last5Rows.Copy
r2.PasteSpecial Paste:=xlPasteValues ' paste to
Application.CutCopyMode = xlCopy
End SubThanks in advance.
ProgramUser
