Copy and paste to another worksheet

[trevolly]

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

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

 

[My Aswer Is This]

Try this:

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]

Try this:

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....

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

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.....

Sub copy()
Sheets("Master").Range("C2,F2,B6:I16").copy
Sheets("DATASHEET").Select

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]

I do not understand.

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

 

[Fluff]

@trevolly please follow this link to see how to post codde. How to Post Your VBA Code

 

[trevolly]

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

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]

I see your getting more help so I will move on.

 

[Fluff]

I see your getting more help so I will move on.

I am not helping, other than showing the OP how to post code correctly. I will leave it to you.

 

[trevolly]

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.

 

[rlv01]

 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

 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?