Macro to copy and paste values
Solved/Closed
SLKG
-
Apr 15, 2010 at 08:11 PM
rizvisa1
rizvisa1
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Related:
- Macro to copy and paste values
- Excel macro to copy and paste values if criteria are met ✓ - Forum - Excel
- Copy Paste Special Values Macro - Forum - Excel
- Macro to copy and paste to end of data - Guide
- Formula to copy and paste values in excel automatically - Guide
- Macro to Copy and Paste ✓ - Forum - Excel
4 replies
rizvisa1
Apr 15, 2010 at 09:00 PM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
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.
rizvisa1
Apr 16, 2010 at 02:13 PM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
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
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
Sorry I forgot I hadn't taken the original vlookups off on the first one.
Thx
SLKG
rizvisa1
Apr 16, 2010 at 04:11 PM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
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
rizvisa1
Apr 17, 2010 at 05:46 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
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
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
SLKG
Apr 17, 2010 at 05:58 AM
- 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
Now I know what to do with all the rest as well!!!
SLKG
rizvisa1
Apr 17, 2010 at 06:01 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
Apr 17, 2010 at 06:01 AM
You are welcome. best of luck,