link data to new workbook

[Guest]

good day all,i need to send data from specific cells from an invoice
template after i have saved as in my customers name, to specific cells in a
new workbook called control.any help on this will be greatly appreciated.
thanks wynb.

 

[Guest]

If I understand you correctly, You want to copy data from a document
(Invoice) to a database type list in another worksheet? The only way I know
to do this, is to write a macro to handle this operation. The macro would
typically save your invoice, then copy specific cells to the control
spreadsheet. It would also check to find the last row of data in the control
sheet, and then move down one line, before pasting the data.

I have written a macro like this to do this, namely to take information from
an input sheet, paste it onto a payslip, and also move all the relevant
information for a specific month to a worksheet named as the month in
question. You can have this if you think it wil help you. You have to know
something about macros though, to enable you to adapt it for your own needs.

 

[Guest]

hi kassie,your macro sounds like it would work.i have some understanding of
macros.if you could post the macro i will work with it.thank you for your
assistance. wynb.

 

[Guest]

I have posted the full macro for you. the moving of data to the control
sheet is actually contained in the procedure called sub Printing(). Hope
this will help you!

Public strMonth As String

Sub Begin()

Sheets("Database").Select
Range("E20").Select
If ActiveCell.Value < 1 Then End
Range("A3").Select
Sheets("Database").Select
Range("B16").Select
strMonth = ActiveCell.Value
' Transfer details to monthly summary
If strMonth = "" Then End
Select Case strMonth
Case Is = ("January")
ActiveWindow.LargeScroll ToRight:=-1
Range("A3:X16").Select
Selection.Copy
Sheets(1).Select
Range("A3").Select
Case Is = ("February")
Range("A3:X16").Select
Selection.Copy
Sheets(2).Select
Range("A3").Select
Case Is = ("March")
Range("A3:X16").Select
Selection.Copy
Sheets(3).Select
Range("A3").Select
Case Is = ("April")
Range("A3:X16").Select
Selection.Copy
Sheets(4).Select
Range("A3").Select
Case Is = ("May")
Range("A3:X16").Select
Selection.Copy
Sheets(5).Select
Range("A3").Select
Case Is = ("June")
Range("A3:X16").Select
Selection.Copy
Sheets(6).Select
Range("A3").Select
Case Is = ("July")
Range("A3:X16").Select
Selection.Copy
Sheets(7).Select
Range("A3").Select
Case Is = ("August")
Range("A3:X16").Select
Selection.Copy
Sheets(8).Select
Range("A3").Select
Case Is = ("September")
Range("A3:X16").Select
Selection.Copy
Sheets(9).Select
Range("A3").Select
Case Is = ("October")
Range("A3:X16").Select
Selection.Copy
Sheets(10).Select
Range("A3").Select
Case Is = ("November")
Range("A3:X16").Select
Selection.Copy
Sheets(11).Select
Range("A3").Select
Case Is = ("December")
Range("A3:X16").Select
Selection.Copy
Sheets(12).Select
Range("A3").Select
Case Else
End
End Select
' Transfer details to monthly summary
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Sheets("Database").Select
Start

End Sub

Sub Start()

If ActiveCell.Value <> "" Then Transferring
Clear

End Sub

Sub Transferring()
' Copy Surname and Initials and paste on payslip
ActiveCell.Offset(0, 0).Range("A1:B1").Select
Selection.Copy
Sheets("Payslip").Select
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Copy Position and paste on payslip
Sheets("Database").Select
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payslip").Select
Range("B11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Insert payment month
Range("B13").Select
ActiveCell.Value = strMonth
'Copy appointment date and paste on payslip
Sheets("Database").Select
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payslip").Select
Range("B15").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Copy earnings and paste on payslip
Sheets("Database").Select
ActiveCell.Offset(0, 1).Range("A1:E1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payslip").Select
Range("D19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'Copy deductions and paste on payslip
Sheets("Database").Select
ActiveCell.Offset(0, 6).Range("A1:E1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payslip").Select
Range("D29").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'Copy Leave and paste on payslip
Sheets("Database").Select
ActiveCell.Offset(0, 7).Range("A11").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payslip").Select
Range("B43").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'Copy sick leave and paste on payslip
Sheets("Database").Select
ActiveCell.Offset(0, 4).Range("A1:C1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payslip").Select
Range("D43").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
'Copy salary accumulated and paste on payslip
Sheets("Database").Select
ActiveCell.Offset(0, 4).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payslip").Select
Range("B48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Copy Commission accumulated and paste on payslip
Sheets("Database").Select
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payslip").Select
Range("B49").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Copy other earnings accumulated and paste on payslip
Sheets("Database").Select
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payslip").Select
Range("B50").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Copy annual PAYE and paste on payslip
Sheets("Database").Select
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payslip").Select
Range("D48").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Copy annual UIF and paste on payslip
Sheets("Database").Select
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Payslip").Select
Range("D49").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
'Copy payment month and paste on payslip
' Sheets("Database").Select
' Range("B16").Select
' Application.CutCopyMode = False
' Selection.Copy
' Sheets("Payslip").Select
' Range("B13").Select
' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
' Application.CutCopyMode = False
Printing

End Sub

Sub Printing()
'Print payslip
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
'Transfer UIF totals for year
Sheets("Database").Select
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Transfers annual PAYE figures
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Transfer other income annual figures
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Transfer annual commission figures
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Transfer annual wages figure
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -1).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
'Transfer annual sick leave figures, and deletes current figure
ActiveCell.Offset(0, -1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
'Transfer annual leave figures, and clears current figure
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.Copy
ActiveCell.Offset(0, -3).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveWindow.SmallScroll ToRight:=-10
'Delete current deductions
ActiveCell.Offset(0, -7).Range("A1:C1").Select
Selection.ClearContents
'Clear current PAYE
ActiveCell.Offset(0, -2).Range("A1").Select
Selection.ClearContents
'Clear current earnings
ActiveCell.Offset(0, -6).Range("A1:E1").Select
Selection.ClearContents
ActiveCell.Offset(0, -4).Range("A1").Select
ActiveCell.Offset(1, 0).Select
Start

End Sub


Sub Clear()

' Clears last payslip printed

Sheets("Payslip").Select
ActiveWindow.LargeScroll Down:=-1
Range("B9:C9").Select
Selection.ClearContents
Range("B11").Select
Selection.ClearContents
Range("B13").Select
Selection.ClearContents
Range("B15").Select
Selection.ClearContents
Range("D1923").Select
Selection.ClearContents
Range("D2933").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=21
Range("B43:B46").Select
Selection.ClearContents
Range("B48:B50").Select
Selection.ClearContents
Range("D4345").Select
Selection.ClearContents
Range("D4850").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-21
Range("B9").Select
Sheets("Database").Select
Range("B16").Select
Selection.ClearContents

End Sub