Open Workbook and Save As Different Name

[PRIMA]

Hell to All,

Is there any way how to copy all sheet tabs and limit the number of rows to be copied and Save As as different name.

Note:

1. I have 2000 rows or more each sheet tab with two (2) rows (Row 1 & 2) are header, I want to limit the rows to 200 each workbook.
2. The next 200 rows shall copy the same two (2) header rows and proceed to 201 rows plus the two (2) header.

Thank you very much in advance.

Regards.

 

[PRIMA]

The new workbook must the same format & sheet tab name as original. The only thing is to limit the rows into 200 or 250.

Any idea please..

Thanks.

 

[PRIMA]

Any help please.. Thanks.

 

[PRIMA]

Guys,

I want to open and save as the same workbook however, I need to limit the number of rows to 200. Each worksheet has 2000 or more rows.

How can I do this, any code? Please help..

Thanks in advance.

Regards.

 

[Richard Schollar]

Hi

Try this:

Sub SaveWB()
 
Dim strFile As String
Dim xlCalc As XlCalculation
Dim wb As Workbook, ws As Worksheet
Dim r As Range
With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Please select your file"
    .InitialFileName = Application.DefaultFilePath
    .AllowMultiSelect = False
    If .Show = -1 Then strFile = .SelectedItems(1) Else Exit Sub
End With
With Application
    xlCalc = .Calculation
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
Set wb = Workbooks.Open(strFile, False)
For Each ws In wb.Worksheets
    With ws
        Set r = Application.Intersect(.Range("201:" & .Rows.Count), .UsedRange)
        If Not r Is Nothing Then r.EntireRow.Clear
        Set r = Nothing
    End With
Next ws
With Application.FileDialog(msoFileDialogSaveAs)
    .Title = "Please choose filename to save as"
    .AllowMultiSelect = False
    .InitialFileName = wb.FullName
    If .Show = -1 Then .Execute
End With
wb.Close
 
End Sub

 

[PRIMA]

Hi Richard Schollar,

Thank you so much for the quick reply. I copied & paste your code however, when I tried to run the macro, it does not save as the workbook.

Any idea? Or may be I missed something.

Thanks again.

Regards.

 

[Richard Schollar]

Do you get the dialog box asking you for a filename to save as?

 

[PRIMA]

Yes.. But, the dialog box "Files of type:" shows "All Files(*.*) and at the top of it shows "Please select your file" When I enter or type the filename, it doesn't save anything.

Thanks again.

Regards.

 

[Richard Schollar]

There are two filedialogs - the first one asks you to select the file you want to open. The code then blitzes all rows beyond row 200 on ever sheet in this file then activates another dialog that asks you to give a SaveAs name. Are you encountering these steps?

 

[PRIMA]

Hi Richard Schollar,

You are right. I missed something. I copied & paste the code into the same file. Now, it works perfectly!!!

Well, is it possible to continuously save as the file without prompting the dialog box "Please select your file" until it finishes the whole rows?

Thank you again.

Regards.