1. 

   sanket.katkar
   

   Member
   Joined: Aug '12
   Posts: 8

   Actually, I am trying to make a tool in excel which will perform following task:
   There are two work book Customer and master workbook .
   In master workbook there are 2 sheets, namely Main, MNG.
   There is a button on the sheet called Main.
   Clicking that button it should import the data from customer workbook to the MNG sheet of the master workbook. Importing should be such that it should only copy from row 67 to end, from customer sheet and paste in MNG sheet from row 2.

   Please help me out...
   Thanks in advance.

   Posted 9 months ago #
2. 

   Luke M
   

   Excel Ninja
   Joined: Nov '10
   Posts: 4,356

   Will need you to make some modifications to fit your exact situation, but this should get you started.
   

   Sub CopyData()
   Dim SourceBook As String
   Dim SourcePath As String
   Dim LastRow As Integer
   
   'Change these as needed
   SourcePath = "C:\My Documents\"
   SourceBook = "Customers.xls"
   
   Application.ScreenUpdating = True
   Workbooks.Open SourcePath & SourceBook
   
   'Change this to the correct source worksheet
   With ActiveWorkbook.Worksheets("Source Sheet")
       'Find the last row of data
       LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
       'Copy the information
       .Range("67:" & LastRow).Copy ThisWorkbook.Worksheets("MNG").Range("A2")
   End With
   
   'Close the customer workbook
   ActiveWorkbook.Close
   Application.ScreenUpdating = True
   
   End Sub

   Posted 9 months ago #
3. 

   sanket.katkar
   

   Member
   Joined: Aug '12
   Posts: 8

   Thanks Luke it works but there is some problem..
   as i am copying the data from B67 but i have to paste it in A2. its pasting in B2 not in the required location

   With ActiveWorkbook.Worksheets("Source Sheet")
   'Find the last row of data
   LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
   'Copy the information
   .Range("67:" & LastRow).Copy ThisWorkbook.Worksheets("MNG").Range("A2")
   End With

   Thanks in advance.

   Posted 9 months ago #
4. 

   Luke M
   

   Excel Ninja
   Joined: Nov '10
   Posts: 4,356

   We're copying the entire row, so we can't have the destination start in a column other than A. (aka, we copied all columns, we have to paste all columns).
   If you only need some of the columns (say, A:Z), could change that block of code to something like this:
   

   With ActiveWorkbook.Worksheets("Source Sheet")
   'Find the last row of data
   LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
   'Copy the information
   .Range("67:" & LastRow).Copy ThisWorkbook.Worksheets("MNG").Range("A2")
   End With
   With ActiveWorkbook.Worksheets("Source Sheet")
   'Find the last row of data
   LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
   'Copy the information
   .Range("A67:Z" & LastRow).Copy ThisWorkbook.Worksheets("MNG").Range("B2")
   End With

   Posted 9 months ago #
5. 

   sanket.katkar
   

   Member
   Joined: Aug '12
   Posts: 8

   Thanks a lot LUKE,
   u r truely a champ in Excel

   Posted 9 months ago #
6. 

   Luke M
   

   Excel Ninja
   Joined: Nov '10
   Posts: 4,356

   You're very welcome sanket.katkar, thanks for the kind words. =)

   Posted 9 months ago #

RSS feed for this topic

Reply

You must log in to post.

Welcome to Chandoo.org. New here?

Thank you so much for visiting us & taking first step to become awesome in Excel. Please use below links to navigate our site.

Learn Excel - Topics » Excel for Beginners - Tutorials » Excel Formula Examples » Array Formulas » Excel Charts » Pivot Tables » Project Management using Excel » Excel Dashboards » Excel VBA » Advanced Excel

Our Products Full Training Programs » Excel School » Excel Dashboard Classes » Excel VBA Classes » Excel Financial Modeling » Excel for PMs Crash Courses » Excel Formula Course » Excel Dashboards Course Downloads » Excel PM Templates » Excel Formula E-book

About us » About Chandoo.org » Welcome » Best of Chandoo.org » FREE Excel Downloads » Contact Details » Excel Forums