Copy Sheet

[rhombus4]

I managed to get help to copy a sheet to a new book, however because the formulas contained indirect in them it gave a ref# errors


this is the formula it tries to copy

=IF(INDIRECT("'"&A1&"'!C5")="","",VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0)) al the other formulas copy ok to the new book.

ALso what do I need to add if I want the new book to be called whatever is in cell A1 and not called New Book

Sheets("Sheet1").Copy
With ActiveSheet.UsedRange
.Value = .Value
End With

 

[wigi]

For instance:

Sub test()
    Sheets("Sheet1").Copy
    With ActiveSheet
        .UsedRange.Value = .UsedRange.Value
        .Parent.SaveAs "C:\" & .[A1], 56
    End With
End Sub

Wigi

 

[rhombus4]

For instance:

Sub test()
    Sheets("Sheet1").Copy
    With ActiveSheet
        .UsedRange.Value = .UsedRange.Value
        .Parent.SaveAs "C:\" & .[A1], 56
    End With
End Sub

Wigi

It doesnt need to save as just open a new excel doc with the name in cell A1 and show all numbers (values) as per original sheet

Still shows Ref# errors and gives runtime error 1004 and highlights

.Parent.SaveAs "C:\" & .[A1], 56

It doesnt need to save as just open a new excel doc with the name in cell A1 and show all numbers (values) as per original sheet

 

[wigi]

It doesnt need to save as just open a new excel doc with the name in cell A1 and show all numbers (values) as per original sheet

.Parent.SaveAs "C:\" & .[A1], 56

Then probably cell A1 of the active sheet contains characters that are not allowed in a filename, or is just empty, ... I cannot guess what you have in your sheets.

You cannot give the file a name without saving it. That's why I do the SaveAs.

Still shows Ref# errors and gives runtime error 1004 and highlights

That's correct, I did not say that I addressed that issue. In general, you have 1 question per thread in an Excel board. It keeps things more organized.

 

[rhombus4]

Then probably cell A1 of the active sheet contains characters that are not allowed in a filename, or is just empty, ... I cannot guess what you have in your sheets.

You cannot give the file a name without saving it. That's why I do the SaveAs.



That's correct, I did not say that I addressed that issue. In general, you have 1 question per thread in an Excel board. It keeps things more organized.

OK the priority is the #ref errors.

It will work if normal formulas are contained but wont work when it contains the indirect formula

=IF(INDIRECT("'"&A1&"'!C5")="","",VLOOKUP(B$2,INDIRECT("'"&A1&"'!B2:D5"),2,0))