-
macro one sheet to multiple-need adjustment w/ input boxes
I utilize the macro below to take a single sheet of data and breakout information based on a specific column. In this case column D.
I use the macro for many different worksheets and the information I want to breakout is not always in column D.
What I would like to do is change it so that the range of data is selected with an input box and the data to break apart is also by an input box.
So when I run it, the macro will ask me the range of the data, which I will enter. Then the next input box would ask what column I want to break out the data.
Sub test()
Application.ScreenUpdating = False
Dim i As Range, LR As Long, ws As Worksheet, wb As Workbook, C As Range
LR = Range("A" & Rows.Count).End(xlUp).Row
Sheets("sheet1").Range("d1:d" & LR).AdvancedFilter xlFilterCopy, copytorange:=Range("h1"), unique:=True
For Each C In Range("h2:h" & Range("h" & Rows.Count).End(xlUp).Row)
On Error GoTo 1
Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = C.Value
Next C
1
For Each C In Sheets("sheet1").Range("h2:h" & Sheets("sheet1").Range("h" & Rows.Count).End(xlUp).Row)
Sheets("sheet1").Range("a1:d1").AutoFilter field:=4, Criteria1:=C.Value
Sheets("sheet1").Range("a1:d" & Sheets("sheet1").Range("a" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy
For Each ws In ThisWorkbook.Worksheets
If ws.Name = C.Value Then
ws.Range("a1").PasteSpecial xlPasteColumnWidths
ws.Range("a1").PasteSpecial xlValue
ws.Range("a1").PasteSpecial xlPasteFormats
End If
Next ws
Sheets("sheet1").Range("a1:d1").AutoFilter
Application.CutCopyMode = False
Next C
Sheets("sheet1").Columns("h").Delete
Sheets("sheet1").Select
Application.ScreenUpdating = True
End Sub
-
-
[VBA]
Dim RngInput As String
Dim ColInput As Integer
RngInput = Inputbox("Range?", , defaultRange)
ColInput = Inputbox("Column?", , defaultCol)
[/VBA]
-
CatDaddy - thanks. How would I reference that in my macro?
-
i wasnt sure which range or collumn was the one you wanted to alter but put that in before, then change your autofilter to:
[VBA]
Sheets("sheet1").Range(RngInput).AutoFilter field:=ColInput, Criteria1:=C.Value
[/VBA]
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules