Make CSV File error

Willie03

Board Regular
Joined
Jun 21, 2013
Messages
50
Hello I have a workbook that sends data in the master sheet to the corresponding tabs in the workbook. Basically the Information is added to the Master Tab and then with the click of a button the data is sent to the corresponding sheets and a workbook created as CSV file for certain sheets.
There are 9 different pay groups. Add the employees id number to column A and column G populates a pay group for the employee... so if I only have 4 employees belonging to different pay groups to report this week only 4 tabs should populate with data and 4 files created when I run the macro... Here are my issues:

  1. All other tabs will populate with the same data as in my Master sheet with all 4 employees when ONLY the 4 Pay Group Tabs should be populated
  2. New CSV files will be created for the 9 Pay Groups, when only 4 Files should be created
What I would like to add is for the data in the master to be transferred to the historical tab before the master sheet is emptied. So if I run the report this week and only have 4 employees in the master those four should be put in the Historical and then every time I run it for the new employees to be added to the historical tab below the previous employees so we can keep a historical of employees there.

Here is the code I have so far:

Code:
Sub transfer_data()
Application.ScreenUpdating = False
Dim filter_criteria As String
Dim bridge_rows As Integer
Dim rng As Range
Dim rng2 As Range
Dim dest_num_rows As Integer
bridge_rows = Worksheets("Bridge").Range("A1").CurrentRegion.Rows.Count
Set rng = Worksheets("Master").Range("A6").CurrentRegion
For n = 3 To bridge_rows + 1
    filter_criteria = Application.WorksheetFunction.Index(Worksheets("Bridge").Range("A1:B" & bridge_rows), Application.WorksheetFunction.Match(Worksheets(n).Name, Worksheets("Bridge").Range("B1:B" & bridge_rows), 0), 1)
    dest_num_rows = Worksheets(n).Range("A1").CurrentRegion.Rows.Count
    rng.AutoFilter Field:=7, Criteria1:=filter_criteria
    Set rng2 = rng.Offset(1, 0).Resize(rng.Rows.Count - 1, 6)
    rng2.Copy Destination:=Worksheets(n).Range("A" & dest_num_rows + 1)
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:="H:\BX-HR\BX-INDUSTRIAL RELATIONS\HR REPRESENTATIVES\PRIVATE\HRSSC\US&CA Benefits\Data Files BW\" & Workbooks("BW Retros Macro.xlsm").Worksheets(n).Name, FileFormat:=xlCSV, CreateBackup:=False
    ThisWorkbook.Sheets(n).Range("A1").CurrentRegion.Copy Destination:=ActiveWorkbook.Worksheets(1).Range("A1")
    ActiveWorkbook.Close savechanges:=True
Next n
rng.AutoFilter
Worksheets("Master").Range("A7:A" & rng.Rows.Count + 5).Clear
Worksheets("Master").Range("D7:D" & rng.Rows.Count + 5).Clear
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Forum statistics

Threads
1,222,404
Messages
6,165,855
Members
451,987
Latest member
Johnzdz

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
Back
Top