Copy paste macro help

Danron

Board Regular
Joined
Jan 27, 2009
Messages
74
I am trying to get my macro to look at cell C6 and if "1847VM" is in the cell then it will...
Copy E11 and Paste that on Sheet1 in the first open space in Column B starting with C15 and going down from there.
Copy A25:A35 and Paste on Sheet1 in first open space in Column C starting with C15 and going down from there.
Copy D25:D35 and Paste on Sheet1 in the first open space in Column D starting with D15 and going down from there.

Here is what I have so far but I think it needs alot of tweeks.
Thanks in advance

Code:
Private Sub CommandButton1_Click()
If Target.Column = 3 And Target.Value <> "1847WM" Then
MsgBox Target.Offset(0, -6).Value & " " & Target.Offset(0, -5).Value & " moved to Sheet1."
    Application.EnableEvents = False
    With Target.EntireRow
        .Copy Destination:=Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
        .Delete
    End With
    Application.EnableEvents = True
    
End If
End Sub
 

Some videos you may like

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Danron

Board Regular
Joined
Jan 27, 2009
Messages
74
Also the main sheet, where the command button is located, is on "OliverForm" and is being pasted onto "Sheet1".

So the macro will look at cell C6 and if "1847VM" is in the cell then it will...

Copy E11 and Paste that on Sheet1 in the first open space in Column C starting with C15 and going down from there.
Copy A25:A35 and Paste on Sheet1 in first open space in Column C starting with C15 and going down from there.
Copy D25:D35 and Paste on Sheet1 in the first open space in Column D starting with D15 and going down from there.
 

Danron

Board Regular
Joined
Jan 27, 2009
Messages
74
I want to copy the data in E11 (in worksheet OliverForm) and have it paste in C15 (in sheet1).
 
L

Legacy 146544

Guest
Here mate, took the time and liberty to make this for you. Copy paste this below code into a seperate module in your workbook. Connect the Sub CopyCells() with your butten, et voila.

Option Explicit
Sub CopyCells()
Dim x As Integer
If Sheets("OliverForm").Range("C6").Value = "1847VM" Then
Sheets("OliverForm").Range("E11").Copy
Sheets("sheet1").Activate
x = FirstEmptyCellDown(15, 3)
Sheets("sheet1").Cells(15 + x, 3).PasteSpecial
Sheets("Oliverform").Activate
Sheets("Oliverform").Range("A25:A35").Copy
Sheets("sheet1").Activate
x = FirstEmptyCellDown(15, 3)
Sheets("sheet1").Cells(15 + x, 3).PasteSpecial
Sheets("Oliverform").Activate
Sheets("Oliverform").Range("D25:D35").Copy
Sheets("sheet1").Activate
x = FirstEmptyCellDown(15, 4)
Sheets("sheet1").Cells(15 + x, 4).PasteSpecial
End If
End Sub
Function FirstEmptyCellDown(Row As Integer, Col As Integer)
Dim counter As Integer
Dim rowsdown As Integer
counter = 0
Do
If Sheets("sheet1").Cells(Row + counter, Col).Value = "" Then
rowsdown = Sheets("sheet1").Cells(Row + counter, Col).Row - Row
Exit Do
Else
counter = counter + 1
End If
Loop
FirstEmptyCellDown = rowsdown
End Function

Let me know if it works for you. Regards
 

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,652
Here ya go:

Code:
    Sheets("OliverForm").Select
    Range("E11").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("C15").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A1").Select

That should do it. If you want to leave it on cell C15 leave out Range A1 just deleted that line.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,688
Messages
5,597,546
Members
414,154
Latest member
thevaper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top