This is actually more of a VBA problem but as I am busy learning VB 6 I’m sure this problem will arise again.

Here’s what I wan to do; I’ve got two separate excel workbooks, the first is a bank statement the second a batch form. In order to allocate payments from the bank account they need to be placed in batches, I already have macros assigned to form control buttons placed in the workbooks that export specified row from the bank statement to the batch. Now as I control each batch has a number that changes with each new batch and that number is displayed in the batch workbook and written in a note coulomb in the bank statement workbook, currently I am entering the batch number manually via a custom userform I created. What I’d like to do is to declare the specific range in which this unique batch number appears, the batch numbers is in the same place on all the batches as it is a master copy, and simply “paste” it in to appropriate place during runtime of the specified exporting macro.
I tried simply selecting & copying the range but because I need to activecell to offset by 1 row every time the macro runs this doesn’t work as next time the cell containing the batch number is still selected and this is then copied and pasted into the note coulomb of my bank statement workbook.

Below is the current code I am using for the cmd that governs this macro, this macro exports a row from the bankstatement workbook (“Master Compiled Sheet Medical.xls") to the batch workbook("Master Batch Medical.xls”)

Private Sub CmdMedical_Click()
Application.ScreenUpdating = False
ActiveCell.Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("Master Batch Medical.xls").Activate
ActiveWindow.WindowState = xlMaximized
ActiveCell.Select
ActiveCell.PasteSpecial
Selection.Interior.ColorIndex = xlNone
ActiveCell.Offset(0, 1).Select
ActiveWindow.WindowState = xlMinimized
Windows("Master Compiled Sheet Medical.xls").Activate
ActiveWindow.WindowState = xlMaximized
ActiveCell.Offset(0, 1).Select
ActiveCell.Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("Master Batch Medical.xls").Activate
ActiveWindow.WindowState = xlMaximized
ActiveCell.Select
ActiveCell.PasteSpecial
Selection.Interior.ColorIndex = xlNone
ActiveCell.Offset(0, 1).Select
ActiveWindow.WindowState = xlMinimized
Windows("Master Compiled Sheet Medical.xls").Activate
ActiveWindow.WindowState = xlMaximized
ActiveCell.Offset(0, 1).Select
ActiveCell.Select
Selection.Copy
ActiveWindow.WindowState = xlMinimized
Windows("Master Batch Medical.xls").Activate
ActiveWindow.WindowState = xlMaximized
ActiveCell.Select
ActiveCell.PasteSpecial
Selection.Interior.ColorIndex = xlNone
ActiveCell.Offset(1, -2).Select
ActiveWindow.WindowState = xlMinimized
Windows("Master Compiled Sheet Medical.xls").Activate
ActiveWindow.WindowState = xlMaximized
ActiveCell.Offset(0, 6).Select
ActiveWindow.WindowState = xlMinimized
Windows("Master Batch Medical.xls").Activate
ActiveWindow.WindowState = xlMaximized
Range("G5").Select
Selection.Copy 'this is where my problem starts.'
ActiveWindow.WindowState = xlMinimized
Windows("Master Compiled Sheet Medical.xls").Activate
ActiveWindow.WindowState = xlMaximized
ActiveCell.Select
ActiveCell.PasteSpecial
Selection.Font.Bold = False
With Selection.Font
.Name = "Arial"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.Offset(1, -8).Select

End
End Sub


The problem arises because as you can see Rang G5 in Master Batch Medical remains selected so next time I run the macro data from Master Compiled Sheet Medical will be pasted over the current batch number.
Range(“G5”) in the Master Batch Medical is Range I want to declare and paste in the Master Compiled Sheet Medical.

Any help or pointers would be appreciated.
Thanx