GJB
Board Regular
- Joined
- Mar 17, 2006
- Messages
- 183
Hi all,
I've been working on my first excel vba project for some time now and so far i've been able to create my code without many problems thanks to this board . But now i have a problem that i can't figure out. I have made a save print function to automatically print and save a workbook. I've got a test in there to see if a filename exists and if it does i have to copy 1 column from the created file in a certain place in the existing file.
The file opens up okay and i can isert the column in the place i want but i can't seem to figure out how to paste the information i want in the newly created column.
I hope someone can help me figure this out.
Thanks in advance,
GJB
I've been working on my first excel vba project for some time now and so far i've been able to create my code without many problems thanks to this board . But now i have a problem that i can't figure out. I have made a save print function to automatically print and save a workbook. I've got a test in there to see if a filename exists and if it does i have to copy 1 column from the created file in a certain place in the existing file.
The file opens up okay and i can isert the column in the place i want but i can't seem to figure out how to paste the information i want in the newly created column.
I hope someone can help me figure this out.
Thanks in advance,
GJB
Rich (BB code):
Public Sub saveprint()
Dim i As String, j As String, k As String
Dim sh As Worksheet 'bron sheet
Dim sh1 As Worksheet 'doel sheet
Dim Thiswb As Workbook
Dim Newwb As Workbook
Dim Savedwb As Workbook
Dim varThiswb As String 'huidige workbook
Dim varNewwb As String 'nieuwe workbook
Dim varSavedwb As String 'opgeslagen workbook
Dim fPath As String
' Pad naar de keurrapporten
'fPath = "\\Server01\Company\marcel\Skyline versie 3\keurrapporten\"
fPath = "d:\test\"
Application.ScreenUpdating = False
' formatteer variabelen voor gebruik in opslag van bestand
i = Format(Me.Controls("datum").Text, "mmm")
j = Format(Me.Controls("datum").Text, "dd-mm")
k = Me.Controls("datum").Text
' kijk of maand map bestaat zo niet maak aan
If Dir(fPath & i, vbDirectory) = "" Then
MkDir (fPath & i)
End If
'declareer namen van de workbooks in variabelen
varThiswb = ThisWorkbook.Name
Set Newwb = Workbooks.Add(1)
varNewwb = ActiveWorkbook.Name
Application.EnableEvents = False
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")
Application.DisplayAlerts = False
Sheets("Blad1").Delete
Application.DisplayAlerts = True
If Dir(fPath & i & "\" & k & ".xls") <> "" Then
varSavedwb = fPath & i & "\" & k & ".xls"
Application.Workbooks.Open varSavedwb
Selection.Find(What:="totaal", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.EntireColumn.Select
Selection.Insert Shift:=xlToRight
'need code to paste sh1.range("c1:c120") into inserted column here
ActiveWorkbook.Save
Else
ActiveWorkbook.SaveAs Filename:=fPath & i & "\" _
& k & ".xls", FileFormat:=xlNormal, Password:="", _
WriteResPassword:="", ReadOnlyRecommended:=False, CreateBackup:=False
End If
'Laat de bstandsnaam zien en print en sluit het document
MsgBox ActiveWorkbook.FullName
ActiveWorkbook.PrintOut Copies:=1
ActiveWorkbook.Close
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub