Copy Range to Another Worksheet on Next Available Blank Row

jessbrasing

New Member
Joined
Mar 11, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
I've searched through the existing threads and haven't found what I need exactly - hope this isn't a repeat.

Need to add a simple second and third copy and paste special section to my current macro (below) if possible. Second copy/paste special- From range A6:I35 on worksheet "Tool" to the next available blank row (column A) in a worksheet named "Claims Data". Third copy/paste special- From range K6:R10 on worksheet "Tool" to the next available blank row (column A) in a worksheet named "Recommendations". Am I able to combine those steps with my current macro rather than running three separate? I don't want any of the original lines cleared and no formulas or formatting carried over. Thanks in advance!

Sub CopyValOnB()
Dim Rng As Range

With Sheets("Personal Worklist")
Set Rng = .Columns(2).Find(Sheets("Tool").Range("B2").Value, .Range("B1"), xlFormulas, xlWhole, xlByRows, xlNext, True, False)
End With
If Not Rng Is Nothing Then
Rng.Offset(, -1).Resize(, 34).Value = Sheets("Tool").Range("A2:AH2").Value
Else
MsgBox "Nothing found"
End If

End Sub
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

rollis13

Active Member
Joined
Jul 30, 2012
Messages
423
Office Version
  1. 2016
Platform
  1. Windows
I'll take a blindly attempt since I have now idea on your data layout. See if this adjustment does the job:
VBA Code:
Option Explicit
Sub CopyValOnB()
    Dim Rng    As Range
    Dim lr     As Long
    With Sheets("Personal Worklist")
        Set Rng = .Columns(2).Find(Sheets("Tool").Range("B2").Value, .Range("B1"), xlFormulas, xlWhole, xlByRows, xlNext, True, False)
    End With
    If Not Rng Is Nothing Then
        Rng.Offset(, -1).Resize(, 34).Value = Sheets("Tool").Range("A2:AH2").Value
        '
        lr = Sheets("Claims Data").Range("A" & Sheets("Claims Data").Rows.Count).End(xlUp).Row + 1
        Sheets("Tool").Range("A6:I35").Copy
        Sheets("Claims Data").Range("A" & lr).PasteSpecial Paste:=xlPasteValues
        lr = Sheets("Recommendations").Range("A" & Sheets("Recommendations").Rows.Count).End(xlUp).Row + 1
        Sheets("Tool").Range("K6:R10").Copy
        Sheets("Recommendations").Range("A" & lr).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False
        '
    Else
        MsgBox "Nothing found"
    End If
End Sub
 

jessbrasing

New Member
Joined
Mar 11, 2021
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Thank you! So far this is fantastic. Anyway to paste to the next blank row if I'm using a formula in column A or K in the two copied ranges?

I'm using this formula in the first columns copied & pasted: =IF(B6>0,$A$2,""). The macro doesn't see that as a blank row after it's copied to Claims Data/Recommendations and the next time it runs it pastes below those empty rows. Anyway to work around that or should I skip the formula?
 

rollis13

Active Member
Joined
Jul 30, 2012
Messages
423
Office Version
  1. 2016
Platform
  1. Windows
Since I was trying to guess your sheet structure I referred to this:
I don't want any of the original lines cleared and no formulas or formatting carried over.
Please consider attaching a test file with only some rows and dummy data but with the exact structure of the original.
And, why are the cells overlapping ? If you are going to copy from one sheet to another the paste will always overwrite whatever it finds.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,569
Messages
5,637,099
Members
416,957
Latest member
Brovashift

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top