CODEWITHSUNDEEP
excelvbacopy-paste
MzBarcaaa
MzBarcaaa

Reputation: 45

VBA - copying to other sheets

I have this code, by a responder who helped me to define my needs yesterday - but there somethings i want to change, but my vba skills are very low and dont know how and where to modify the code. I want it do 2 Things.

  1. Right know it transferes data, i want it to copy it, over with the values that are calculated in the cells. I have some cells, where i have some formulas and it dosent follows with it. I just want the calculated value over. I dont know if i can use xlPasteValues somewhere to get what i want?

    1. The second thing that i want is, when copying over, i want to be on top and the previous copies move Down, so the latest copy always are in the top.

Thank you before handed :)

Option Explicit

Sub Copypastemeddata()

    Dim wb As Workbook
    Dim ws As Worksheet
    Dim sourceCell As Range
    Dim targetSheet As Worksheet

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Opgørsel")

    Set sourceCell = ws.Range("D3")  'Cell with sheet names for copying to

    With ws

          Set targetSheet = wb.Worksheets(sourceCell.Text)

          Dim nextRow As Long
          nextRow = GetLastRow(targetSheet, 1)
          nextRow = IIf(nextRow = 1, 1, nextRow + 1)

         .Range("A1").CurrentRegion.Copy targetSheet.Range("A" & nextRow)
         targetSheet.Columns.AutoFit

    End With

End Sub


Public Function GetLastRow(ByVal ws As Worksheet, Optional ByVal columnNumber As Long = 1) As Long

    With ws

      GetLastRow = .Cells(.Rows.Count, columnNumber).End(xlUp).Row

    End With

End Function

Upvotes: 0

Views: 55

Answers (2)

DisplayName
DisplayName

Reputation: 13386

substitute

      Dim nextRow As Long
      nextRow = GetLastRow(targetSheet, 1)
      nextRow = IIf(nextRow = 1, 1, nextRow + 1)

     .Range("A1").CurrentRegion.Copy targetSheet.Range("A" & nextRow)

with

    With .Range("A1").CurrentRegion
        targetSheet.Rows(1).Resize(.Rows.Count).Insert shift:=xlUp
        targetSheet.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
    End With

Upvotes: 0

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

Please give this a try...

The StartRow variable defines the destination row on targetSheet, you may change it as per your requirement.

Sub Copypastemeddata()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim sourceCell As Range
    Dim targetSheet As Worksheet
    Dim StartRow As Integer

    Application.ScreenUpdating = False

    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Opgørsel")

    Set sourceCell = ws.Range("D3")  'Cell with sheet names for copying to
    StartRow = 1    'Destination row on targetSheet
    With ws
          Set targetSheet = wb.Worksheets(sourceCell.Text)
         .Range("A1").CurrentRegion.Copy
         targetSheet.Range("A" & StartRow).Insert shift:=xlDown
         targetSheet.Range("A" & StartRow).PasteSpecial xlPasteValues
         targetSheet.Columns.AutoFit
    End With
    Application.CutCopyMode = 0
    Application.ScreenUpdating = True
End Sub

Upvotes: 1

Related Questions