goldbeje
08-22-2012, 08:58 AM
: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.
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.