Previous Topic Next Topic

Macro to copy and paste values

Solved/Closed
SLKG - Apr 15, 2010 at 08:11 PM
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Apr 17, 2010 at 06:01 AM
Hi
I am a complete novice when it comes to macros. What I am looking for is a macro that when run looks in Cell Q7 on Sheet 1 to see what number is in that cell (number changes everytime the book is opened) then copys ranges B17:B27, I17:I27, Q17:Q27 then looks on sheet 2 for all the numbers in colum A that match Q7 on sheet 1 (there are 11 consective rows that will have matching numbers - numbers start at 700001 and go up to 700451), then will Paste Special/Values along side those rows with the matching numbers in columns B, C, D. I have tried recording the macro but can't work it out. I am using excel 2003
Any help would be greatly appreciated.
Thanks
SLKG

Update
thanks for the response rizvisa1
here is the link
https://authentification.site/files/21966057/Product_Invoice.xls

4 replies

Answer 1 / 4
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 15, 2010 at 09:00 PM
Could you please upload a sample file on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
0
Answer 2 / 4
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 16, 2010 at 02:13 PM
Could you please put up and updated file with some data. The date does not need to be true, but should be good enough for looking at the issue
0
Answer 3 / 4
SLKG
Apr 16, 2010 at 03:32 PM
https://authentification.site/files/21976194/Product_Invoice.xls
Sorry I forgot I hadn't taken the original vlookups off on the first one.
Thx
SLKG
0
Answer 4 / 4
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 16, 2010 at 04:11 PM
Based on your book, this should do it 
Sub moveData()
Dim PI_Sheet As String
Dim DC_Sheet As String
Dim invoiceLoc As Long
Dim invoice As Variant

    PI_Sheet = "Product Invoice"
    DC_Sheet = "Data Collection"

    Sheets(DC_Sheet).Select
    
    invoice = Sheets(PI_Sheet).Range("M7")
    
    invoiceLoc = 0
    On Error Resume Next
        invoiceLoc = Application.WorksheetFunction.Match(invoice, Range("A:A"), 0)
    
    On Error GoTo 0
    
    If (invoiceLoc > 0) Then
    
        With Sheets(PI_Sheet)
        
            Range("B" & invoiceLoc & ":B" & (invoiceLoc + 10)) = .Range("B17:B27").Value
            Range("C" & invoiceLoc & ":C" & (invoiceLoc + 10)) = .Range("G17:G27").Value
            Range("D" & invoiceLoc & ":D" & (invoiceLoc + 10)) = .Range("M17:M27").Value
            
        End With
    
    End If
    
End Sub
0
SLKG
Apr 16, 2010 at 09:55 PM
Thanks rizvisa1
but it simply pasted the values into columns B, C, D, on PI_Sheet not into columns B, C, D, on the DC_Sheet
SLKG
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 17, 2010 at 05:46 AM
I just tested again, For me it does correctly. Try with this modification and if it still does not work, you have to upload your test file with macro.

With Sheets(PI_Sheet)

Sheets(DC_Sheet).Range("B" & invoiceLoc & ":B" & (invoiceLoc + 10)) =Sheets(PI_Sheet).Range("B17:B27").Value

Sheets(DC_Sheet).Range("C" & invoiceLoc & ":C" & (invoiceLoc + 10)) = Sheets(PI_Sheet).Range("G17:G27").Value

Sheets(DC_Sheet).Range("D" & invoiceLoc & ":D" & (invoiceLoc + 10)) = Sheets(PI_Sheet).Range("M17:M27").Value

End With
0
SLKG Posts 6 Registration date Friday April 16, 2010 Status Member Last seen April 27, 2010
Apr 17, 2010 at 05:58 AM
It worked! Thank You So Much!!! You are a genius!! :)
Now I know what to do with all the rest as well!!!
SLKG
0
rizvisa1 Posts 4479 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 767
Apr 17, 2010 at 06:01 AM
You are welcome. best of luck,
0