A macro that copies a sheet to a new workbook pasted AS VALUES and AS FORMULA on another?

Wow it looks more complicated as what i imagined it would be. Anyway i'll give it a go and post here asap, thanks for the help anteagles20!

Hi Anteagles20, I just tried your code and it produces this error: Run-time error '1004': Excel cannot access 'CUSTOMER_QUOTES'. The document may be read-only or encrypted. And when i press debug the yellow highlight is on this line:

Workbooks.Open (strWorkbook(intLoop))

by the way this is the code i copied in the file:

Public Sub copyingSub()
Dim strWorkbook(1 To 2) As String
Dim intLoop As Integer
Dim wkbMyWorkbook As Workbook

strWorkbook(1) = "C:\Users\Jason\Desktop\Excelforumhelp\CUSTOMER_QUOTES"
strWorkbook(2) = "C:\Users\Jason\Desktop\Excelforumhelp\CUSTOMER_QUOTES_REFERENCE"


For intLoop = 1 To 2

Workbooks.Open (strWorkbook(intLoop))
Set wkbMyWorkbook = ActiveWorkbook
wkbMyWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
wkbMyWorkbook.Worksheets(Worksheets.Count).Name = "Quotations"

ThisWorkbook.Worksheets("Quotations").Cells.Copy

If intLoop = 1 Then
wkbMyWorkbook.Worksheets("Quotations").Cells.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ElseIf intLoop = 2 Then
wkbMyWorkbook.Worksheets("Quotations").Cells.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If

wkbMyWorkbook.Close True
Set wkbMyWorkbook = Nothing

Next intLoop

End Sub

I tried removing the password of the QUOTATION ESTIMATE file it didn't work, i also tried changing the target folder and it doesn't work. I don't know why excel can't access the targets i put.

For better understanding, here is the QUOTATION ESTIMATE file i made: QUOTATION ESTIMATE.xlsm The code is in module5

i apologize if it seems i'm "making you do my homework" but i am trying to understand every line of code you're writing so i could improve on my vba skills. Again, thank you for your help

Hmm, not sure. Try including the file extension as well e.g

Please Login or Register  to view this content.

Also edited original post with comments to explain process, hopefully useful if you are learning vba.

Just tested it out and you definitely need to include the file extension. Hopefully that is the issue.

Also should probably mention for future reference - when putting code in a post you need to use the code tags. This is the # button above where you type the post.

Hi anteagles,

It's still not working for me I'm a little confused. My intention is to put the copied sheet into a QUOTATION_ESTIMATE folder and a QUOTATION_ESTIMATE_REFERENCE folder. Did you think that the copied sheet will be copied and named as QUOTATION_ESTIMATE.xlsm and QUOTATION_ESTIMATE_REFERENCE.xlsm that's why you suggested the extension? This is the error it gives me when i put an xlsm extention:

Run-time error '1004':

'C:\Users\Kevin\Desktop\Excelforumhelp\Customer_Quotes.xlsm' could not be found. Check the spelling of the file name, and verify that the file location is correct. If you are trying to open the file from your list of most recently used files, make sure that the file has not been renamed, moved, or deleted.

Anyway, i appreciated the comments on the codes you wrote, am learning a lot!