goldbeje
08-26-2012, 06:40 PM
I have a macro coded to create a new sheet, copy from the first sheet, and paste in specific format to the newly created sheet2. My problem is I cannot get column C to truncate at the end of each cell's string; being "..D" or "...D*". Every cell in column C ends in either "D" or "D*" and I need this removed. Any assistance would be helpful.
Code I am currently working with is shown below. I would like to insert the needed code after
CopyPaste_Sheet2.Hide
ActiveWorkbook.Sheets(2).Activate
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
CopyPaste_Sheet2.Hide
ActiveWorkbook.Sheets(2).Activate
UserForm1.Show vbModeless
UserForm1.Left = UserForm1.Left - UserForm1.Width / 2
UserForm2.Show vbModeless
UserForm2.Left = UserForm2.Left + UserForm1.Width / 2
End Sub
Thanks for looking at my frustrations.
J.
Code I am currently working with is shown below. I would like to insert the needed code after
CopyPaste_Sheet2.Hide
ActiveWorkbook.Sheets(2).Activate
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
CopyPaste_Sheet2.Hide
ActiveWorkbook.Sheets(2).Activate
UserForm1.Show vbModeless
UserForm1.Left = UserForm1.Left - UserForm1.Width / 2
UserForm2.Show vbModeless
UserForm2.Left = UserForm2.Left + UserForm1.Width / 2
End Sub
Thanks for looking at my frustrations.
J.