Copying Ranges from 7 worksheets in one workbook to 7 new files

[Joebob1960]

I was wondering if anyone could direct me to the proper code for this, as this is really tedious.

Basically, I have 7 worksheets in a workbook I am updating. Once all the calculations are made, I then need to copy certain ranges of these worksheets to files/workbooks (as values) that go by the same name as the tab. The new workbook needs to be the same name as the tab, and will overwrite the previous file/workbook.

Does anyone know how I would go about this?

 

[Joe4]

I think we need some more details, such as:
1. How many sheets are in the workbook in total? Are there more than 7? If so, how do we determine which ones should be used to create new files and which ones shouldn't?
2. What folder/directory should the files be saved to?
3. Will the new files also be Excel files?
4. How do we determine which ranges are to be included (it sounds like you only want part of each sheet, not the entire sheet)?

 

[Joebob1960]

Answers below:

I think we need some more details, such as:
1. How many sheets are in the workbook in total? Are there more than 7? Yes. there are about 25 in total.

If so, how do we determine which ones should be used to create new files and which ones shouldn't? Selecting, or Shift Left Click, or Cntl Click
2. What folder/directory should the files be saved to?
A separate one. Same Parent, but different folder...something like...\New Extracts
3. Will the new files also be Excel files? Yes
4. How do we determine which ranges are to be included (it sounds like you only want part of each sheet, not the entire sheet)?
That is correct. Just a part of each sheet, as I have notations, directions and objects in the first few rows of each sheet, and right most columns.

Thanks for any help you can provide.

 

[Joe4]

OK, regarding items 1, 2, and 4, we actually need you to provide the details. Otherwise, we have no "map" which to program against.

Pretend you are giving someone instructions to someone totally new, who has never seen this before.

1. What instructions would you give them regarding how to identify the 7 sheets you want to create new files from?
Is it always going to be the same 7 sheet names? If so, what exactly are the names of these 7 sheets?
Otherwise, how can we determine which sheets to include?

2. What is the current path where this data/macro Excel file will be stored?
What is the full path of where you want the files saved to.

4. What are the EXACT rules for which columns/rows to include/exclude?

 

[Richard U]

Without knowing exactly what you want, this is just guesswork, but should point you in the right direction

Sub split_em_up()
Dim wb As New Workbook
Dim clct As New Collection
Dim var() As Variant
Dim sht(1 To 7) As String
Dim rng(1 To 7) As String
Dim ctr As Integer
Dim path As String
Dim rng2 As Range
Dim r1 As Integer, r2 As Integer, c1 As Integer, c2 As Integer
Dim s1 As String, s2 As String


Application.DisplayAlerts = False

'path = "C/wherever/your/files/are/located"

'put your named ranges into the array

rng(1) = "rangename1"
rng(2) = "rangename2"
rng(3) = "rangename3"
rng(4) = "rangename4"
rng(5) = "rangename5"
rng(6) = "rangename6"
rng(7) = "rangename7"

For ctr = 1 To 7
Set wb = Workbooks.Add

'Sheets(ctr).Name
ThisWorkbook.Activate
Range(rng(ctr)).Select
 var = Range(rng(ctr)).Value
 r1 = 1
 r2 = UBound(var, 1)
 c1 = 1
 c2 = UBound(var, 2)
With wb
    .Activate
    s1 = .Sheets(1).Cells(1, 1).Address
    s2 = .Sheets(1).Cells(r2, c2).Address
    Set rng2 = .Sheets(1).Range(s1 & ":" & s2)
    
    
    rng2.Value = var
    .SaveAs (path & ThisWorkbook.Sheets(ctr).Name)
End With
wb.Close

Next ctr



Application.DisplayAlerts = True


End Sub

 

[Joebob1960]

Thanks. I will try that.