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]

What exactly can you not quite get to work - are you getting an error, if so on what line and what's the error?

 

[jhpaul1220]

So when I try to run it, it says "Run Time Error "348" Object does not support this property or method" and then highlights the second line. What I don't understand is that that part of it seemed to work fine earlier.

 

[rs2k]

You are also pasting into the same call twice;

Sheet1.Range("C30").Copy
 Sheet2.Range("F6").Paste
 Sheet1.Range("C27").Copy
 Sheet2.Range("F6").Paste

 

[vavs]

If I understand this correctly, you want to put the cells that you have copied into the last row available on Sheet2? If that is the case, you can find the last row in the sheet and then use that as a variable when pasting. Here is what I do:

Dim LR as Integer


Worksheets("Sheet2").Activate
LR = lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row "B" is the column that has data in it. It can be whatever you want.

Then in the body of the copy and paste:

Sheet1.Range("B2").Copy
Sheet2.Range(B" & LR).Paste
.
.
.

End sub

Now you will put the information on the next open row in Sheet2

 

[jhpaul1220]

If I understand this correctly, you want to put the cells that you have copied into the last row available on Sheet2? If that is the case, you can find the last row in the sheet and then use that as a variable when pasting. Here is what I do:

Dim LR as Integer


Worksheets("Sheet2").Activate
LR = lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row "B" is the column that has data in it. It can be whatever you want.

Then in the body of the copy and paste:

Sheet1.Range("B2").Copy
Sheet2.Range(B" & LR).Paste
.
.
.

End sub

Now you will put the information on the next open row in Sheet2

So then where is says (B" & LR) do I replace B with the location I want to copy that information too?

 

[jhpaul1220]

This is what I have now but I'm getting a "Run Time error '9' Subscript out of range"

Private Sub CommandButton1_Click()
'
'
' Macro()
'
Dim LR As Integer


Worksheets("Sheet2").Activate
LR = lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row
Sheet1.Range("B2").Copy
Sheet2.Range(B6 & LR).Paste
Sheet1.Range("B3").Copy
Sheet2.Range(C6 & LR).Paste
Sheet1.Range("B4").Copy
Sheet2.Range(D6 & LR).Paste
Sheet1.Range("C30").Copy
Sheet2.Range(E6 & LR).Paste
Sheet1.Range("C27").Copy
Sheet2.Range(F6 & LR).Paste
Sheet1.Range("C12").Copy
Sheet2.Range(G6 & LR).Paste
Sheet1.Range("G29").Copy
Sheet2.Range(H6 & LR).Paste



End Sub

 

[vavs]

I messed up. Use the first line in place of the second line.

LR = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

LR = lastRow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row

 

[JoeMo]

So when I try to run it, it says "Run Time Error "348" Object does not support this property or method" and then highlights the second line. What I don't understand is that that part of it seemed to work fine earlier.

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")

 

[jhpaul1220]

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