I need to copy a range of cells starting from a header row to "sheet2"..

[goldbeje]

:banghead: :banghead: :banghead:

I am having extreme difficulty getting my macro to copy a specific range and pasting it to "sheet2". The range will be dependant on only the header row for reference. The problem with the code is that is is copying only the selected cell at time of macro trigger. I want it to copy from "Batch Name" down covering all the remaining data below that row (There are multiple rows of data, "Batch Name" is the best header row to reference though and it happens to be in cell B). I know I am missing the part at which I need to have the code to tell this macro to highlight everything needed below r.Range and copy that to the beginning of "Sheet2". Any assistance would be helpful. I am not experienced in VBA really at all. What I created is from reading and searching threads.

I currently have the code as:

Option Explicit
Public Sub CheckLocation()
LocCheckForm.Show

'Find the start row
Dim r As Range
Set r = ActiveSheet.Range("B1:B99").Find(What:="Batch Name", LookAt:=xlPart)
If Not r Is Nothing Then

Set r = Selection
Selection.Copy

'to paste on sheet name: "Sheet2"
Sheets("Sheet2").Range(r.Address).PasteSpecial (xlPasteAll)
End If
End Sub


The header rows go across columns. The starting location changes as the data before it is dependant on the user/request. I have added an image of the worksheet to give you a visual. The cell highlighted would be the first row to copy from down. All of the data would need to be copied to Sheet2. There wouldn't be an instance where the header row would be past B99.
After the header is found the rows from that point down need to be copied; there can be 100 or 200 of these rows.. The first table is how the worksheet is originally; the second table would be the preference on how to copy them to "sheet2". The reason for this is because I currently have a macro running that verifies if barcodes match plates and their locations. The point of this copy to "Sheet2" is to compare lines from scans to find errors. If that isn't possible it would be all of them from the header row down. It would be the best day of my life if this is possible, I have been pulling my hair out teaching myself VBA the last week.

Thanks for looking at my frustration and road block.
J.

[CatDaddy]

Option Explicit
Public Sub CheckLocation()
LocCheckForm.Show

'Find the start row
Dim r As Range
Set r = ActiveSheet.Range("B1:B99").Find(What:="Batch Name", LookAt:=xlPart)
If Not r Is Nothing Then
r.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1")
End If
End Sub

[goldbeje]

CatDaddy,

That works for the header row, but I need everything from that point down. In the attachment is a visual of what I am trying to do. The worksheet is in the document and the rows to copy to sheet2.

Thanks for your assistance.



Private Sub CommandButton1_Click()
'Find the start row
Dim r As Range
Set r = ActiveSheet.Range("B1:B99").Find(What:="PCR Plate ID", LookAt:=xlPart)
If Not r Is Nothing Then
r.EntireRow.Copy Destination:=Sheets("Sheet2").Range("A1")
End If

Set r = Selection
Selection.Copy

'to paste on sheet name: "Sheet2"
Sheets("Sheet2").Range(r.Address).PasteSpecial (xlPasteAll)

End Sub

[CatDaddy]

Private Sub CommandButton1_Click()
Dim r As Range
Dim lr, rowResize, colResize As Long
Set r = ActiveSheet.Range("B1:B99").Find(What:="PCR Plate ID", LookAt:=xlPart)
lr = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
rowResize = lr - r.Row + 1
colResize = 12
If Not r Is Nothing Then
r.Offset(0, -1).Resize(rowResize, colResize).Copy Destination:=Sheets("Sheet2").Range("A1")
End If

End Sub

[goldbeje]

Private Sub CommandButton1_Click()
Dim r As Range
Dim lr, rowResize, colResize As Long
Set r = ActiveSheet.Range("B1:B99").Find(What:="PCR Plate ID", LookAt:=xlPart)
lr = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
rowResize = lr - r.Row + 1
colResize = 12
If Not r Is Nothing Then
r.Offset(0, -1).Resize(rowResize, colResize).Copy Destination:=Sheets("Sheet2").Range("A1")
End If

End Sub

Thanks!! That worked to copy all the data. Is it possible to do that like the attachment shows? Rows A,B,C,G,I,M and then insert a row after G, copy A to H (inserted row), cut rows H,I,M and then move to row A? I have a form that saves scans matching this data and want it to save side by side to find differences. With rows that are different highlight RED for incorrect. But, I need to be able to copy the worksheet in the same format to do so.

Thanks again.

[CatDaddy]

can you post a workbook please

[goldbeje]

I have attached the workbook I have been working with.

[CatDaddy]

i meant post a workbook with what you want sheet 2 to look like!

[goldbeje]

attached

[CatDaddy]

Im sorry I do not see how you are getting from the first sheet to the second sheet...your logic doesnt make sense to me

[goldbeje]

Im sorry I do not see how you are getting from the first sheet to the second sheet...your logic doesnt make sense to me

The idea is to copy from the first worksheet those four columns. The problem is that for the Source ID it is split in to 2 columns with 4 offsets. Also, column A and B only show an identifier for the Source ID and offsets once. They need to by ascending down with all information on all Source IDs with each of the 4 offsets.

1 119418 J94317_001 1
1 119418 J94317_001 2
1 119418 J94317_001 3
1 119418 J94317_002 4

The purpose of this macro is to be an error check for loading plates onto a deck of a liquid handler instrument. Taking from Source ID (triplicates) and please samples in to PCR plate (4 offsets). Each plate and deck location for the plates are scanned for verification.

Does that make a bit more sense?

[CatDaddy]

what about the difference between the lines that have J94317_001 and J94317_001*?? I couldnt pick out a consistent pattern in the example you gave

[CatDaddy]

Private Sub CommandButton1_Click()
Dim r As Range
Dim srcID As String
Dim lr, sR, i, c, INDX As Long
Set r = ActiveSheet.Range("B1:B99").Find(What:="PCR Plate ID", LookAt:=xlPart)
INDX = 1
i = 2
lr = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
Range("B" & r.Row & ",C" & r.Row & ",G" & r.Row).Copy Destination:=Sheets(2).Range("B1")
For c = (r.Row + 1) To lr Step 3
srcID = Range("B" & c).Text

With Sheets(2)
.Range("A" & i & ":A" & i + 3).Value = INDX
.Range("B" & i & ":B" & i + 3).Value = srcID
End With

Range("C" & c & ",G" & c).Copy Destination:=Sheets(2).Range("C" & i)
Range("H" & c & ",L" & c).Copy Destination:=Sheets(2).Range("C" & i + 1)
Range("C" & c + 1 & ",G" & c + 1).Copy Destination:=Sheets(2).Range("C" & i + 2)
Range("H" & c + 1 & ",L" & c + 1).Copy Destination:=Sheets(2).Range("C" & i + 3)

i = i + 4
INDX = INDX + 1
Next c
End Sub

[goldbeje]

what about the difference between the lines that have J94317_001 and J94317_001*?? I couldnt pick out a consistent pattern in the example you gave

In the worksheet, it is generated with a D and D* after each Source ID plate number (J93714_001D* or J93714_001D). The D and/or D* are not needed and useless in this process. The barcodes being scanned don't have a D at the end of them.

[goldbeje]

Posted on accident... New thread started for this.