Consulting

Results 1 to 5 of 5

Thread: Copy Range Until Last Cell With Data

  1. #1
    VBAX Regular
    Joined
    Mar 2017
    Posts
    55
    Location

    Copy Range Until Last Cell With Data

    Hi, I have some code below that consolidates my workbooks and worksheets. The line below in bold (myWS.Range("A2:Z100").Copy) selects a range from each worksheet and pastes it in the master file. I would like this to copy until the last cell of data since the rows are different with each file. Currently it's defined at row 100.

    Any help with be apprecited. Thank you.


    Option Explicit
    
    Sub ConsolidateDataKarpExprs()
    '
    Dim MyPath As String
    Dim SumPath As String
    Dim MyName As String
    Dim SumName As String
    Dim MyTemplate As String
    Dim SumTemplate As String
    Dim myWS As Worksheet
    Dim sumWS As Worksheet
    Dim Last_Row As Long
    
    'Define folders and filenames
    MyPath = "C:\Users\Kate\Desktop\FedEx Reporting\Karpathian Express Test\"
    SumPath = "C:\Users\Kate\Desktop\FedEx Reporting\"
    MyTemplate = "*.xls"  'Set the template.
    SumTemplate = "Karpathian Express Master.xlsm"
    
    'Open the template file and get the Worksheet to put the data into
    SumName = Dir(SumPath & SumTemplate)
    Workbooks.Open SumPath & SumName
    
    Set sumWS = ActiveWorkbook.Worksheets("Linehaul Trips")
    'Open each source file, copying the data from each into the template file
    MyName = Dir(MyPath & MyTemplate)    'Retrieve the first file
    Do While MyName <> ""
        'Open the source file and get the worksheet with the data we want.
        Workbooks.Open MyPath & MyName
        Set myWS = ActiveWorkbook.Worksheets("Linehaul Trips")
        'Copy the data from the source and paste at the end of Summary sheet
        myWS.Range("A2:Z100").Copy
        sumWS.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        'Close the current sourcefile and get the next
        Workbooks(MyName).Close SaveChanges:=False        'close
        MyName = Dir                    'Get next file
    Loop
    
    Set sumWS = ActiveWorkbook.Worksheets("Other Settlements Adjustments")
    'Open each source file, copying the data from each into the template file
    MyName = Dir(MyPath & MyTemplate)    'Retrieve the first file
    Do While MyName <> ""
        'Open the source file and get the worksheet with the data we want.
        Workbooks.Open MyPath & MyName
        Set myWS = ActiveWorkbook.Worksheets("Other Settlements Adjustments")
        'Copy the data from the source and paste at the end of Summary sheet
        myWS.Range("A2:Z106").Copy
        sumWS.Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
        'Close the current sourcefile and get the next
        Workbooks(MyName).Close SaveChanges:=False        'close
        MyName = Dir                    'Get next file
    Loop
    
    
    
    'Now all sourcefiles are copied into the Template file. Close and save it
    'Workbooks(SumName).Close SaveChanges:=True
    End Sub
    Last edited by Paul_Hossler; 03-26-2017 at 06:54 PM.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Please use CODE tags with your macro(s) between them

    Click the [#] icon and put your code between the [CODE] ..... [/CODE] tags

    It makes it easier to read and to see your code
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Not tested (or even syntax checked) but instead of ...

    myWS.Range("A2:Z100").Copy

    ... try something like this

    Set rTemp = Intersect (myWS.Range("A:Z"), myWS.UsedRange)
    Set rTemp = rTemp.Cells(2,1).resize (rTemp.Rows.Count-1, rTemp.Columns.Count).Copy
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Regular
    Joined
    Mar 2017
    Posts
    55
    Location
    Thanks Paul. What would I define rTemp as?

    (and noted regarding posting macro between code tags, will do so in future posts)

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by sharc316 View Post
    Thanks Paul. What would I define rTemp as?
    Dim rTemp As Range

    There's probably a way to do it all on one line and/or without Dim-ing a temp Range variable, but that (wordy) style is just the way I do it

    So if you've used a WS and the last/lowest cell was AC123

    1. The first statement makes rTemp = the intersection of A1:AC123 and A:Z, or = A1:Z123 (which has 123 rows and 26 columns)

    2. The next statement resizes the above rTemp starting in A2 for 122 rows and 26 columns or A2:Z123

    BTW, if there is no data past (say) row 50 in columns A:Z, it'll still copy the empty rows

    If that's a problem, there are more 'accurate' but more complicated ways to just copy something like A2:A36, B2:B45, C2:C19, ...., Z2:Z50
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •