Need help with VBA Code in Copying multiple sheets to a new a workbook

[aashish83]

I have the following code i just want that when copying "BI" sheet it should just copy from Range A1:C28 and the rest to remain as it is....Below is the code

Sub FSRA()

With ActiveWorkbook.Sheets(Array("FSR Level A", "BI", "Instructions"))
.Copy
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "Audit Form FSR A", FileFormat:=52

End With

End Sub

 

[igold]

Does this do what you want...

Sub FSRA()
    
    Dim rng As Range
    With ActiveWorkbook.Sheets(Array("FSR Level A", "BI", "Instructions"))
        Set rng = ActiveWorkbook.Worksheets("BI").Range("A1:C28")
        .Copy
        ActiveWorkbook.Worksheets("BI").Cells.ClearContents
        ActiveWorkbook.Worksheets("BI").Range("A1:C28") = rng.Value2
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "Audit Form FSR A", FileFormat:=52
    End With

End Sub

 

[aashish83]

Does this do what you want...

Sub FSRA()
   
    Dim rng As Range
    With ActiveWorkbook.Sheets(Array("FSR Level A", "BI", "Instructions"))
        Set rng = ActiveWorkbook.Worksheets("BI").Range("A1:C28")
        .Copy
        ActiveWorkbook.Worksheets("BI").Cells.ClearContents
        ActiveWorkbook.Worksheets("BI").Range("A1:C28") = rng.Value2
        ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "Audit Form FSR A", FileFormat:=52
    End With

End Sub

Hi
This removes all content in A1:C28 in the new sheet and it is still copying A29...Is it possible that we can just hide A29, A30 & 31 in new sheet automatically?

 

[igold]

Did you actually try the code... On my test sheet, the code completely cleared Sheet BI and copied the Range("A1:C28") back into that sheet as per your Post #1.

 

[aashish83]

Did you actually try the code... On my test sheet, the code completely cleared Sheet BI and copied the Range("A1:C28) back into that sheet as per your Post #1.

Yes i did try it and i don't want it clear the data i just want to only copy (A1:C28) and not beyond that from BI sheet

 

[igold]

I don't understand, you only want Range(A1:C28) on sheet BI from the source workbook copied to the new workbook. This clears the Sheet BI in the new workbook and copies just that range to it.

 

[aashish83]

I don't understand, you only want Range(A1:C28) on sheet BI from the source workbook copied to the new workbook. This clears the Sheet BI in the new workbook and copies just that range to it.

Then BI sheet in new workbook is blank so i guess we don't need to clear contents

 

[igold]

If you don't clear contents you are back to where you were with your original code...

 

[aashish83]

The below code did the trick for me, changed the range from the original code.

Sub FSRA()

Dim rng As Range
With ActiveWorkbook.Sheets(Array("FSR Level A", "BI", "Instructions"))
Set rng = ActiveWorkbook.Worksheets("BI").Range("A1:B28")
.Copy
ActiveWorkbook.Worksheets("BI").Range("B29:B31").EntireRow.Hidden = True
ActiveWorkbook.Worksheets("BI").Range("A1:B28") = rng.Value2
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & Application.PathSeparator & "Audit Form FSR A", FileFormat:=52
End With

End Sub