Copy sheet values onto another workbook

avatron

New Member
Joined
Apr 29, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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?

VBA Code:
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!
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
Hi & welcome to MrExcel.
How about
VBA Code:
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

New Member
Joined
Apr 29, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
Will the sheet already exist, or does it need to be created?
 

avatron

New Member
Joined
Apr 29, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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

New Member
Joined
Apr 29, 2020
Messages
4
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Brilliant! thank you very much, its perfekt!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
50,563
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,361
Members
412,320
Latest member
sixnine0312
Top