Copy column (formulas & format) from workbook to another

[Kmahraz]

Hey guys,
looking for a way to copy several columns in different sheets from one work book to another.
The columns contain formulas that i would like to keep; but they need to reference the new destination work book and not the source.
Also I would like to keep the format if possible.
Private Sub CommandButton1_Click()
Dim sourceColumn As Range, targetColumn As Range

Set sourceColumn = Workbooks("Chandoo 1st post.xls").Worksheets("Asia Prices from PUZJ").Columns("AC")
Set targetColumn = Workbooks("KM-test 2.xls").Worksheets("Asia Prices from PUZJ").Columns("AC")

sourceColumn.Copy Destination:=targetColumn
End Sub

 

[Luke M]

Welcome to the forum!

I think you can just have your code remove the external workbook reference after you copy. Your current formulas in your example are broken, and I don't have the sheets in question, so I wasn't able to test.

Private Sub CommandButton1_Click()
    Dim sourceColumn As Range, targetColumn As Range

    Set sourceColumn = Workbooks("Chandoo 1st post.xls").Worksheets("Asia Prices from PUZJ").Columns("AC")
    Set targetColumn = Workbooks("KM-test 2.xls").Worksheets("Asia Prices from PUZJ").Columns("AC")
   
    sourceColumn.Copy Destination:=targetColumn
    targetColumn.Replace what:="[*]", replacement:="", lookat:=xlFormula
End Sub

 

[Kmahraz]

Hello Luke,
Thank you so much for the assist, since i can't share the files in questions i created a new one ((TARGET)) and ((SOURCE)) that you can find attached.
The last time i used VBA was in college, almost 12 years ago so i can use some help.
Please see files attached, what i'm trying to accomplish is:

