Dave T
02-20-2013, 06:21 PM
Hello All,
I have a spreadsheet that is used to process truck heavy load permit applications.
On the first sheet called 'Heavy Load Register' we manually assign each application a HL number, and then from the permit we add:
Column B - Date application assessed
Column C - 'y' if it is an enquiry
Column D - The operator/truck company name
Column E - What the load is
Column F - Where they are going from and to
Column G - What level of assessment it is
Column H - How long it took from receiving the application to us processing it and sending it backFor applications that are enquiries (identified by 'y' in column C) we transfer the details for the worksheet called 'Heavy Load Register' to the worksheet called 'Non-Standard Studies'.
Currently this is a clumsy copy and paste and I would like to automate the process.
What I am after is a macro that will find all instances of 'y' in column C of the worksheet called 'Heavy Load Register' and copy associated data from other columns A, B, C, D, E, F & H to the worksheet called 'Non-Standard Studies'.
I have found a macro that works but it only copies one column and it deletes any borders in the destination.
To copy the other associated data I have used VLOOKUP but I am sure a macro could do this cleaner.
Sub RangeCopyPaste()
'http://stackoverflow.com/questions/13470007/copy-all-cells-with-certain-value-into-another-column-skipping-blanks
Dim cell As Range
Dim NewRange As Range
Dim MyCount As Long
MyCount = 1
For Each cell In Worksheets("Heavy Load Register").Range("C2:C1000")
If cell.Value = "y" Or cell.Value = "Y" Then
If MyCount = 1 Then Set NewRange = cell.Offset(0, -2)
Set NewRange = Application.Union(NewRange, cell.Offset(0, -2))
MyCount = MyCount + 1
End If
Next cell
NewRange.Copy Destination:=ActiveSheet.Range("B6")
End Sub
If someone could help me out with a macro that would copy all of the data to the other worksheet, or at the very minimum just copy the HL number without any formatting to the destination worksheet (i.e. paste values) it would be greatly appreciated.
Regards,
Dave T
I have a spreadsheet that is used to process truck heavy load permit applications.
On the first sheet called 'Heavy Load Register' we manually assign each application a HL number, and then from the permit we add:
Column B - Date application assessed
Column C - 'y' if it is an enquiry
Column D - The operator/truck company name
Column E - What the load is
Column F - Where they are going from and to
Column G - What level of assessment it is
Column H - How long it took from receiving the application to us processing it and sending it backFor applications that are enquiries (identified by 'y' in column C) we transfer the details for the worksheet called 'Heavy Load Register' to the worksheet called 'Non-Standard Studies'.
Currently this is a clumsy copy and paste and I would like to automate the process.
What I am after is a macro that will find all instances of 'y' in column C of the worksheet called 'Heavy Load Register' and copy associated data from other columns A, B, C, D, E, F & H to the worksheet called 'Non-Standard Studies'.
I have found a macro that works but it only copies one column and it deletes any borders in the destination.
To copy the other associated data I have used VLOOKUP but I am sure a macro could do this cleaner.
Sub RangeCopyPaste()
'http://stackoverflow.com/questions/13470007/copy-all-cells-with-certain-value-into-another-column-skipping-blanks
Dim cell As Range
Dim NewRange As Range
Dim MyCount As Long
MyCount = 1
For Each cell In Worksheets("Heavy Load Register").Range("C2:C1000")
If cell.Value = "y" Or cell.Value = "Y" Then
If MyCount = 1 Then Set NewRange = cell.Offset(0, -2)
Set NewRange = Application.Union(NewRange, cell.Offset(0, -2))
MyCount = MyCount + 1
End If
Next cell
NewRange.Copy Destination:=ActiveSheet.Range("B6")
End Sub
If someone could help me out with a macro that would copy all of the data to the other worksheet, or at the very minimum just copy the HL number without any formatting to the destination worksheet (i.e. paste values) it would be greatly appreciated.
Regards,
Dave T