Copy sheet values onto another workbook

[avatron]

Hi Everyone,

I would like to copy a sheet end of every month that contains formulas (vlookup) into a another woorkbook but only values and no formulas. So basically this would be an archiving. This two different files are in different folders. My issue is due to the name of the sheet different every month (named by current month, right now is april) i would need to change the name in VBA every month to do the job properly.

My question is how can i specify to copy always the previous sheet in the workbook, and how to specify to place it with the original sheet name to the end of the target workbook?

Dim OutputFile As Workbook
Dim Inputpath As String
Dim Outputpath As String '


Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("C:\Users\xxxx\Desktop\Archive.xlsm")


InputFile.Sheets("March").Activate
InputFile.Sheets("March").Range("A1:O360").Copy


OutputFile.Sheets("Sheet1").Activate
OutputFile.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

OutputFile.Close savechanges:=True

Thanks for help!

 

[Fluff]

Hi & welcome to MrExcel.
How about

Dim OutputFile As Workbook, InputFile As Workbook
Dim Sht As String
Dim Outputpath As String '


Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("C:\Users\xxxx\Desktop\Archive.xlsm")
Sht = MonthName(Month(Date) - 1, 0)

InputFile.Sheets(Sht).Range("A1:O360").Copy


OutputFile.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues

OutputFile.Close savechanges:=True

Rather than pasting to sheet1, do you need to create a new sheet in the output file?

 

[avatron]

Hi Fluff,

Thanks, it worked well!

Don't need to pasting to sheet1, it would be the best if using the same name as it was originally.

 

[Fluff]

Will the sheet already exist, or does it need to be created?

 

[avatron]

Need to be created, or even can be copied the whole sheet without vlookup formula.

 

[Fluff]

Ok, how about

Dim OutputFile As Workbook, InputFile As Workbook
Dim Sht As String
Dim Outputpath As String


Set InputFile = ActiveWorkbook
Set OutputFile = Workbooks.Open("C:\Users\xxxx\Desktop\Archive.xlsm")
Sht = MonthName(Month(Date) - 1, 0)

With OutputFile
   InputFile.Sheets(Sht).Copy , .Sheets(.Sheets.Count)
   With .Sheets(Sht).UsedRange
      .Value = .Value
   End With
End With
OutputFile.Close savechanges:=True

 

[avatron]

Brilliant! thank you very much, its perfekt!

 

[Fluff]

You're welcome & thanks for the feedback.