1. Copy column M, N from Worksheet " DATA TABLE" located in workbook " SOURCE" Insert into M & N FROM Worksheet "T.TABLE" located in workbook " TARGET" ( formulas need to reference new workbook (TARGET)
2. Copy column F from from Worksheet " LOOKUP" located in workbook " SOURCE" Insert into F FROM Worksheet "T.LOOKUP" located in workbook "TARGET"
3. Would like to keep the same format for the M,N & F
4. Formulas need to reference the target workbook

thanks again for the assist.
Best,
Karim

 

[Luke M]

The formulas in SOURCE workbook reference a sheet called LOOKUP, but in TARGET workbook there is no sheet with this name. In your actual setup, are the sheet names exactly the same, or is there a change? If the latter, then this is a much more difficult problem.

For the latter question concerning the LOOKUP sheet, the formula doesn't reference any sheets, so you can do a straight copy:

Sub SimpleCopy()
ThisWorkbook.Worksheets("LOOKUP").Range("F:F").Copy
Workbooks("TARGET.xls").Worksheets("T.LOOKUP").Range("F:F").Insert
Application.CutCopyMode = False
End Sub

 

[Kmahraz]

Hi Luke, the CODE provided work great for the F column.
Thanks for pointing that, the sheet are named the same, i changed the names of the sheets thinking it will help T.LOOKUP was just LOOKUP.
For my case all sheets are going to be named the same from the TARGET to the SOURCE.
Please let me know if you need any additional clarification.
Regards,
Karim

 

[Luke M]

Then code for first part would look like:

Sub Button1_Click()
    Dim sourceColumn As Range, targetColumn As Range

    Set sourceColumn = ThisWorkbook.Worksheets("DATA TABLE").Range("M:N")
    Set targetColumn = Workbooks("TARGET.xls").Worksheets("T.TABLE").Range("M:N")
   
    sourceColumn.Copy Destination:=targetColumn
    targetColumn.Replace what:="[*]", replacement:=""

End Sub

Be sure to change the names of any workbooks/worksheets as needed to match your actual file(s).

 

[Kmahraz]

Thanks again Luke! The code work perfectly.
I have only a couple of weeks to complete this project and it's causing me some headaches, trying to copy several columns from a different workbook to another without the references, so that all formulas reference the new workbook..

If possible I have a couple of additional questions:

1. In case I have several columns from different sheet, do I only duplicate the code provided?
2. If I need to copy only column "M" and lets say "AC" from the same sheet is it better to duplicate the code or is there another way?
3. The target workbook name change every month, for example: last month it was "2015-07 TARGET" next month it's going to be 2015-08 TARGET"
   1. How do I make sure it's looking at the latest file without having them opening the VBA code and changing the target file?
   2. Is it possible to have the VBA request the file name every time the macro is run?
4. In general i'm trying to see how i can limit the interaction with this spreadsheet/ workbook to avoid having other associates to copy and paste manually and messing-up the prices/ cost since it did happen in the past.

I appreciate the help.
Best,
Karim

 

[Luke M]

For the different columns, you would need to repeat the lines of code that do the actual copying. If the columns in question have inter-sheet formulas, then you'll also need to repeat the Replace command line.

For the workbook, I'd add this to the beginning

'Example of opening a workbook and assigning it to a variable
Dim wbDest As Workbook

With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Pick where to copy the formulas"
    .Show
   
    If .SelectedItems.Count = 0 Then
        'User cancelled
        Exit Sub
    Else
        'Open the workbook
        Set wbDest = Workbooks.Open(.SelectedItems(1))
    End If
End With

'Later on...
Set targetColumn = wbDest.Worksheets("LOOKUP").Range("L:L")
'...and so forth

 

[Kmahraz]

Thanks Luke, I will give it a try after my meeting and keep you posted.
Regards,
Karim

 

[Kmahraz]

Hey Luke,
Thanks for the reply...I am so close to getting this little project complete and now it's unreal. Thanks a Bunch.
I still have a very minor problem though and I've tried rewriting some of the code for a pop-up box to input a password and maybe a message box stating operation complete.
Can you please assist? here's is my final code:
Sub Button1_Click()
'Example of opening a workbook and assigning it to a variable
Dim wbDest As Workbook

With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
.Title = "Pick where to copy the formulas"
.Show

If .SelectedItems.Count = 0 Then
'User cancelled
Exit Sub
Else
'Open the workbook
Set wbDest = Workbooks.Open(.SelectedItems(1))
End If
End With

'EURO HPMP COLUMN

ThisWorkbook.Worksheets("EURO HPMP").Range("L:M").Copy
Workbooks("TARGET-TEST.xls").Worksheets("EURO HPMP").Range("L:M").Insert
Application.CutCopyMode = False

'Europe HPMP COLUMN

ThisWorkbook.Worksheets("Europe").Range("Y:Z").Copy
Workbooks("TARGET-TEST.xls").Worksheets("Europe").Range("Y:Z").Insert
Application.CutCopyMode = False


'NAFTA HPMP COLUMN

ThisWorkbook.Worksheets("NAFTA HPMP").Range("L:M").Copy
Workbooks("TARGET-TEST.xls").Worksheets("NAFTA HPMP").Range("L:M").Insert
Application.CutCopyMode = False


'NAFTA Prices COLUMN

ThisWorkbook.Worksheets("NAFTA Prices").Range("X:X").Copy
Workbooks("TARGET-TEST.xls").Worksheets("NAFTA Prices").Range("X:X").Insert
Application.CutCopyMode = False


'P&P Prices COLUMN

ThisWorkbook.Worksheets("P&P").Range("O:O").Copy
Workbooks("TARGET-TEST.xls").Worksheets("P&P").Range("O:O").Insert
Application.CutCopyMode = False

'Asia Prices from PUZJ Prices COLUMN

Dim sourceColumn As Range, targetColumn As Range

Set sourceColumn = ThisWorkbook.Worksheets("Asia Prices from PUZJ").Range("AC:AC")
Set targetColumn = Workbooks("TARGET-TEST.xls").Worksheets("Asia Prices from PUZJ").Range("AC:AC")

sourceColumn.Copy Destination:=targetColumn
targetColumn.Replace what:="[*]", replacement:=""

End Sub

 

[Luke M]

Here's the code with amendments. Current password is set to be Pass1234, but you can change to whatever you want.

Sub Button1_Click()
'Example of opening a workbook and assigning it to a variable
Dim wbDest As Workbook
Dim sourceColumn As Range, targetColumn As Range
Const myPass As String = "Pass1234"

'Password check
If InputBox("What is the password?", "Password", "****") <> myPass Then Exit Sub

Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Pick where to copy the formulas"
    .Show
   
    If .SelectedItems.Count = 0 Then
        'User cancelled
        Exit Sub
    Else
        'Open the workbook
        Set wbDest = Workbooks.Open(.SelectedItems(1))
    End If
End With

'EURO HPMP COLUMN
ThisWorkbook.Worksheets("EURO HPMP").Range("L:M").Copy
wbDest.Worksheets("EURO HPMP").Range("L:M").Insert

'Europe HPMP COLUMN
ThisWorkbook.Worksheets("Europe").Range("Y:Z").Copy
wbDest.Worksheets("Europe").Range("Y:Z").Insert

'NAFTA HPMP COLUMN
ThisWorkbook.Worksheets("NAFTA HPMP").Range("L:M").Copy
wbDest.Worksheets("NAFTA HPMP").Range("L:M").Insert

'NAFTA Prices COLUMN
ThisWorkbook.Worksheets("NAFTA Prices").Range("X:X").Copy
wbDest.Worksheets("NAFTA Prices").Range("X:X").Insert

'P&P Prices COLUMN
ThisWorkbook.Worksheets("P&P").Range("O:O").Copy
wbDest.Worksheets("P&P").Range("O:O").Insert

'Asia Prices from PUZJ Prices COLUMN
Set sourceColumn = ThisWorkbook.Worksheets("Asia Prices from PUZJ").Range("AC:AC")
Set targetColumn = wbDest.Worksheets("Asia Prices from PUZJ").Range("AC:AC")

sourceColumn.Copy Destination:=targetColumn
targetColumn.Replace what:="[*]", replacement:=""
Application.CutCopyMode = False
Application.ScreenUpdating = True

'Let user know we finished
MsgBox "Done!"

End Sub

 

[Kmahraz]

Thanks Luke, such a simple solution, awesome.
You should be a Master and not a Ninja
Best,
Karim