+ Reply to Thread
Results 1 to 10 of 10

Effective way to copy data

Hybrid View

  1. #1
    Registered User
    Join Date
    06-03-2014
    MS-Off Ver
    2007
    Posts
    87

    Effective way to copy data

    Hi, Good morning,

    If it is possible, I'm looking to do a code that will do the following steps :

    -Open a Workbook (data)
    -Search for the year 2013 in all the cells
    -Go two rows above it and two columns to the left and select the range to the lowest row and last column

    -Paste value in my other workbook
    -Repeat (loop) for all the worksheets of the data workbook

    I need your ideas concerning the two steps in bold. I know you can do the following concerning the value "2013" :

    Dim rg as Range
    For each rg in Worksheet("sheet1")
    If rg.value = "2013" Then rg.Offset(-2,-2)
    However, I have no idea how to select and copy the whole area. The thing is that it is not a defined area and it can moved through time and change except for the years.

    Thank you

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Effective way to copy data

    Perhaps something like this.
    Dim rg as Range
    Dim rgCopy As Range
    
        For each rg in Worksheet("sheet1").UsedRange
            If rg.value = "2013" Then 
                Set rngCopy = Range(rg.Offset(-2,-2), rng.Offset(-2,-2).End(xlDown).End(xlToRight))
            End If
        Next rg
    By the way, will 2013 appear multiple times in each worksheet, or just the once?
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-03-2014
    MS-Off Ver
    2007
    Posts
    87

    Re: Effective way to copy data

    It will appear multiple times but I may use another value to make it more convenient.

    Thank you Norie. Your answers are always fast and to the point.

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Effective way to copy data

    No problem.

    By the way, if you have a lot of data you might want to consider using Find/FindNext instead of looping.

  5. #5
    Registered User
    Join Date
    06-03-2014
    MS-Off Ver
    2007
    Posts
    87

    Re: Effective way to copy data

    Sounds interesting.

    Does it reduce the size of the code and the speed of execution ?

  6. #6
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Effective way to copy data

    It should speed up execution, but might require a bit more code.

  7. #7
    Registered User
    Join Date
    06-03-2014
    MS-Off Ver
    2007
    Posts
    87

    Re: Effective way to copy data

    Ok, I might take a look into it if my first idea on how to build this code fail.

    Thank you Norie.

  8. #8
    Registered User
    Join Date
    06-03-2014
    MS-Off Ver
    2007
    Posts
    87

    Re: Effective way to copy data

    Okay, I need some backup help. I tried to work with xlDown. However, I need to repeat end(xldown).end(xldown).end(xldown).... to get to the end of my data because of the blanks. So, instead, I'm trying to make a count to the bottom row and use it to define my range to copy. But, I just can't seem to define the right syntax.

    Sub codeX()
        Dim wbTarget As Workbook, rg As Range, rgCopy As Range, wbthis As Workbook, ws As Worksheet, lastrow As Long
        Set wbTarget = Workbooks("BPCdata.xlsm")
        Set wbthis = Workbooks("Dashboard.xlsm")
            
        wbthis.Worksheets(1).Cells.Clear
            
            wbTarget.Activate
            For Each rg In Worksheets(2).UsedRange
                If rg.Value = "BIZTYPE: 123" Then
                    lastrow = Range("A" & Rows.Count).End(xlUp).Row
                    Set rngCopy = Range(rg.Offset(-3, 0), rg.Offset(-3, 0).lastrow.End(xlToRight))
                    rngCopy.Copy
                    wbthis.Worksheets("Donnees").Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                End If
            Next rg
            
    End Sub
    My problems are from the two code in bold font.

  9. #9
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Effective way to copy data

    You can try this, though if there are a lot of blank areas in your data then another approach might be needed.
    Dim rg as Range
    Dim rgCopy As Range
    Dim LastRow As Long
    Dim LastCol As Long
    
        For each rg in Worksheet("sheet1").UsedRange
            If rg.value = "2013" Then 
    
                LastRow = Cells(Rows.Count, rg.Offset(,-2).Column).End(xlUp).Row
                LastCol = Cells(rg.Offset(-2).Row, Columns.Count).End(xlToLeft).Column
    
                Set rngCopy = Range(rg.Offset(-2,-2), Cells(LastRow, LastCol))
            End If
        Next rg

  10. #10
    Registered User
    Join Date
    06-03-2014
    MS-Off Ver
    2007
    Posts
    87

    Re: Effective way to copy data

    Great! Now, I'll just need to work on the format and details. Thank you again Norie.

    Edit : I had to change End(xlToLeft).Column to End(xlToRight).Column to count all the columns
    Last edited by SIMBAtheCAT; 06-23-2014 at 01:05 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Hello! Itries2help but, I needs more knowledge to be effective.
    By itries2help in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 04-15-2014, 07:14 AM
  2. more effective script
    By Evolta in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 12-05-2013, 09:13 AM
  3. [SOLVED] Effective Data structure required
    By thiaga in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-08-2006, 04:15 PM
  4. More effective code
    By Roman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2005, 12:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1