PDA

View Full Version : Cut/Paste data, code only works on first 4 rows



radka.silva
04-29-2015, 08:48 AM
Hello,

I am cutting some data from one spreadsheet and pasting in another spreadsheet and would like to make this repetative task more automated. The code that I have so far is working, but only cuts and pastes the first four rows; what am I missing? Here is my code:

Sub CopyAUM()
Dim wbPortfolio As Workbook, wsMaster As Workbook, wb As Workbook
For Each wb In Application.Workbooks
If wb.Name Like "4-15 AUM_LCV*" Then
Set wbAUM = wb
ElseIf wb.Name Like "DOWNLOAD*" Then
Set wsDownload = wb
End If
Next
lastRow = Range("A" & Rows.Count).End(xlUp).Row
Call wsDownload.ActiveSheet.Range("A2:H" & lastRow).Copy(wbAUM.ActiveSheet.Range("A6"))
End Sub

I have to note that I have spliced this code from my other iqueries on this forum without a full understanding of how it works. :(
I am working with Excel 2010.

Thanks!

radka.silva
04-29-2015, 10:58 AM
OK, I think I may have actually figured out why it's copying only 4 rows. I believe my 'last row' reference is actually reading from the spreadsheet that I want to copy into, not the one I want to copy from. :)

So I updated the code to this:

Sub CopyAUM()
Dim wbPortfolio As Workbook, wsMaster As Workbook, wb As Workbook
For Each wb In Application.Workbooks
If wb.Name Like "4-15 AUM_LCV*" Then
Set wbAUM = wb
ElseIf wb.Name Like "DOWNLOAD*" Then
Set wsDownload = wb
End If
Next
Call wsDownload.ActiveSheet.Range("A2:H100").Copy(wbAUM.ActiveSheet.Range("A6"))
End Sub

This will work for now, but if my data goes over 100 rows, I would have to update the macro. Can anybody help me reference the last row in the spreadsheet I am copying from?

Thanks,