VBA save file as using a different workbook

Question

I am new to VBA and have experienced has been so good until now, that I am stock with something I was asked that I don't handle and haven't learned well yet.

I am giving some information to give you a better view of what my situation is and hopefully understand what I am doing.

What I am doing is the following, I am using a master file that opens two workbooks, the first one is data that needs to be cleared and prepared to fill out the second workbook which is a template (.csv) that would be uploaded to SAP.

The idea is to have all codes in the master file so that by pressing its buttons all actions needed can be completed, but I can't complete the last part which is saving the file as the specific name I want using a path in the master file. It throws me an error: This is what I've got:

'Pasting Data in CVC Template

Workbooks("CVC_Weekly AMS.csv").Activate
Range("E3").Select
ActiveSheet.Paste

'Saving Data as:

If Range("B4") = "C:\" Then

path = Range("B4")
Else
path = Range("B4") + "\"
End If

filename = InputBox("Type in the name to save this file - Recommended name CVC_Weekly AMS Current Date ")
ActiveWorkbook.SaveAs path & filename, FileFormat:=6 '6 .csv format

It seems that the code indicates to use the range in the template file instead of using the master file, the thing is that, if I add another code such as windows(masterfile) or workbooks (masterfile).activate, it saves the master file using the name I entered in the input box.

Can some one give me a hand with this? this is getting me frustrated!

See [This](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) Work with objects and it will be ok :) — Siddharth Rout, May 19 '15 at 16:28

score 1 · Answer 1 · answered May 19 '15 at 16:23

Not knowing what you are copying.

This code can direct you in the right direction.

Sub Button1_Click()
    Dim wb As Workbook
    Dim bk As Workbook
    Dim ws As Worksheet
    Dim sh As Worksheet
    Dim ShRng As Range

    Set wb = ThisWorkbook
    Set bk = Workbooks("CVC_Weekly AMS.csv")

    Set ws = wb.Sheets("Sheet1")
    Set sh = bk.Sheets("Sheet1")

    Set ShRng = sh.Range("E3")

    ws.Range("A1").Copy ShRng



    If ws.Range("B4") = "C:\" Then
        Path = ws.Range("B4")
    Else
        Path = ws.Range("B4") + "\"
    End If

End Sub

VBA save file as using a different workbook

1 Answers1