VBA

Ben S

Board Regular
Joined
Oct 21, 2002
Messages
124
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Damon Ostrander

MrExcel MVP
Joined
Feb 17, 2002
Messages
4,239
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.
 

Forum statistics

Threads
1,144,293
Messages
5,723,547
Members
422,502
Latest member
barakgahtan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top