Copying multiple cells not in same range

Question

I have a code that copies a specified range (depending on how many values cells in another sheet have values). so if in sheet 1 column B I have 4 cells populated, the macro will copy the values of 4 rows in sheet 2.

Now I would like it also to always copy another row in same sheet together with the range which is W157.

The part that copies the range is working fine but it's not copying the one last row.

any suggestions please.

Public Sub CommandButton2_Click()
Dim i As Long
Dim wb As Workbook
Dim NewWB As Workbook
Dim saveFile As String
Dim WorkRng As Range


On Error Resume Next


i = Sheets(1).Range("B14").End(xlDown).Row - 13 '(start checking if any 
values from B15 downwards)

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set wb = ActiveWorkbook
Set NewWB = Application.Workbooks.Add
Thispath = wb.path

wb.Sheets(2).Range ("W7:W" & i + 5) & wb.Sheets(2).Range("W157").Copy
NewWB.Worksheets(1).Range("A1").PasteSpecial Paste:=xlPasteValues
NewWB.SaveAs filename:=Thispath & "\TEXTFILE.txt", FileFormat:=xlText, 
CreateBackup:=False
NewWB.Close

Application.CutCopyMode = False
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Hein Wessels Hein Wessels · Accepted Answer · 2018-05-31T11:32:33

You are only specifying to copy one range. The '&' you used is not "and", but the indicator for concatenating strings in VBA. "And" is the logical operator, but I don't think it will work in the .Copy function.

You should copy the two ranges independently. First the row, and then the range. That would be the easiest.

Hope this helps. :)

And I noted your comment. You can always write a separate line to read and print the final row at the bottom of what you have copied.

Copying multiple cells not in same range

1 Answers