Hi All,
I am trying to copy and paste some data from one sheet to another based on the active cell currently selected. I am using a macro that copies the data from one page to another based on whether there is a value in each row from B21 to B70. This is what I have, and I am getting an error during the range selection process highlighted in red.
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, rng As Range
Set sh1 = Sheets("APARTMENT UNITS")
Set sh2 = Sheets("INVOICE")
Lastrow = Sheets("INVOICE").Cells(Rows.Count, "B").End(xlUp).Row + 1
For Each c In sh1.Range("B21:B70")
If c.Value <> "" Then
Range(ActiveCell, ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 3), ActiveCell.Offset(0, 38)).Select
Selection.Copy
Sheets("INVOICE").Select
Range ("B" & Lastrow)
Lastrow = Lastrow + 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next
This way try number 6 changing up that range selection process. I cant seem to get it quite right. I was trying offset cells in this attempt, but have made several attempts with no success.
Basically, there is an active cell selected somewhere in column B. Based on that cell, I need to select and copy the data in the corresponding row columns B, D, E and BN. Then that data is pasted into a different sheet in the next blank row of column B.
Hopefully someone can help me figure out how to select a range of cells that are not contiguous using VBA.
I am trying to copy and paste some data from one sheet to another based on the active cell currently selected. I am using a macro that copies the data from one page to another based on whether there is a value in each row from B21 to B70. This is what I have, and I am getting an error during the range selection process highlighted in red.
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, rng As Range
Set sh1 = Sheets("APARTMENT UNITS")
Set sh2 = Sheets("INVOICE")
Lastrow = Sheets("INVOICE").Cells(Rows.Count, "B").End(xlUp).Row + 1
For Each c In sh1.Range("B21:B70")
If c.Value <> "" Then
Range(ActiveCell, ActiveCell.Offset(0, 2), ActiveCell.Offset(0, 3), ActiveCell.Offset(0, 38)).Select
Selection.Copy
Sheets("INVOICE").Select
Range ("B" & Lastrow)
Lastrow = Lastrow + 1
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
Next
This way try number 6 changing up that range selection process. I cant seem to get it quite right. I was trying offset cells in this attempt, but have made several attempts with no success.
Basically, there is an active cell selected somewhere in column B. Based on that cell, I need to select and copy the data in the corresponding row columns B, D, E and BN. Then that data is pasted into a different sheet in the next blank row of column B.
Hopefully someone can help me figure out how to select a range of cells that are not contiguous using VBA.