Hi
The code below finds a date using the caledar control on sheet1 , then finds the first set of 7 cells on sheet1 and pastes that info on sheet3.range("a:h"). Works great thanks to Jay's help. Now If I want to paste the next set of 7 cells from sheet1 to sheet3 then I have to rewrite some of the code so that the next line on sheet3(row2) contains the next set of info. This works great also!
Private Sub CommandButton1_Click()
Sheets("sheet1").Activate
For Each a In Worksheets("sheet1").Range("d2:d10000")
If a.Value = ListBox1.Value Then
a.Offset(0, 0).Activate
Dim lastrow As Long, rng As Range
lastrow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet
Set rng = ActiveCell.Offset(0, 7).Resize(,
End With
rng.Copy Sheets("Sheet3").Cells(lastrow + 1, 1)
lastrow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet
Set rng = ActiveCell.Offset(0, 15).Resize(,
End With
rng.Copy Sheets("Sheet3").Cells(lastrow + 1, 1)
End If
Next a
Sheets("sheet3").Activate
End Sub
Heres the problem- if I have 29 sets of info to be pasted from sheet1 to sheet3, do I have to rewrite this code 29 times or is there an easier way to do this??
The code below finds a date using the caledar control on sheet1 , then finds the first set of 7 cells on sheet1 and pastes that info on sheet3.range("a:h"). Works great thanks to Jay's help. Now If I want to paste the next set of 7 cells from sheet1 to sheet3 then I have to rewrite some of the code so that the next line on sheet3(row2) contains the next set of info. This works great also!
Private Sub CommandButton1_Click()
Sheets("sheet1").Activate
For Each a In Worksheets("sheet1").Range("d2:d10000")
If a.Value = ListBox1.Value Then
a.Offset(0, 0).Activate
Dim lastrow As Long, rng As Range
lastrow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet
Set rng = ActiveCell.Offset(0, 7).Resize(,
End With
rng.Copy Sheets("Sheet3").Cells(lastrow + 1, 1)
lastrow = Sheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Row
With ActiveSheet
Set rng = ActiveCell.Offset(0, 15).Resize(,
End With
rng.Copy Sheets("Sheet3").Cells(lastrow + 1, 1)
End If
Next a
Sheets("sheet3").Activate
End Sub
Heres the problem- if I have 29 sets of info to be pasted from sheet1 to sheet3, do I have to rewrite this code 29 times or is there an easier way to do this??