help with copying column between 2 workbooks through vba

[GJB]

Hi all,
I'm currently working on my first excel application and i'm kinda stuck at the moment. I have a form wich will get imput from the user feeding it manufactured products and it will spit out the parts used for billing. I have an excel sheet setup for this purpose wich includes a template so to speak that wil do the actual conversion. This template will be saved to a new file by date. BUT there can be many such conversions in 1 day so i need to be able to insert the column with te data for that bill included in an already saved file. I have a script setup for this purpose wich wil open the file if it exists and inserts a new column where i want it to. My problem at the moment is that i can't figure out how to paste the needed column from the file created with the macro to the file opened if the filename exists. Below is some heavily commented code wich should be self explanatory.
I hope that someone can help me because i'm getting a bit crazy from this problem

Sincerely,

GJB

Please Login or Register  to view this content.

[Glen Mettler]

This might need a little tweak but should put on the right path:
< your code>
ActiveCell.EntireColumn.Select
Selection.Insert Shift:=xlToRight
*'need code to paste sh1.range("c1:c120") into inserted column here*

'=========== my code

COL = ActiveCell.Column
Cells(1, COL).Select
Filename = "File Name to open??"
ThisSheet = ActiveWorkbook.ActiveSheet.Name
Set BaseBook = Application.ActiveWorkbook.Name
Set SourceBook = Workbooks.Open(Filename)
SourceTab = SourceBook.Worksheets("Sheet1").Index
Set SourceRange =
SourceBook.Worksheets("Sheets1").Range("C1:C120")
SourceRcount = SourceRange.Rows.Count
Set DestRange = BaseBook.Worksheets(ThisSheet).Cells(1, COL)

SourceRange.Copy DestRange
SourceBook.Close False

Hope this helps

Glen

[GJB]

Thanks Glen this works like a charm, but now i've run into another problem wich wich you could maybe help me. Within the folowing piece of code i'd like to save the formula's for column D, is this possible?

Windows(varThiswb).Activate
Set sh = Blad6
sh.Cells.Copy
Windows(varNewwb).Activate
Set sh1 = Worksheets.Add(After:=Worksheets(Worksheets.Count))
ActiveSheet.Name = "dagoverzicht" & j
sh1.Range("A1").PasteSpecial Paste:=xlValues
sh1.Range("A1").PasteSpecial Paste:=xlFormats
sh1.Range("c1:c120").Copy
Names.Add Name:="totaal", RefersTo:=Range("D1:D120")

Thanks a lot for the help so far,

Sincerely,

GJB