Select COPY range based upon MAX in Column

[CordingBags]

I am trying to write / find a MACRO to copy a range from the Current Sheet to another sheet, FIXTURES
Range is based upon the MAX value in column F
Then range will extend from column D to column N
Copy range commences at D1 and may go as far as N318, but should stop at N???, based on MAX value in column F.
Plus one row.
ie if MAX is row 107 then copy area D1:N108

Any help appreciated
Thanks
Paul

 

[kevin9999]

You haven't said where exactly on the FIXTURES sheet you want the values copied to, or whether the "current sheet" contains constant values or formulas. It's better to use the actual sheet name explicitly rather than use reference to the current sheet. Having said that, try the following on a copy of your workbook as a starter - we can refine you requirements from here.

Option Explicit
Sub CordingBags()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = ActiveSheet           '<~~ *** This needs improving, better to use actual sheet name ***
    Set ws2 = Worksheets("FIXTURES")
    
    ws1.Range("D1:N" & ws1.Range("F:F").Find(Application.Max(ws1.Range("F:F"))).Row + 1).Copy _
    ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
End Sub

 

[CordingBags]

Hi Kevin


Many thanks for your efforts. I have tried this MACRO.

It throws up a debug Runtime Error '91'
Object variable or With block variable not set Error

Clicking debug shows: ws1.Range("D1:N" & ws1.Range("F:F").Find(Application.Max(ws1.Range("F:F"))).Row + 1).Copy _
ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)

As the error.


Answering your questions:
1. Paste location is same as copy location. D1:N??? on "FIXTURES" sheet
2. Reason I need to do this is because the program that this is parsed to will not accept cells with formulas or conditional formatting etc. Therefore paste should be values only. But thought I could probably achieve this myself given assistance with the copy range.
3. Workbook contains about 15 tabs, each sheet very similar to the others in terms of formula, formats, data validation etc. Major difference depth of data in column F which holds the date of a fixture.
4. I had therefore hoped to find a MACRO that would apply to whichever sheet I happened to be on at the time.
5. Could use multiple macros if that is easier / works. 15 similar MACRO each referring to a different sheet. Sheet names include, MENS EVE LGE 1, MENS EVE LGE 2, LADIES AFT 2. Hopefully I have enough knowledge to amend a template MACRO if they do need replicating.

6. Doing this "manually" I highlight cells D1:N?? (dependent upon data in column F) then copy paste values only onto the FIXTURES sheet. Was just hoping that a MACRO would "simplify" the process also means shallower learning curve for anyone else operating the workbook, just run the macro. Which I could button on each sheet. No requirement to know about copy paste special etc. Less chance perhaps of not going far enough or too far down the columns when selecting copy range.

Again Many Thanks for your Efforts

Cheers

Paul

 

[kevin9999]

OK, try the following (UNTESTED)

Option Explicit
Sub CordingBags()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = ActiveSheet
    Set ws2 = Worksheets("FIXTURES")
    
    ws1.Range("D1:N" & ws1.Range("F:F").Find(Application.Max(ws1.Range("F:F"))).Row + 1).Copy
    ws2.Range("D1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

 

[CordingBags]

OK, try the following (UNTESTED)

Option Explicit
Sub CordingBags()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = ActiveSheet
    Set ws2 = Worksheets("FIXTURES")
   
    ws1.Range("D1:N" & ws1.Range("F:F").Find(Application.Max(ws1.Range("F:F"))).Row + 1).Copy
    ws2.Range("D1").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
End Sub

Morning Kevin
Again Many thanks for your efforts,
getting the same error 91 message, seems to be a problem selecting the range as the debug line is
ws1.Range("D1:N" & ws1.Range("F:F").Find(Application.Max(ws1.Range("F:F"))).Row + 1).Copy
Would it be better to try putting the sheet name in instead of ActiveSheet,
Have tried changing but don't think I know quite enough about to do.
this is what I tried but still gets debug at same point.
ws1.Range("D1:N" & Sheets("OD LGE").Range("F:F").Find(Application.Max(Sheets("OD LGE").Range("F:F"))).Row + 1).Copy
Thanks for your help
Cheers

 

[kevin9999]

There's not much more I can suggest without seeing your actual worksheet(s). Can you provide a sample of your sheet using the XL2BB add in, or alternatively, share your workbook via Dropbox, Google Drive or similar file sharing platform. The code works fine on a mock-up I created based on your description.

 

[CordingBags]

Have tried setting up a folder share Google Sheets: Sign-in
Which hopefully lets you access

Will try XL2BBadd in bit later in the morning just off to hospital.
Thanks
Paul

 

[kevin9999]

Tried opening the link - "Access Denied"...

 

[kevin9999]

OK got it now. The following code worked on the file you shared:

Option Explicit
Sub CordingBags_V2()
    Dim ws1 As Worksheet, ws2 As Worksheet, i As Long
    Set ws1 = ActiveSheet
    Set ws2 = Worksheets("FIXTURES")
    i = Application.Max(ws1.Range("F:F"))
    If i > 0 Then
        ws1.Range("D1:N" & i + 1).Copy
        ws2.Range("D1").PasteSpecial xlPasteValues
        Application.CutCopyMode = False
    End If
End Sub

 

[CordingBags]

Many Thanks Kevin.

This certainly transfers the data but doesn't stop at the last entry with a value in column F.
Row 119 plus 1 in the workbook I sent.
Copy Range is going down to Row 318.

Appreciate your efforts.
Thanks
Paul