Solved

Vba macro to copy the data from one sheet to another

Posted on 2012-04-13
6
468 Views
Last Modified: 2012-06-27
Dear experts,

I have two files, which have one sheet in each which are relevant for this purpose.

In the sheet in the first file (source file), there is data in either three or four columns (vba to be kept flexible to fix the range). The number of rows of data is in the sheet in the first file is 200k. The macro should look for the last record and then stop at the stage of copying and pasting the date in to the sheet in the second file.

The data in the sheet in the first file should be copied by macro in sets of 20k records (three or four columns and 20k rows) and then copied in to the sheet in the second file.  this should be done until the last record from the sheet in the first file is copied into the sheet in the second file.

This macro should be tailorable to other files also. Which mean it should ask for the first file and its sheet name and the range where the data exists and which needs to be copied. the Vba should ask for number rows to be copied at a time. Similarly the macro should ask for the second file the sheet and start of the range where the pasting fo the values should start.

I have ms xl 2007 and xp os.

Request your help in this regard.

Thank you
0
Comment
Question by:Excellearner
  • 3
  • 3
6 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
1/ so does WorkBook2 (excel files are called workbooks) exist or does it need to be created ?

2/ If created, do you need column widths to match ?

3/ Normally the macro would be stored within Book1, but it seems you may need to have a 'master' workbook ?

4/ How do you want users to select the file name and output location.?  Use the std excel methods ?  Note that the macro will need to open each workbook to get the list of sheets and output location. This may take a while.

5/ I presume you want a macro to do this as copying 200k rows is causing problems ? otherwise, a macro doesnt do much.
0
 

Author Comment

by:Excellearner
Comment Utility
Robberbaron,

Thank you for your comment, below are my replies. I tried to be comprehensive with my request, but i guess there is always a gap.


1/ so does WorkBook2 (excel files are called workbooks) exist or does it need to be created ?
Reply: both the workbooks will be kept open. The column ranges in both the sheets may or may not he sequential. In the current case the column range to be copied is B:J (say). The data starts in the source file in the row 5 but the file where the data needs to be copied starts from row 6 in column B:J. Hence the macro must be able to handle this.

2/ If created, do you need column widths to match ?
Reply: answered above

3/ Normally the macro would be stored within Book1, but it seems you may need to have a 'master' workbook ?
Reply;The macro will be destination file. The file where the data is copied into. I will activate the macro from the workbook in to which the data needs to be copied.

4/ How do you want users to select the file name and output location.?  Use the std excel methods ?  Note that the macro will need to open each workbook to get the list of sheets and output location. This may take a while.

Reply Kindly provide text (highlighted in different colour) for the script where file path/details need to be changed.

5/ I presume you want a macro to do this as copying 200k rows is causing problems ? otherwise, a macro doesnt do much.

Reply: the destination file has complex formula in a couple of sheets, hence it hangs when i copy all the 200k rows of data at once. I would prefer the data to be copied in small lots.
At this stage the formulae calculation option will be set to manual in both the sheets to save memory.

Kindly help.

Thank you
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
Comment Utility
this is where i was prior to your reply.....

I'll try updating on the assumtointhat both source and destination are already open.

try this in a master workbook.
Sub CopyData()
    '
    ' Robberbaron @ EE   April 2012
    '
    Dim ok As Boolean
    Dim wbSource As Workbook, rngSourceStart As Range, rngSource As Range
    Dim wbDest As Workbook, rngDestStart As Range, rngDest As Range
    
    'get the source
    Set qq = Application.Dialogs(xlDialogOpen)
    
    ok = OpenSingleFile("select Source workbook", "")
    'Application.Dialogs(xlDialogOpen).Show()
    'or use http://msdn.microsoft.com/en-us/library/aa432103%28v=office.12%29.aspx
    If ok Then
        'was openned by user. now active
        Set wbSource = ActiveWorkbook
               
        'pick range
        Set rngSourceStart = GetRange("Select the first row of input data (all required columns)")
        If rngSourceStart Is Nothing Then
            ok = False
         Else
            rngSourceStart.Activate
            Set rngSource = ActiveSheet.Range(rngSourceStart, rngSourceStart.End(xlDown))

        End If
        
     Else
        Exit Sub
    End If

    'get chunk size
    chunk = Application.InputBox("Enter copy block size ", Type:=1)
    
    'get the destination
    'ok = Application.Dialogs(xlDialogOpen).Show
    ok = OpenSingleFile("Select destination workbook", "")
    If ok Then
        'was openned by user. now active
        Set wbDest = ActiveWorkbook
               
        'pick range
        Set rngDestStart = GetRange("Select the start of output area on desired sheet")


        'get end
    End If

    If ok Then
        'copy from source to dest in chunks
        Dim lCount As Long
        Dim rngCopy As Range, rngOut As Range
        lCount = rngSource.Rows.Count
        
        If chunk > lCount Then chunk = lCount
        
        Set rngOut = rngDestStart
        For lx = 1 To lCount Step chunk
            'copy from source to dest in chunks
            Set rngCopy = rngSource.Rows(lx).Resize(chunk)
            
            'rngCopy.Worksheet.Activate
            
            'do the copy
            rngCopy.Copy
            
            rngOut.Worksheet.Activate
            rngOut.PasteSpecial xlPasteAll, xlPasteSpecialOperationNone
            
            'set next output
            Set rngOut = rngOut.Offset(chunk)
        Next lx
        
        'copy balance
        chunk = lCount - lx
        If chunk > 0 Then
            'copy from source to dest in chunks
            Set rngCopy = rngSource.Rows(lx).Resize(chunk)

            'do the copy
            rngCopy.Copy (rngOut)
        End If
        
        rngDestStart.Worksheet.Activate
        rngDestStart.Select
    End If

