VBA Command Button Coding

[madski]

Hello,

I am looking for code that can be used in conjunction with the ActiveX Command Button. I need help with coding that will copy a range of cells on one spreadsheet and paste into the next blank cell on another spreadsheet. I am painfully new to this and have been trying to find a solution to no avail. Any help would be greatly appreciated!

 

[My Aswer Is This]

Hello,

I am looking for code that can be used in conjunction with the ActiveX Command Button. I need help with coding that will copy a range of cells on one spreadsheet and paste into the next blank cell on another spreadsheet. I am painfully new to this and have been trying to find a solution to no avail. Any help would be greatly appreciated!

You used the term "spreadsheet". That is a term Excel used in the 1980's. We now use the terms:
Workbook and Worksheet
So are you wanting to copy from one Worksheet to another Worksheet in the same Workbook.
Or from one Workbook to another Workbook.
And for us to show you a example of how to do that. We would need more specific details.
Like we need to know copy what range from what Worksheet and paste into what Worksheet.
And if you actually want to copy from Workbook to another Workbook we would need to have a lot more details.

 

[Zyndstoff]

Private Sub CommandButton1_Click()
  Sheets("Sheet1").Range("B3:B8").Copy Sheets("Sheet2").Range("A1")
End Sub

Presuming it is just one Workbook with different sheets:
Sheet1 = Name of source sheet
Sheet2 = Name of destination sheet
Range("B3:B8") = Change B3:B8 to the range you want to copy
Range("A1") = Change A1 to the left topmost cell you want the data to be placed

 

[madski]

You used the term "spreadsheet". That is a term Excel used in the 1980's. We now use the terms:
Workbook and Worksheet
So are you wanting to copy from one Worksheet to another Worksheet in the same Workbook.
Or from one Workbook to another Workbook.
And for us to show you a example of how to do that. We would need more specific details.
Like we need to know copy what range from what Worksheet and paste into what Worksheet.
And if you actually want to copy from Workbook to another Workbook we would need to have a lot more details.

Thank you for the clarification, that was an overlook on my part! So essentially I am looking to copy a range of cells on worksheet “REPORTS” and paste it into the next blank cell on the worksheet named “JOURNAL”. There are several different ranges that I will have to run this for, the first being E2:H32. I am trying to special paste (U) to paste it in picture format for the desired formatting effect, if that is even possible. I have attached photos for you reference. Thank you in advance!

 

[My Aswer Is This]

Thank you for the clarification, that was an overlook on my part! So essentially I am looking to copy a range of cells on worksheet “REPORTS” and paste it into the next blank cell on the worksheet named “JOURNAL”. There are several different ranges that I will have to run this for, the first being E2:H32. I am trying to special paste (U) to paste it in picture format for the desired formatting effect, if that is even possible. I have attached photos for you reference. Thank you in advance!

You said:
There are several different ranges that I will have to run this for, the first being E2:H32.
To help you we will need all these specific details.
And I assume you mean you want to copy formatting.
Not sure why picture format if there is such a thing.

 

[madski]

Thank you for the clarification, that was an overlook on my part! So essentially I am looking to copy a range of cells on worksheet “REPORTS” and paste it into the next blank cell on the worksheet named “JOURNAL”. There are several different ranges that I will have to run this for, the first being E2:H32. I am trying to special paste (U) to paste it in picture format for the desired formatting effect, if that is even possible. I have attached photos for you reference. Thank you in advance!

You said:
There are several different ranges that I will have to run this for, the first being E2:H32.
To help you we will need all these specific details.
And I assume you mean you want to copy formatting.
Not sure why picture format if there is such a thing.

When I say there are several different ranges, I mean that there are several different command buttons that I will have to run this for, meaning that each command button will correlate to its own range of cells. Sorry for the confusion.

I’m trying to format as picture (PNG), which is an option via paste special when manually done. I’m doing this as I am trying to paste a range of cells that has anywhere between 4 to 6 columns into a singular column. Mainly to retain the formatting. If you know another option or workaround, input would be appreciated.

Using Record Macro, here is what I was able to get:

Sub YELLOW1()
'
' Yellow1 Macro
'

'
Range("F2:I32").Select
Selection.Copy
Sheets("JOURNAL").Select
Range("C10").Select
ActiveSheet.Pictures.Paste.Select
ActiveSheet.Shapes.Range(Array("Picture 6")).Select
End Sub

When run like this as a Macro, it has the desired effect, although I changed "Range("C10").Select" to "Range("C" & Rows.Count).End(xlUp).Offset(1).Select" to account for the next blank cell. The issue I'm running into is when I try to copy the code into the Command Button Sub. When I reach "ActiveSheet.Pictures.Paste.Select" I get run=time error 1004, application defined or object defined error.

 

[Zyndstoff]

I tried this, and it worked for me:

Private Sub CommandButton1_Click()
  Application.CutCopyMode = False
  Range("F2:I32").Select
  Selection.Copy
  Worksheets("JOURNAL").Select
  Worksheets("JOURNAL").Range("C" & Rows.Count).End(xlUp).Offset(1).Select
  ActiveSheet.Pictures.Paste
End Sub

Funny thing I encountered is that the code will not work when I use "Sheets(...).select", but does perform perfectly when I substitute the term "Sheets" with "Worksheets"... don't know why.

 

[madski]

I tried this, and it worked for me:

Private Sub CommandButton1_Click()
  Application.CutCopyMode = False
  Range("F2:I32").Select
  Selection.Copy
  Worksheets("JOURNAL").Select
  Worksheets("JOURNAL").Range("C" & Rows.Count).End(xlUp).Offset(1).Select
  ActiveSheet.Pictures.Paste
End Sub

Funny thing I encountered is that the code will not work when I use "Sheets(...).select", but does perform perfectly when I substitute the term "Sheets" with "Worksheets"... don't know why.

Thank you so much! This one worked for me. I appreciate the help.

 

[Zyndstoff]

Glad I could solve that problem. Have fun.

I added one line of code to push the first empty cell one row below after pasting:

Private Sub CommandButton1_Click()
  Application.CutCopyMode = False
  'Range("F2:I32").Select
  Selection.Copy
  Worksheets("JOURNAL").Select
  Worksheets("JOURNAL").Range("C" & Rows.Count).End(xlUp).Offset(1) = " "
  Worksheets("JOURNAL").Range("C" & Rows.Count).End(xlUp).Offset(1).Select
  ActiveSheet.Pictures.Paste
End Sub

 

[My Aswer Is This]

In case you want to know. Excel has a built in way to Copy a Range and then it's in the clipboard
And you can paste it where you want.
On the far left corner on the Menu Bar you should see a Icon that says Copy.
You then have the choice of copy or copy as picture.
Now that it's in the clip board just paste it where you want.