0

I am trying to copy cell values from Flow Based Fixtures Sheet and paste the cell values only to the Report sheet. I know you need two lines to execute the copy and paste special but I am having difficulty figuring out how to refactor the code. Any help is greatly appreciated.

Flow Based Fixtures: Flow Based Fixtures

Report: Report

Private Sub Worksheet_Activate()

Dim fbfRow        As Long
Dim vbfRow        As Long

Dim fbflastRow     As Long
Dim vbflastRow     As Long

Dim fbfnextRow     As Long
Dim vbfnextRow     As Long

Dim fbfwksSource   As Worksheet
Dim vbfwksSource   As Worksheet

Dim fbfwksTarget   As Worksheet
Dim vbfwksTarget   As Worksheet

Set fbfwksSource = Worksheets("Flow Based Fixtures")
Set fbfwksTarget = Worksheets("Report")

fbflastRow = fbfwksSource.Range("A" & fbfwksSource.Rows.Count).End(xlUp).Row

fbfnextRow = 8

With fbfwksSource
    For fbfRow = 4 To fbflastRow
                
        .Range(.Cells(fbfRow, 1), .Cells(fbfRow, 6)).Copy
         fbfwksTarget.Cells(fbfnextRow, 1).PasteSpecial xlPasteValues
        .Range(.Cells(fbfRow, 7), .Cells(fbfRow, 7)).Copy 
        fbfwksTarget.Cells(fbfnextRow, 11).PasteSpecial xlPasteValues
        .Range(.Cells(fbfRow, 8), .Cells(fbfRow, 8)).Copy 
        fbfwksTarget.Cells(fbfnextRow, 12).PasteSpecial xlPasteValues
        .Range(.Cells(fbfRow, 14), .Cells(fbfRow, 14)).Copy
        fbfwksTarget.Cells(fbfnextRow, 13).PasteSpecial xlPasteValues
        fbfnextRow = fbfnextRow + 1
    Next fbfRow
End With
BigBen
  • 46,229
  • 7
  • 24
  • 40
sixman
  • 1
  • 1
  • Make each `Copy` line two lines: `.Range(.Cells(fbfRow, 1), .Cells(fbfRow, 6)).Copy`, `fbfwksTarget.Cells(fbfnextRow, 1).PasteSpecial xlPasteValues`, and so on. – BigBen Aug 31 '21 at 15:58
  • I tried that to no avail. It's still copying and pasting the formula. – sixman Aug 31 '21 at 18:33
  • Not if you're using `xlPasteValues`, that's impossible. – BigBen Aug 31 '21 at 18:38
  • Ben, here is what I tried to do: With fbfwksSource For fbfRow = 4 To fbflastRow .Range(.Cells(fbfRow, 1), .Cells(fbfRow, 6)).Copy fbfwksTarget.Cells(fbfnextRow, 1).PasteSpecial xlPasteValues fbfnextRow = fbfnextRow + 1 Next fbfRow End With This still copies over the formula. Any help is greatly appreciated. – sixman Aug 31 '21 at 18:45
  • `fbfwksTarget.Cells(fbfnextRow, 1).Resize(,6).Value = .Range(.Cells(fbfRow, 1), .Cells(fbfRow, 6)).Value`. I've never heard of `Copy` and `PasteSpecial xlPasteValues` pasting formulas. But if you prefer, bypass the clipboard directly by assigning the `.Value` of the source range to the target range. – BigBen Aug 31 '21 at 18:56
  • Note that the `.Copy` is one line, and the `.PasteSpecial` is a new line (not like that in your latest edit). – BigBen Aug 31 '21 at 19:04
  • I updated my edit again to reflect what I have tried in my code... It's still copying the formula. – sixman Aug 31 '21 at 19:14
  • I can't reproduce this. What is the formula it's copying? You could always skip the clipboard as mentioned in a previous comment and assign the `.Value` directly. – BigBen Aug 31 '21 at 19:18
  • Well, not sure what I just did... But I got working. Thanks Ben! – sixman Aug 31 '21 at 19:20

0 Answers0