Need help debugging

[jhpaul1220]

So for work I need to create an excel spread sheet that uses a button/macro to take data from a form on Sheet 1 and copies it into a table on Sheet 2 in the appropriate places. Each time you use the button it needs to put the data in a new line on the table. I think I'm almost there but I can't quite get it to work and I have no idea why at this point. Any help or guidance you could provide would be amazing!

Private Sub CommandButton1_Click()
'
'
' Macro()
'
Sheet1.Range("B2").Copy
Sheet2.Range("B6").Paste
Sheet1.Range("B3").Copy
Sheet2.Range("C6").Paste
Sheet1.Range("B4").Copy
Sheet2.Range("E6").Paste
Sheet1.Range("C30").Copy
Sheet2.Range("F6").Paste
Sheet1.Range("C27").Copy
Sheet2.Range("F6").Paste
Sheet1.Range("C12").Copy
Sheet2.Range("G6").Paste
Sheet1.Range("G29").Copy
Sheet2.Range("H6").Paste

With Sheet2
Sheet2.Range("B6:H6").Copy
Cells(.UsedRange.Columns(1).Rows.Count + 1, 1).Paste
End With



End Sub

 

[JoeMo]

I still seem to be getting the same error. Any other thoughts?

See post #9 that I posted while you were submitting post #10.

 

[jhpaul1220]

You can't use .Paste it has to be .PasteSpecial in the construct you are using. But, there's an easier way, you can use this construct for all the copy/paste that is at the top of your code:

Sheet1.Range("B2").Copy Sheet2.Range("B6")
Sheet1.Range("B3").Copy Sheet2.Range("C6")
Sheet1.Range("B4").Copy Sheet2.Range("E6")
Sheet1.Range("C30").Copy Sheet2.Range("F6")
Sheet1.Range("C27").Copy Sheet2.Range("F6")
Sheet1.Range("C12").Copy Sheet2.Range("G6")
Sheet1.Range("G29").Copy Sheet2.Range("H6")

This is the construct: Sheet1.Range("Some range").Copy destination:= Sheet2.Range("Some range")

So I changed it liked you said but this "Run Time error '9' Subscript out of range" keeps showing up. Thank you all for your help by the way. You're saving a young interns life right now.

 

[vavs]

Try this

Sub macro1()

Dim LR

Worksheets("Sheet2").Activate
LR = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row + 1


Sheet1.Range("B2").Copy Sheet2.Range("B" & LR)
Sheet1.Range("B3").Copy Sheet2.Range("C" & LR)
Sheet1.Range("B4").Copy Sheet2.Range("E" & LR)
Sheet1.Range("C30").Copy Sheet2.Range("F" & LR)
Sheet1.Range("C27").Copy Sheet2.Range("F" & LR)
Sheet1.Range("C12").Copy Sheet2.Range("G" & LR)
Sheet1.Range("G29").Copy Sheet2.Range("H" & LR)


End Sub

 

[jhpaul1220]

Hopefully this is the last questions. Again thank you all. So is that the entire code or does that remain with in the "Private Sub" and "End Sub"


Like this:
Private Sub CommandButton1_Click()
'
'
' Macro()
'
Sub macro1()


Dim LR


Worksheets("Sheet2").Activate
LR = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row + 1




Sheet1.Range("B2").Copy Sheet2.Range("B" & LR)
Sheet1.Range("B3").Copy Sheet2.Range("C" & LR)
Sheet1.Range("B4").Copy Sheet2.Range("E" & LR)
Sheet1.Range("C30").Copy Sheet2.Range("F" & LR)
Sheet1.Range("C27").Copy Sheet2.Range("F" & LR)
Sheet1.Range("C12").Copy Sheet2.Range("G" & LR)
Sheet1.Range("G29").Copy Sheet2.Range("H" & LR)




End Sub



End Sub

Or this:
Sub macro1()

Dim LR

Worksheets("Sheet2").Activate
LR = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row + 1


Sheet1.Range("B2").Copy Sheet2.Range("B" & LR)
Sheet1.Range("B3").Copy Sheet2.Range("C" & LR)
Sheet1.Range("B4").Copy Sheet2.Range("E" & LR)
Sheet1.Range("C30").Copy Sheet2.Range("F" & LR)
Sheet1.Range("C27").Copy Sheet2.Range("F" & LR)
Sheet1.Range("C12").Copy Sheet2.Range("G" & LR)
Sheet1.Range("G29").Copy Sheet2.Range("H" & LR)


End Sub

 

[vavs]

If you want it to perform the function when you click the button, then this is what you need. Otherwise you will have a macro within a macro and not accomplish anything.

Private Sub CommandButton1_Click()


Dim LR


Worksheets("Sheet2").Activate
LR = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row + 1




Sheet1.Range("B2").Copy Sheet2.Range("B" & LR)
Sheet1.Range("B3").Copy Sheet2.Range("C" & LR)
Sheet1.Range("B4").Copy Sheet2.Range("E" & LR)
Sheet1.Range("C30").Copy Sheet2.Range("F" & LR)
Sheet1.Range("C27").Copy Sheet2.Range("F" & LR)
Sheet1.Range("C12").Copy Sheet2.Range("G" & LR)
Sheet1.Range("G29").Copy Sheet2.Range("H" & LR)




End Sub