Great video - can you please help if I want to save these in excel workbook instead of PDFs? this would really what I'm looking for Greg! I look forward to your response!
thanks for this video. it save the day. how about filtering the in between number. like from 1 to 10 as cell / data value to filter from the "list" sheet and selecting the path of the file?
Thank you so much for your help! I have been watching your videos and playing with code the past two days and was able to use this and the other code you have for copying into excel to make a loop for this but export to Excel. The only thing different is I made the data validation list in A1 on sheet 1 and then my column for region was named organization and I changed it to be under Column A(2). Sub copy_data_2_new_book() Dim count_col As Integer Dim count_row As Integer Dim og As Worksheet Dim wb As Workbook Dim organization As String Dim i As Long Set Data = ThisWorkbook.Sheets(1) Set List = ThisWorkbook.Sheets(2) 'count numnber of regions List.Activate Count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown))) Data.Activate For i = 1 To Count 'updating the region name organization = List.Cells(i, 1).Text Data.Cells(1, 1) = organization Set og = Sheet1 organization = og.Cells(1, 1).Value Set wb = Workbooks.Add wb.Sheets("Sheet1").Name = organization og.Activate count_col = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlToRight))) count_row = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlDown))) ActiveSheet.Range("A2").AutoFilter Field:=1, Criteria1:=organization 'copies data from sheet to workbook og.Range(Cells(1, 1), Cells(count_row, count_col)). _ SpecialCells(xlCellTypeVisible).Copy wb.Sheets(organization).Cells(1, 1).PasteSpecial xlPasteValues wb.Sheets(organization).Cells(1, 1).PasteSpecial xlPasteFormats Application.CutCopyMode = False og.ShowAllData og.AutoFilterMode = False wb.Activate Cells.Select Cells.EntireColumn.AutoFit Range("A1").Select 'save and close On Error Resume Next Application.DisplayAlerts = False wb.SaveAs "/Users/p00082221/My Drive (p00082221@houstonisd.org)/PCIM/Reporting 22-23/Campus Data Reports/To Be Sorted/" & _ organization & " " & Format(Date, "mm-dd-yyyy") & ".xlsx" Application.DisplayAlerts = True wb.Close 'savechanges:=False Next i End Sub
Dude, you taught me something I've been looking to do for about a month. Please keep it up as I learn easier the way you explain. Thanks.
Will do! Thanks for the motivation!!
Good video - very helpful. I was struggling getting something similar w/out the save as dialog. Your instructions helped perfectly.
So glad to hear that!! Thanks for watching!!
I'm brazilian, but don't write good english: You make great video. I'm in love with you. I love you. Congratulations.
Thank you!!
Great video - can you please help if I want to save these in excel workbook instead of PDFs? this would really what I'm looking for Greg! I look forward to your response!
How do I save the filtered data in Excel instead of PDF?
thanks for this video. it save the day. how about filtering the in between number. like from 1 to 10 as cell / data value to filter from the "list" sheet and selecting the path of the file?
Very helpful. When I try to execute this, it only saves the first 2 regions as pdfs. Any suggestions?
Thank you so much for your help! I have been watching your videos and playing with code the past two days and was able to use this and the other code you have for copying into excel to make a loop for this but export to Excel. The only thing different is I made the data validation list in A1 on sheet 1 and then my column for region was named organization and I changed it to be under Column A(2).
Sub copy_data_2_new_book()
Dim count_col As Integer
Dim count_row As Integer
Dim og As Worksheet
Dim wb As Workbook
Dim organization As String
Dim i As Long
Set Data = ThisWorkbook.Sheets(1)
Set List = ThisWorkbook.Sheets(2)
'count numnber of regions
List.Activate
Count = WorksheetFunction.CountA(Range("A1", Range("A1").End(xlDown)))
Data.Activate
For i = 1 To Count
'updating the region name
organization = List.Cells(i, 1).Text
Data.Cells(1, 1) = organization
Set og = Sheet1
organization = og.Cells(1, 1).Value
Set wb = Workbooks.Add
wb.Sheets("Sheet1").Name = organization
og.Activate
count_col = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A2", Range("A2").End(xlDown)))
ActiveSheet.Range("A2").AutoFilter Field:=1, Criteria1:=organization
'copies data from sheet to workbook
og.Range(Cells(1, 1), Cells(count_row, count_col)). _
SpecialCells(xlCellTypeVisible).Copy
wb.Sheets(organization).Cells(1, 1).PasteSpecial xlPasteValues
wb.Sheets(organization).Cells(1, 1).PasteSpecial xlPasteFormats
Application.CutCopyMode = False
og.ShowAllData
og.AutoFilterMode = False
wb.Activate
Cells.Select
Cells.EntireColumn.AutoFit
Range("A1").Select
'save and close
On Error Resume Next
Application.DisplayAlerts = False
wb.SaveAs "/Users/p00082221/My Drive (p00082221@houstonisd.org)/PCIM/Reporting 22-23/Campus Data Reports/To Be Sorted/" & _
organization & " " & Format(Date, "mm-dd-yyyy") & ".xlsx"
Application.DisplayAlerts = True
wb.Close 'savechanges:=False
Next i
End Sub
This is exactly what I was looking for. Liked and subscribed. Thanks a lot!
Glad to hear that!! Thank you!!
good