Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Copy and paste between worksheet with total rows unknown

  Asked By: Brayden    Date: Mar 15    Category: MS Office    Views: 1339
  

I wish to copy rows from 'WeeklyExport.xls' (worksheet 'WkExport')
to 'MasterPPB.xls' (worksheet 'PivotData') in 500 row blocks. The
number of rows in WeeklyExport.xls will on average be 10,000 rows but
will always vary. I am using blocks of 500 to keep clipboard size to
a reasonable amount and would like to flush the clipboard after each
loop iteration. I'm not sure how to reference cell ranges
programmatically using VBA. The pseudo for what I'm after is along
the lines of

Set iTotalRows to total rows in WeeklyExport.xls (WkExport)
Set iLoopCount to iTotalRows / 500 (discarding any remainder)
Set iRemainder to iTotalRows mod 500
Set iLoopIdx to 1
Set iStartRow to 0
Set iEndRow to 0

Loop for x to iLoopCount
Set iStartRow to iEndRow + 1
Set iEndRow to x * 500
Copy rows 500 rows in the range iStartRow:iEndRow from
WeeklyExport.xls (WkExport) to MasterPPB.xls (PivotData)
Flush clipboard
End of loop

If Remainder <> 0
Copy iRemainder rows in the range (iEndRow+1):(iEndRow+iRemainder)
from WeeklyExport.xls (WkExport) to MasterPPB.xls (PivotData)
specifying relevant range
flush clipboard
End If

I hope the pseudo code makes the requirement clearer and apologise in
advance if you spot any typo's.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Madeeha Malik     Answered On: Mar 15

I think there are probably better ways to do what you're asking,
but I tossed together this sub which would do what you described, I
think. I'm assuming you're adding the lines from the weekly report
to the end of the MasterPPB report, so if that's NOT the case, this
would need to be adjusted. But, check this out and see if it comes
close--probably needs a bit of tweaking:

Option Explicit

Sub weeklyToMPPB()
Dim iTotalRows
Dim LastCol%
Dim Row1&
Dim Row2&
Dim wkExp As String 'variable for WeeklyExport.xls
Dim MsPPB As String 'variable for MasterPPB.xls'
Dim iRow As Integer

wkExp = "WeeklyExport.xls"
MsPPB = "MasterPPB.xls"
'<----assuming that the first row is column labels
iTotalRows = Range("A65535").End(xlUp).Row
'<---Finds last row used
LastCol% = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'<---Finds last column used

'so a given selection would be
'Range(Cells(Row1&, 1), Cells(Row2&, LastCol%)).Select
'so the loop would set the values for Row1& and Row2&

Windows(wkExp).Activate
'set the first 500 row selection
Row1& = 2
Row2& = Row1& + 499
Range(Cells(Row1&, 1), Cells(Row2&, LastCol%)).Select
Selection.Copy
Windows(MsPPB).Activate
iRow = Range("A65535").End(xlUp).Row + 1
Cells(iRow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Do While Cells(Row1&, 1).Value <> ""
Windows(wkExp).Activate
Row1& = Row2 + 1
If Row2& < iTotalRows Then
Row2& = Row1& + 499
Else
Row2& = iTotalRows
End If
Range(Cells(Row1&, 1), Cells(Row2&, LastCol%)).Select
If Cells(Row1&, 1).Value <> "" Then
Selection.Copy
Windows(MsPPB).Activate
iRow = Range("A65535").End(xlUp).Row + 1
Cells(iRow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Loop

End Sub

 
Didn't find what you were looking for? Find more on Copy and paste between worksheet with total rows unknown Or get search suggestion and latest updates.




Tagged: