Selecting & copying a variable range

[KaitMacN]

Hello

I am having trouble coming up with the proper code to select a variable range.
The situation is that I have courses that have a various numbers of students emailed to me in a select format and I want to be able copy that information into a new sheet and have the information re-organized and pasted into a new master list.

Since I was unable to sort out the variable codes I have been selecting many more cells than necessary with a very messy code!

I have included a modified version of the file that will hopefully make sense

Thanks!

[tigeravatar]

KaitMacN,

Attached is a modified version of your Unit workbook. The Paste macro has been deleted and the object assigned to the Paste macro has been reassigned to the Copy macro. In the 'Entry' sheet, rows 55 to 105 have been deleted including the object that was assigned to the Copy macro. All you need to do is click the object in the 'Master' sheet to run the new Copy macro to pull in information as desired. The Copy macro has been updated to the following:

Sub Copy_()
    
    'E = Entry, M = Master, rc = RowCount, nr = NextRow
    Static wsE As Worksheet: Set wsE = Sheets("Entry")
    Static wsM As Worksheet: Set wsM = Sheets("Master")
    Static Erc As Long:      Erc = wsE.Cells(Rows.Count, "A").End(xlUp).Row - 9
    Static Mnr As Long:      Mnr = wsM.Cells(Rows.Count, "A").End(xlUp).Row + 1
    
    wsM.Cells(Mnr, "A").Resize(Erc).Value = wsE.Range("C10").Resize(Erc).Value    'Last name
    wsM.Cells(Mnr, "B").Resize(Erc).Value = wsE.Range("B10").Resize(Erc).Value    'Rank
    wsM.Cells(Mnr, "C").Resize(Erc).Value = wsE.Range("A10").Resize(Erc).Value    'Serial Number
    wsM.Cells(Mnr, "D").Resize(Erc).Value = wsE.Range("B5").Value                 'Start Date
    wsM.Cells(Mnr, "E").Resize(Erc).Value = wsE.Range("B6").Value                 'End Date
    wsM.Cells(Mnr, "F").Resize(Erc).Value = wsE.Range("N10").Resize(Erc).Value    'Card Number
    wsM.Cells(Mnr, "G").Resize(Erc).Value = wsE.Range("B1").Value                 'Course Name
    wsM.Cells(Mnr, "H").Resize(Erc).Value = wsE.Range("B2").Value                 'Session

End Sub

Hope that helps,
~tigeravatar

[KaitMacN]

Thanks so much! That is a much more elegant and effective code AND it actually does what I wanted unlike mine :P