Excel/VBA help

CFA General
#1

Say I have an existing file, Book1.xls. Currently I am viewing Book2.xls, which has worksheets named “Foo” and “Bar”. What would be the VBA code to add worksheets “Foo” and “Bar” to the workbook Book2.xls?

#2

When in doubt, hit record macro.

#3

err I would like to save to Book1.xls

#4

Ah you didn’t make that clear. Easy. You need to make use of the ThisWorkbook / That Workbook method Dim ThisWorkbook As String Dim ThatWorkbook As String varThisWorkbook = Activeworkbook.name 'this is Book 1 varThatWorkbook = “enter file path” ’ I assume this doesn’t change if it does then you have to define it Windows(varThisWorkbook).Activate Sheets.Add.Name = “Foo” Sheets.Add.Name = “Bar” 'You create the sheets in the destination workbook Windows(varThatWorkbook).Activate Sheets(“Foo”).Activate Cells.Select Selection.Copy Windows(varThisWorkbook).Activate Sheets(“Foo”).Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'THIS ONLY PASTES VALUES CUSTOMIZE BASED ON WHAT YOU NEED Windows(varThatWorkbook).Activate Sheets(“Bar”).Activate Cells.Select Selection.Copy Windows(varThisWorkbook).Activate Sheets(“Bar”).Select Cells.Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'THIS ONLY PASTES VALUES CUSTOMIZE BASED ON WHAT YOU NEED

#5

double post

#6

Or: Here is some lazy code: Sub Workbooks(“Book2”).Sheets(“Foo”).Copy _ After:=Workbooks(“Book1”).Sheets(“nameoflastsheet”) Workbooks(“Book2”).Sheets(“Bar”).Copy _ After:=Workbooks(“Book1”).Sheets(“nameoflastsheet”) End Sub

#7

That somehow seems more eloquent than my code. I don’t have a programming background but boy I feel stupid. Ha!

#8

thanks VA and adehbone. i’m back home and i’m going to experiment with this code, i’ll update the thread with results.