Copy cell values to new row on another sheet

Microsoft Excel 2003 (full product)
May 29, 2010 at 04:22:32
Specs: Windows XP
Hi,

I would like to write a macro in Excel 2003 that will copy the cell values of cells B2 to B7 and Cell N30 from 'Sheet1' to the next new row in 'Sheet2' (in columns A to G)

I am a complete newbie to VBA and so don't even know where to start. Can anyone help please?


See More: Copy cell values to new row on another sheet

Report •


#1
May 29, 2010 at 20:32:56
I don't have access to Excel until Monday, so I'm writing this off the top of my head without any means to test it. If it errors out, let me know what it says and I'll try to fix it - or wait until Monday and I'll make sure it works.

Option Explicit
Sub CopyRange()
Dim nextRow as Integer
'Find next empty row on Sheet2
  nextRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Sheet1!B2:B7, Transpose to Sheet2
    Sheets(1).Range("B2:B7").Copy
    Sheets("Sheet2").Range("A" & nextRow).PasteSpecial Transpose:=True
'Copy Sheet1!N20 to Column G
   Sheets(1).Range("N20").Copy Destination:=Sheets(2).Range("G" & nextRow)
End Sub


Report •

#2
May 29, 2010 at 21:19:26
Hi DerbyDad,

That's great - it worked first time with no errors! I wish I knew VBA well enough to write code off the top of my head!!

Excellent! Many thanks for the speedy and comprehensive response.

Matt


Report •

#3
May 29, 2010 at 22:26:45
re: "off the top of my head"

Well, that and a Google search to make sure I got the Transpose part right. That's just not something I do too often.

I'm glad I could help.


Report •

Related Solutions

#4
May 29, 2010 at 23:21:12
Just one last thing - I am trying to apply the PasteValues aswell as the transpose. I just added it to the line you gave me as follows, but it doesn't work. Any ideas?

Option Explicit
Sub CopyRange()
Dim nextRow as Integer
'Find next empty row on Sheet2
nextRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Sheet1!B2:B7, Transpose to Sheet2
Sheets(1).Range("B2:B7").Copy
Sheets("Sheet2").Range("A" & nextRow).PasteSpecial Transpose:=True, PasteValues:=True
'Copy Sheet1!N20 to Column G
Sheets(1).Range("N20").Copy Destination:=Sheets(2).Range("G" & nextRow)
End Sub


Report •

#5
May 29, 2010 at 23:58:36
I have got the PasteSpecial to work but have encountered another problem now I am trying the macro with my actual workbook; Cell N20 is a formula and it is not copying the value of the cell, which is what I need it to do (its not copying anything at all). Any ideas?

Sub CopyRange()
Dim nextRow As Integer
'Find next empty row on Sheet2
  nextRow = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1
'Copy Sheet1!B2:B7, Transpose to Sheet2
    Sheets(1).Range("B2:B7").Copy
    Sheets("Quotes").Range("A" & nextRow).PasteSpecial Paste:=xlPasteValues, Transpose:=True
'Copy Sheet1!N20 to Column G
   Sheets(1).Range("N20").Copy
    Sheets("Sheet2").Range("G" & nextRow).PasteSpecial Paste:=xlPasteValues
'inform user that copy has finished
   MsgBox "Quote Copied", vbInformation, "Premier Cleaning"
End Sub


Report •

#6
May 30, 2010 at 05:43:40
Your code looks right, but I can't test it since I don't have Excel.

One way to check and see it the problem is the Copy or the Paste is to single step through the code using F8.

Put your cursor anywhere in the code and pres F8. Each time you press F8 it will execute one line. After it executes Sheets(1).Range("N20").Copy click anywhere in your spreadsheet and manually do a Paste (or PasteSpecial) if it Pastes N20, then you'll know that the code copied it.

Then go back into the VBA editor and hit F8 to execute the PasteSpecial line.

Let us know what you find.


Report •


Ask Question