[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

How can I find data and copy from one worksheet to another using a macro?

Posted on 2012-08-20
6
Medium Priority
?
421 Views
Last Modified: 2012-08-21
I have the workings of a macro which has worked for some parts of the code. But when I try to look at "Sheet2" to find a source of data using "ActiveCell" I wear egg on my face. Can any one help me.
The attached file shows my workings at this time.
I would like to run the the macro and select a range of cells in sheet2 then look at sheet1 and copy some of the columns of data back to sheet2. Then repeat the process until all the selection has been completed or an empty cell or can not find data occurence where the message Box appears revealing problem.
CopyDataTest--B.xlsm
0
Comment
Question by:user2073
  • 3
  • 2
6 Comments
 
LVL 8

Expert Comment

by:Elton Pascua
ID: 38314729
I appreciate that you tried to explain the end goal in detail but I'm somehow confused with the process.

Can you show a sample completed item, say once it completes for one item, how would it look like?
0
 

Author Comment

by:user2073
ID: 38314808
I have included a sample worksheet as requested. Hoping it helps to clear any issues.

THANKS
Copy-of-CopyDataTest--B1.xlsm
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 38315003
Hi

Try this

Sub Find_N_Move()
    
    Dim ka, k, i As Long, n As Long, c As Long
    Dim r As Range, j As Long
    
    Set r = Sheets("Sheet2").Range("c5:c21")
    ka = r.Resize(, 5).Value2
    
    k = Sheets("Sheet1").Range("a1:g17")
    
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(ka, 1)
            If Len(ka(i, 1)) Then
                .Item(ka(i, 1)) = i
            End If
        Next
        For i = 1 To UBound(k, 1)
            If .exists(k(i, 1)) Then
                n = .Item(k(i, 1)): j = 1
                For c = 3 To UBound(k, 2)
                    Select Case c
                        Case 3 To 5, 7 'col c,d,e,g
                            j = j + 1
                            ka(n, j) = k(i, c)
                    End Select
                Next
            End If
        Next
        r.Resize(, 5) = ka
        MsgBox "Done"
    End With
    
End Sub

Open in new window


Kris
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:user2073
ID: 38315295
Kris
Your solution works well, Thanks

The only change I would is to be able select any part of the range on "Sheet2" then run the macro for the selection only can you alter the code to achieve this request. All the rest is fine.
0
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 2000 total points
ID: 38315318
Hi

Replace

Set r = Sheets("Sheet2").Range("c5:c21")

Open in new window


with

Set r = Selection

Open in new window

0
 

Author Closing Comment

by:user2073
ID: 38315394
Great solution thanks for the help. I appreciate the abilities and dedication you provide.

Many Thanks
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

872 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