Copy and paste to another worksheet

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
77
Office Version
  1. 365
Platform
  1. Windows
Hi all

I'm wondering if I could ask for any help. I have a simple workbook where one of the sheets (named Master) has a designed form where the cells have drop down menu inputs. I have written a vba code so that when a "Submit" button is pressed on the "Master" sheet it copies this inputted data to a sheet called "DATABASE" (in the same workbook). The vba code then clears the "Master" sheet and saves it. The vba pastes the contents on the "DATASHEET" across columns, so in an order.

At present there are several separate cells and then a block of cells that I would like to copy the data from. If I have the vba code copy from just the block ("B6:I16") for example it works, copies the data and then clears the content from the "Master" form. If I add another "single cell" reference, for example ("C2, B6:I16") it reports an error "Run-time error '1004': This action won't work on multiple selections". The code I've written is.....

Id really appreciate any help with this

Trevor

VBA Code:
Sub copy()
Sheets("Master").Range("B6:I16").copy
Sheets("DATASHEET").Select

Dim LastRow As Long
LastRow = Sheets("DATASHEET").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row

ActiveSheet.Range("A1").Cells(LastRow, 1).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Worksheets("Master").Range("C2,F2,C6:I16").ClearContents

ActiveWorkbook.Save
Sheets("Master").Select

End Sub
VBA Code:
 
Last edited:

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,116
Office Version
  1. 2013
Platform
  1. Windows
Try this:
VBA Code:
Sub Clear_Range()
'Modified 7/2/2022  10:17:01 AM  EDT

With Worksheets("Master")
    .[C2,F2,C6:I16].ClearContents
End With
End Sub
 

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
77
Office Version
  1. 365
Platform
  1. Windows
Try this:
VBA Code:
Sub Clear_Range()
'Modified 7/2/2022  10:17:01 AM  EDT

With Worksheets("Master")
    .[C2,F2,C6:I16].ClearContents
End With
End Sub

The clear command of the vba works fine - its the copy part that fails. If I leave the vba as....
VBA Code:
Sub copy()
Sheets("Master").Range("B6:I16").copy
Sheets("DATASHEET").Select
VBA Code:

Then it works as it copies that range to the "DATASHEET" If I want to copy the data from two other cells (C2 and F2) and the range "B6:I16" by using vba.....
VBA Code:
Sub copy()
Sheets("Master").Range("C2,F2,B6:I16").copy
Sheets("DATASHEET").Select
VBA Code:

Then I get the error "Run-time error '1004': This action won't work on multiple selections"

Sorry if I didnt understand - Im a bit new to vba.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,116
Office Version
  1. 2013
Platform
  1. Windows
I do not understand.

Just show me the line of code you're attempting to run that does not work.
 

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
77
Office Version
  1. 365
Platform
  1. Windows
I do not understand.

Just show me the line of code you're attempting to run that does not work.
This part... these are the cells I want to copy from but when I add C2 and F2 to the vba below it states the error

VBA Code:
Sub copy()
    Sheets("Master").Range("C2,F2,B6:I16").copy

Sorry @Fluff - I did use the "vba quick wrap" button .... I just thought you pressed the button once to start the "vba quote", entered the vba, and then pressed the button again to "seal it"
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
19,116
Office Version
  1. 2013
Platform
  1. Windows
I see your getting more help so I will move on.
 

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
77
Office Version
  1. 365
Platform
  1. Windows
I see your getting more help so I will move on.
Oh ok...... I havent had any help from elsewhere but thanks for trying. Sorry if I offended anyone...not sure how tbh.
 
Last edited:

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,794
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
VBA Code:
 Sheets("Master").Range("C2,F2,B6:I16").copy

is not legal syntax for the range.copy method. The range specified must be contiguous , so if you want to include C2 and F2, then

VBA Code:
 Sheets("Master").Range("B2:I16").copy

If you want to copy JUST C2 and F2 along with B6:I16, then more than one operation is needed along with some consideration about where you intend each piece of data to go. For example, in the code example you posted , you copied Range("B6:I16") from one sheet and pasted it to Range("A1") in another sheet. Where did you intend C2 and F2 to be pasted?
 

Forum statistics

Threads
1,175,544
Messages
5,898,048
Members
434,690
Latest member
Shamsuddin M

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