Solved Copying a row to new sheet.

February 3, 2014 at 02:47:54
Specs: Windows 7
Hi

I am asking for help as i am not a programmer and have hit a wall with my VBA development

I am trying to copy a row of data in columms A to I from the active sheet to a new sheet. The row of data to be copied is wherever the active cell is. This can be anywhere on the sheet as is dependent on the user.

The active sheet is called "enquiries" and the sheet to move the data to is called "open contracts"

The data should always be put into row starting A6 and pasted as a value.

Thank you for helping.


See More: Copying a row to new sheet.

Report •

✔ Best Answer
February 3, 2014 at 10:40:45
Please take this posting tip in the spirit it is intended...

You should try and post as much detail related to your requirements as possible. Since you said "The data should always be put into row starting A6" that's what the code was written to do.

Had you said "The data should always be put into row starting A6 and the existing data in the Open Contracts sheet should be shifted down one row", you would have gotten something like this:

Sub CopyData()
Dim copyRange As String
  With Application
    .ScreenUpdating = False
    .CutCopyMode = False
  End With
'Insert Row at Open Contracts Row 6
  Sheets("Open Contracts").Range("$A$6").EntireRow.Insert
'Determine Address of Range to be Copied
    copyRange = Range("$A" & ActiveCell.Row & _
                     ":$I" & ActiveCell.Row).Address
'Copy/PasteSpecial Values
    Range(copyRange).Copy
       Sheets("Open Contracts").Range("$A$6").PasteSpecial Paste:=xlPasteValues
'Clear the clipboard
    Application.CutCopyMode = False
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 3, 2014 at 06:42:11
The following code assumes that the Enquiries sheet is active when the code is run. If not, some additional lines of code will be required.

Sub CopyData()
Dim copyRange As String
Application.ScreenUpdating = False
'Determine Address of Range to be Copied
    copyRange = Range("$A" & ActiveCell.Row & _
                     ":$I" & ActiveCell.Row).Address
'Copy/PasteSpecial Values
    Range(copyRange).Copy
       Sheets("Open Contracts").Range("$A$6").PasteSpecial Paste:=xlPasteValues
'Clear the clipboard
    Application.CutCopyMode = False
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 3, 2014 at 08:24:19
Thank you very much, it has worked nicely!


Report •

#3
February 3, 2014 at 08:58:58
Hi again

I just noticed a small problem.

If i copy two lines of information from enquiries to contracts, the second line overwrites the first.

Would you kindly adjust the code so it adds a new line in range A6 on each activation so i keep all the information transfered.

thank you very much iin advance.


Report •

Related Solutions

#4
February 3, 2014 at 10:40:45
✔ Best Answer
Please take this posting tip in the spirit it is intended...

You should try and post as much detail related to your requirements as possible. Since you said "The data should always be put into row starting A6" that's what the code was written to do.

Had you said "The data should always be put into row starting A6 and the existing data in the Open Contracts sheet should be shifted down one row", you would have gotten something like this:

Sub CopyData()
Dim copyRange As String
  With Application
    .ScreenUpdating = False
    .CutCopyMode = False
  End With
'Insert Row at Open Contracts Row 6
  Sheets("Open Contracts").Range("$A$6").EntireRow.Insert
'Determine Address of Range to be Copied
    copyRange = Range("$A" & ActiveCell.Row & _
                     ":$I" & ActiveCell.Row).Address
'Copy/PasteSpecial Values
    Range(copyRange).Copy
       Sheets("Open Contracts").Range("$A$6").PasteSpecial Paste:=xlPasteValues
'Clear the clipboard
    Application.CutCopyMode = False
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#5
February 4, 2014 at 00:01:59
Thank you, message recieved. Very much appreciate the help.

Report •

Ask Question