Copy and paste to another worksheet

trevolly

Board Regular
Joined
Aug 22, 2021
Messages
115
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:
Thanks for the reply, it is appreciated - I honestly didnt realise it would be such a difficult process and cause issues - I did state I am new to vba and excel. I'd upload a picture or two of what I was trying to achieve but tbh I dont want to cause any more issues. I've asked @Fluff to close my account.
 
Upvote 0

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Thanks for the reply, it is appreciated - I honestly didnt realise it would be such a difficult process and cause issues - I did state I am new to vba and excel. I'd upload a picture or two of what I was trying to achieve but tbh I dont want to cause any more issues. I've asked @Fluff to close my account.
Well, that's up to you. But if you change your mind then there is a free tool (XL2BB) available here which can help you post examples and sample data.

 
Upvote 0
Well, that's up to you. But if you change your mind then there is a free tool (XL2BB) available here which can help you post examples and sample data.

Unfortunately I use excel from a work supplied laptop and I cannot install XL2BB as its locked down, thanks anyway.
 
Upvote 0
Please do not close your account. I was needing to go do some work outside and not be back for a while so I thought Fluff could help you. I was not upset at you. I believe you have to do what you want with more than one line of code. Copy and paste the first range and then the next.
Or if all you want is the values copied you could use something like this:
VBA Code:
Sub No_Copy_Needed()
'Modified 7/2/2022  1:48:37 PM  EDT
Sheets("Alpha").Range("A1").Value = Sheets("Master").Range("B1").Value
End Sub
 
Upvote 0
VBA Code:
Sub SimpleCopyTest()
    Sheets("CopyFrom").Range("C2").copy
    Sheets("CopyTo").Range("A1").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("CopyFrom").Range("F2").copy
    Sheets("CopyTo").Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("CopyFrom").Range("B6:I16").copy
    Sheets("CopyTo").Range("A3").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
End Sub

Book4
ABCDEFGHIJ
1
2underbankuntoweredobduracychertverbenasmaundererisinglassvill
3erlkingsnaticinehodeningqueazierquartzposhoctanscapacify
4merkinoblatelybillederrorswildcatskeratoticpacketedrecoils
5inflaterpompanospataquenetmongermechanismtorsadesfaintishsaindoux
6selliformscandiumsupsheathgoavespolynesiafeathereroblatelyelsa
7sestineshiralleelimburgerquickingblottiestimplementtoiletrypizz
8laurinextensuremelopianogordiidaetapsterlyweeshymousebirdrifler
9kryoliteshawneesoutswingantrocelearcsoutbuyadauntpartitas
10inachoidnotionsalthionicfullamsurbanistsskinkingcaprylicphenoxid
11mordicantaffydavyisinglassrussifierresumesfipennytumidilyholidam
12manyattacapacifytanninedbellwindsarnaluxescageynessextensure
13tessrewroughtjetsamsraysmikirdewaxeschechakowhiner
14drokpalettingbeautigarthsfumewortgrecianyounkersplating
15ineditaseabeeboodledomjanisaryoverequalrecirclevillalphonsin
16milkersmousebirdbeewareolegformylateunthriftyenodaldemisang
17
CopyFrom



Book4
ABCDEFGH
1untowered
2verbenas
3selliformscandiumsupsheathgoavespolynesiafeathereroblatelyelsa
4sestineshiralleelimburgerquickingblottiestimplementtoiletrypizz
5laurinextensuremelopianogordiidaetapsterlyweeshymousebirdrifler
6kryoliteshawneesoutswingantrocelearcsoutbuyadauntpartitas
7inachoidnotionsalthionicfullamsurbanistsskinkingcaprylicphenoxid
8mordicantaffydavyisinglassrussifierresumesfipennytumidilyholidam
9manyattacapacifytanninedbellwindsarnaluxescageynessextensure
10tessrewroughtjetsamsraysmikirdewaxeschechakowhiner
11drokpalettingbeautigarthsfumewortgrecianyounkersplating
12ineditaseabeeboodledomjanisaryoverequalrecirclevillalphonsin
13milkersmousebirdbeewareolegformylateunthriftyenodaldemisang
CopyTo
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

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
Back
Top