End Sub


Function GetRange(SMessage)
    'returns nothing or range object
    Dim rng As Range
    On Error Resume Next
    Set rng = Application.InputBox(SMessage, Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then
        'MsgBox "you hit cancel"
        
    End If
    Set GetRange = rng
    
    
End Function

Function OpenSingleFile(sPrompt, sType) As Boolean


    'Declare a variable as a FileDialog object.
    Dim fd As FileDialog
    'Create a FileDialog object as a File Picker dialog box.
    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    'Declare a variable to contain the path
    'of each selected item. Even though the path is aString,
    'the variable must be a Variant because For Each...Next
    'routines only work with Variants and Objects.
    Dim vrtSelectedItem As Variant

    'Use a With...End With block to reference the FileDialog object.
    With fd
        
        .AllowMultiSelect = False
        .Title = sPrompt
        'Use the Show method to display the File Picker dialog box and return the user's action.
        'The user pressed the button.
        If .Show = -1 Then

            'Step through each string in the FileDialogSelectedItems collection.
            For Each vrtSelectedItem In .SelectedItems

                'vrtSelectedItem is aString that contains the path of each selected item.
                'You can use any file I/O functions that you want to work with this path.
                'This example displays the path in a message box.
                'MsgBox "The path is: " & vrtSelectedItem
                Workbooks.Open (vrtSelectedItem)
                OpenSingleFile = True
            Next vrtSelectedItem
        'The user pressed Cancel.
        Else
            OpenSingleFile = False
        End If
    End With

    'Set the object variable to Nothing.
    Set fd = Nothing

End Function

Open in new window

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
Comment Utility
updated.  Shouldnt matter where the macro is stored as it asks for both source and destination
Sub CopyData()
    '
    ' Robberbaron @ EE   April 2012
    '  v2
    Dim ok As Boolean
    Dim wbSource As Workbook, rngSourceStart As Range, rngSource As Range
    Dim wbDest As Workbook, rngDestStart As Range, rngDest As Range
    
    'get the source
    Set qq = Application.Dialogs(xlDialogOpen)
    
    'ok = OpenSingleFile("select Source workbook", "")

    'or use http://msdn.microsoft.com/en-us/library/aa432103%28v=office.12%29.aspx
    ok = True
    If ok Then
        'was openned by user. now active
        Set wbSource = ActiveWorkbook
               
        'pick range
        Set rngSourceStart = GetRange("Select the first row of input data (all required columns)")
        If rngSourceStart Is Nothing Then
            ok = False
         Else
            rngSourceStart.Activate
            Set rngSource = ActiveSheet.Range(rngSourceStart, rngSourceStart.End(xlDown))

        End If
        
     Else
        Exit Sub
    End If

    'get chunk size
    chunk = Application.InputBox("Enter copy block size ", Type:=1)
    
    'get the destination
    'ok = Application.Dialogs(xlDialogOpen).Show
    'ok = OpenSingleFile("Select destination workbook", "")
    
    If ok Then
        'pick output range
        Set rngDestStart = GetRange("Select the start of output area on desired sheet")

    End If

    If ok Then
        'copy from source to dest in chunks
        Dim lCount As Long
        Dim rngCopy As Range, rngOut As Range
        lCount = rngSource.Rows.Count
        
        If chunk > lCount Then chunk = lCount
        
        Set rngOut = rngDestStart
        For lx = 1 To lCount Step chunk
            'copy from source to dest in chunks
            Set rngCopy = rngSource.Rows(lx).Resize(chunk)
            
            'rngCopy.Worksheet.Activate
            
            'do the copy
            rngCopy.Copy
            
            rngOut.Worksheet.Activate
            rngOut.PasteSpecial xlPasteAll, xlPasteSpecialOperationNone
            
            'set next output
            Set rngOut = rngOut.Offset(chunk)
        Next lx
        
        'copy balance
        chunk = lCount - lx
        If chunk > 0 Then
            'copy from source to dest in chunks
            Set rngCopy = rngSource.Rows(lx).Resize(chunk)

            'do the copy
            rngCopy.Copy (rngOut)
        End If
        
        rngDestStart.Worksheet.Activate
        rngDestStart.Activate
    End If

End Sub


Function GetRange(SMessage)
    'returns nothing or range object
    Dim rng As Range
    On Error Resume Next
    Set rng = Application.InputBox(SMessage, Type:=8)
    On Error GoTo 0
    If rng Is Nothing Then
        'MsgBox "you hit cancel"
        
    End If
    Set GetRange = rng
    
    
End Function

Open in new window

0
 

Author Comment

by:Excellearner
Comment Utility
Robberbaron,

thank you for the macro.

The macro asks me the range of columns and start to start the selection and copy process, I am fine with it.

The macro asks the set of rows for copying range, I am fine with it.

I have one issue iwht the window which asks for destination sheet. At this stage, while the cursor is in the macro message window. It does not allow me to go ot the destination sheet window and metnion the start of the range where the pasting shall start.

Thank you
0
 

Author Closing Comment

by:Excellearner
Comment Utility
Hi, the VBa did the job when it had to perform on the same sheet.

When i had to choose two different files, then I could not choose the destination sheet.

Thankyou for your help
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now