VBA

[Ben S]

I need to write some VBA code and attach it to a button.

I have a spreadsheet and would like to automatically export sections of the spreadsheet to different people. How would i write the following in VBA

The spreadsheet contains roughly 1000rows and columns extend from AA to AJ. I would like to extract rows where column E(Column E has been named range division) = either K11 or S41 and export this into a new workbook called K Division, sheet 1. Where column e = the criteria
E.g If row 5 column E = S41 i would like to transfer the information, formulas and formatting of AA5 to AJ5 into a workbook named K Division onto worksheet 1.

 

[Damon Ostrander]

Hi Ben,

Here is a macro that I believe does what you describe. The source worksheet must be active when you run the macro. The macro does no checking to ensure that rows 41 or 11 are not removed if E41 or E11 contain one of the values in S41 or K11, but I assume that this can never happen. I did not make use of the name you provided for column E because "range division" is not a legal range name (contains a blank). Perhaps you meant just "division"? Anyway, here is the code. It marks the rows for removal and then removes them on a second pass so that the row numbering change does not affect the algorithm.



Sub MoveDivision()
Dim NextAvailRow As Long
Dim iRow As Long
Dim DestWS As Worksheet
Set DestWS = Workbooks("K Division.xls").Worksheets(1)
NextAvailRow = 1
Application.ScreenUpdating = False
For iRow = 1 To [E65536].End(xlUp).Row
Select Case Cells(iRow, 5)
Case [K11], [S41]
Range(Cells(iRow, 27), Cells(iRow, 36)).Copy Destination:=DestWS.Cells(NextAvailRow, 27)
Application.CutCopyMode = False
Cells(iRow, 5) = "remove"
NextAvailRow = NextAvailRow + 1
End Select
Next iRow
Application.ScreenUpdating = True
Application.ScreenUpdating = False
For iRow = [E65536].End(xlUp).Row To 1 Step -1
If Cells(iRow, 5) = "remove" Then Rows(iRow).Delete
Next iRow
Application.ScreenUpdating = True
End Sub

This macro should work from a standard macro module or the code can be extracted and pasted into a button's Click event.