+ Reply to Thread
Results 1 to 10 of 10

copy Range in Value & Paste Special form.

Hybrid View

  1. #1
    Forum Contributor
    Join Date
    01-29-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    434

    copy Range in Value & Paste Special form.

    Hello Experts' & Guru's

    We are using a VBA code in my project file for copy a Range ("A115:U215") from one sheet to another sheet (prarup-1) when A specif cell value "Y"

    part of code line in below


    With Sheets("Prarup-1").Cells(Rows.Count, 2).End(xlUp)(2).Resize(101, 21)
                .Parent.Visible = -1
                .Value = Range("A115:U215").Value
                .Cells(.Rows.Count, 8).Resize(, 6).FormulaR1C1 = "=SUM(R[" & 1 - .Rows.Count & "]C:R[-1]C)"
                On Error Resume Next
                For i = .Rows.Count To 1 Step -1
                    If .Rows(i).Text = vbNullString Then .Rows(i).EntireRow.Delete
                Next i
                On Error GoTo 0
                .Offset(, -1).Resize(, 12).Borders.LineStyle = xlContinuous
            End With
    We want in Above Range A115:I215 & N115:U215 in Value & J115 to M215 in Paste Special Form..... pls suggest me where & what i change in above code

    Thanks in advance
    Last edited by santosh226001; 09-28-2013 at 04:00 AM. Reason: edit in range 215 place with 115

  2. #2
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,199

    Re: copy Range in Value & Paste Special form.

    Hi, santosh226001,

    maybe indicate where to put the values as I doubt they all should be placed into the very same area. And you would need to epxlain if any further action is to be made for these cells as well.

    The code may look like (untested)
    With Sheets("Prarup-1").Cells(Rows.Count, 2).End(xlUp)(2).Resize(1, 9)
        .Parent.Visible = -1
        .Value = Range("A115:I115").Value
        .Offset(, 1).Value = Range("N115:U115").Value
        Range("J115:M115").Copy
        .Offset(, 2).PasteSpecial Operation:=xlPasteFormulas
    '...
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  3. #3
    Forum Contributor
    Join Date
    01-29-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    434

    Re: copy Range in Value & Paste Special form.

    hello Holge thanks for reply

    when u add this lines in my code as follwing

    With Sheets("Prarup-1").Cells(Rows.Count, 2).End(xlUp)(2).Resize(1, 9)
        .Parent.Visible = -1
        .Value = Range("A115:I115").Value
        .Offset(, 1).Value = Range("N115:U115").Value
        Range("J115:M115").Copy
        .Offset(, 2).PasteSpecial Operation:=xlPasteFormulas
                .Cells(.Rows.Count, 8).Resize(, 6).FormulaR1C1 = "=SUM(R[" & 1 - .Rows.Count & "]C:R[-1]C)"
                On Error Resume Next
                For i = .Rows.Count To 1 Step -1
                    If .Rows(i).Text = vbNullString Then .Rows(i).EntireRow.Delete
                Next i
                On Error GoTo 0
                .Offset(, -1).Resize(, 12).Borders.LineStyle = xlContinuous
            End With
    Thank this msg disply in run time

    "Run-time error'1004'
    Paste Special method of Range Class Failed


    Sir in previous code Resize(101, 21) but u suggested Resize(1, 9)


    & Sorry also when we chek we error range is A115 to U215 ... sorry sorry plss... it's not a single row data it's 100 rows data.. pls..
    Last edited by santosh226001; 09-28-2013 at 03:57 AM.

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,199

    Re: copy Range in Value & Paste Special form.

    Hi, santosh226001,

    please have a look at the Forum Rules (especially 6a: Never edit a thread or post to which others have already responded).

    Quote Originally Posted by HaHoBe
    indicate where to put the values as I doubt they all should be placed into the very same area
    Nice still to have no information about that because that may help finding a solution.

    What about a workbook for testing? I must admit that the offset is wrong (should adapt the ranges properly and not be 1 but at least 10 or even more if your areas are wider). And about the pastespecial: usually itīs the first cell which is given as destination. But somebody certainly "tailored" that code for a reason unknown to me so maybe use 2 With-Staments instead of just one to take care of both ranges for values as well as a single cell for pastespecial.

    range is A115 to U215 ... sorry sorry plss... it's not a single row data it's 100 rows data.
    Hm - 115 to 215 makes 101 rows for me.

    Ciao,
    Holger

  5. #5
    Forum Contributor
    Join Date
    01-29-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    434

    Re: copy Range in Value & Paste Special form.

    Sir i m enclosing zip file coz file size is too big in excel more then 2 mb so we make zip

    in this file when we press Y in Cell K107 then my specific range data transfer to 2 sheet as per define in code

    1 sheet Prarup-1 & 2 sheet Prarup-2

    currently my data transfer as value.. we want in Prarup1 sheet Colum K to N Paste Data with formula which is we define in Entry Sheet...

    Thanks in advance
    Attached Files Attached Files

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,199

    Re: copy Range in Value & Paste Special form.

    Hi, santosh226001,

    what formulas as there are none in the sample in the area you want excluded from values? If itīs a sum in Column N for the cells in the left of that column I would add them like the sum at the bottom of the copied range. It really would help me if the sample and the result would reflect what you are after instead of guessing what it might or not be.

    Ciao,
    Holger

  7. #7
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,199

    Re: copy Range in Value & Paste Special form.

    Hi, santosh226001,

    maybe try this alteration where I used a new variable to hold the first empty row in Sheet Prarub-1:
            With Sheets("Prarup-1")
                lngFF = .Cells(Rows.Count, 2).End(xlUp)(2).Row
                With .Cells(lngFF, "B").Resize(101, 21)
                  .Parent.Visible = -1
                  .Value = Range("A115:U215").Value
                  .Range("J115:M215").Copy
                  Sheets("Prarup-1").Cells(lngFF, "K").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                          SkipBlanks:=False, Transpose:=False
                  .Cells(.Rows.Count, 8).Resize(, 6).FormulaR1C1 = "=SUM(R[" & 1 - .Rows.Count & "]C:R[-1]C)"
    '...
    Ciao,
    Holger

  8. #8
    Forum Contributor
    Join Date
    01-29-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    434

    Re: copy Range in Value & Paste Special form.

    hi holger.. we use ur code but run time show
    comple error case without Select Case...

    we request u pls if in ur end file works then pls attach file so that we easly use copy..

    pls attach file which one u work..
    thanks

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 365 on Win11 (desktop), 2019 on Win11 (notebook)
    Posts
    8,199

    Re: copy Range in Value & Paste Special form.

    Hi, santosh226001,

    Private Sub Worksheet_Change(ByVal Target As Range)
    
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("K107,F2,a24,a44,a54,a64,a74,a84")) Is Nothing Then Exit Sub
    
    Dim r As Range, i As Long, lngFF As Long
    
    Set r = Sheets("Roadlist").Columns(2).Find(Range("F2").Value, lookat:=xlWhole)
    If r Is Nothing Then
      MsgBox "not found " & Range("F2").Value, 64
      Exit Sub
    End If
    
    ' check with Roadlist sheet that road if already enter
    If r(1, 8) = "Enter" Then
      MsgBox r & " Data already Enter..", 64
      Exit Sub
    End If
    
    Select Case Target.Address(0, 0)
    
        Case "F2"
            Sheets("Entry").Unprotect Password:="san"
            Application.EnableEvents = False
            Range("k2").Value = r(1, 2)
            If r(1, 4) <= 20 Then Rows("26:104").Hidden = True
            Application.EnableEvents = True
            Sheets("Entry").Protect Password:="san"
    '        Application.StatusBar = "This is msg box"
        
        Case "K107"
            If Target.Text = "N" Then MsgBox "You Choose No .. so NO Record Post": Exit Sub
            If MsgBox("Are you Sure You Want to Save Entered Road Record?", vbYesNo, _
                      "Confermation Yes or Not") = vbNo Then Exit Sub
            If Range("m106").Value <> "Data Ok" Then MsgBox "Data Not Correct... See Row Number 106 for Errors!!": Exit Sub
    
            ' copy prarup1
            With Sheets("Prarup-1")
                lngFF = .Cells(Rows.Count, 2).End(xlUp)(2).Row
                With .Cells(lngFF, "B").Resize(101, 21)
                  .Parent.Visible = -1
                  .Value = Range("A115:U215").Value
                  .Range("J115:M215").Copy
                  Sheets("Prarup-1").Cells(lngFF, "K").PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                          SkipBlanks:=False, Transpose:=False
                  .Cells(.Rows.Count, 8).Resize(, 6).FormulaR1C1 = "=SUM(R[" & 1 - .Rows.Count & "]C:R[-1]C)"
                  On Error Resume Next
                  For i = .Rows.Count To 1 Step -1
                      If .Rows(i).Text = vbNullString Then .Rows(i).EntireRow.Delete
                  Next i
                  On Error GoTo 0
                  .Offset(, -1).Resize(, 12).Borders.LineStyle = xlContinuous
              End With
            End With
            
            ' copy to prarup2
            With Sheets("Prarup-2").Cells(Rows.Count, 2).End(xlUp)(2).Resize(, 137)
                .Value = Range("A111:EG111").Value
                .Offset(, -1).Resize(, 52).Borders.LineStyle = xlContinuous
            End With
            Application.EnableEvents = False
            Target.Value = "N"
            ' copy workbook after each entry record
    '        ActiveWorkbook.Save
            Sheets("Entry").Unprotect Password:="san"
            Range("F2:i2,k2").ClearContents: Range("A5:j104").SpecialCells(2).ClearContents
            Sheets("Entry").Protect Password:="san"
            Sheets("Prarup-2").Visible = xlVeryHidden
    '        Range("a5").Select
            Application.EnableEvents = True
          
        Case "A24"
            Sheets("Entry").Unprotect Password:="san"
            Rows("25:44").Hidden = False
            Sheets("Entry").Protect Password:="san"
    
        Case "A44"
            Sheets("Entry").Unprotect Password:="san"
            Rows("45:54").Hidden = False
            Sheets("Entry").Protect Password:="san"
    
        Case "A54"
            Sheets("Entry").Unprotect Password:="san"
            Rows("55:64").Hidden = False
            Sheets("Entry").Protect Password:="san"
    
        Case "A64"
            Sheets("Entry").Unprotect Password:="san"
            Rows("65:74").Hidden = False
            Sheets("Entry").Protect Password:="san"
    
        Case "A74"
            Sheets("Entry").Unprotect Password:="san"
            Rows("75:84").Hidden = False
            Sheets("Entry").Protect Password:="san"
    
        Case "A84"
            Sheets("Entry").Unprotect Password:="san"
            Rows("85:104").Hidden = False
            Sheets("Entry").Protect Password:="san"
    End Select
    
    End Sub
    CIao,
    Holger
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    01-29-2012
    Location
    India
    MS-Off Ver
    Excel 2016
    Posts
    434

    Re: copy Range in Value & Paste Special form.

    hello
    We try to sheet but we face following problem..

    Data copy from entry sheet to Prarup-1 Sheet after copy coloum K to n Blank in praurp-1 sheet



    Sir currently we start a new thred which is following if u suggest a macro for follwoing thread then we solve above problem with this way.... my thread link is following..

    http://www.excelforum.com/excel-prog...her-sheet.html


    Thanks for ur reply

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Copy range using paste special
    By Shaun1978 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 06-07-2013, 09:45 AM
  2. Copy/Paste special form one sheet to another into first open colum
    By GeorgeD in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-28-2010, 05:50 PM
  3. Copy then paste special to next empty row in range
    By slearner in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-19-2010, 02:54 AM
  4. Copy range then Paste Special Macro
    By Spellbound in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 08-10-2009, 09:35 PM
  5. Copy and Paste Special Dynamic Range
    By BigH in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-20-2006, 06:20 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1