Sheet Select

Resolved · High Priority · Version 2010

Tue 17 Jan 2017, 14:47 Delegate Sean said...

Sean has attended:
Excel VBA Introduction course
Excel VBA Intermediate course

Sheet Select

Afternoon All,

Any idea why the below code wont work?

It comes up with 'out of range' when trying to select the NG DATA Label and Bar Code sheet? The Code is correct as its what is used to select master data at the beginning of the code!

All the best.

Sub NGDATA1()

'Select Master Data Sheet
Sheets("Master Data").Select

'Copy Relevant Cells
Range("F138,F174,F177").Select
Selection.Copy

'Select NG DATA Label and Bar Code Sheet
Sheets("NG DATA Label and Bar Code").Select
Range("A2").Select

'Paste Data to NG DATA Label and Bar Code Sheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

'Cancel Marching Ants
Application.CutCopyMode = False

End Sub

Tue 17 Jan 2017, 15:06 Trainer Jens said...

RE: Sheet Select

Hi Sean,

Thank you for the forum question.

You cannot copy multiple selections in VBA.

I try to avoid copy and paste in vba. You can get a lot of problems and it takes a long time for Excel to execute copy and paste.

A much better practise is the code below and specially if it is only 3 values.

Sheets("NG DATA Label and Bar Code").Range("A2").value=Sheets("Master Data").range("F138").value

If it is a lot of data you can use an array.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Tue 17 Jan 2017, 15:24 Delegate Sean said...

RE: Sheet Select

Hello Jens,

Sorry to hear this!

The spreadsheet I have relies entirely on copy/paste.

there is a Master data sheet which must copy data from products into different formats on different sheets. Then from this format our Label printer can recognize the data and print it.

I was creating code so that once the master data sheet is completed the user clicks a 'generate label' button which takes all the relevant data and puts it in the correct worksheets.

I know this isn't much fun for the computer and is also very taxing but its the only way I know how to populate the cells. If the cells reference other ones the printer will print the =ref or =if.

as for VBA not doing ranges it has done for me before? I have another spreadsheet that selects different cells (as long as they're in acending order) and places them in another sheet.

My way of thought was to create modules for the copy and paste function of all ranges on each sheet. Then include each sheets' call in one large call. Messy I know.

Please tell me if I'm in above my head and that if the intermediate course would be beneficial

Tue 17 Jan 2017, 15:49 Trainer Jens said...

RE: Sheet Select

Hi Sean,

I was a little bit to fast answering you before. You can copy multiple selections if it is in the same column or row.

I tested your code and it worked fine. It must be something to do with your sheet names. Try to change them to sheet number as in my example.

I still wouldn't use copy and paste, but it depends on your knowledge. If it is few values, I would just do:

Sheets("Destination Sheet").Range("A1")=Sheets("Source Sheet").Range("A1")

A lot of data as I mentioned I would use an Array. Arrays are not straight forward. We have Arrays on the advanced course.

The code I tested is the code below.

Sub Test()
Sheets(1).Select
Range("a1,a6,a8").Select
Selection.Copy
Sheets(2).Select
Range("A2").Select

'Paste Data to NG DATA Label and Bar Code Sheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True

End Sub

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Wed 18 Jan 2017, 10:32 Delegate Sean said...

RE: Sheet Select

Hi Jens,

I'm starting to believe the work I'm doing at the moment may be leading me down the wrong way.

Would the Intermediate course help towards a little more understanding of Arrays?

The worksheet I am working on seem simple in practice yet is very complicated (For me)

I need to create dynamic ranges really. So when Cell F1 is selected it selects a named range to execute in the VBA

but when G1 is selected it selects the same range but in the column G, and so on.

in simple terms I am taking a column and splitting all its data into rows on other sheets so a printer can extract the data in the right format and print labels.

It's rather critical to the business that this sheet is automated as they're having lots of problems at the moment with human error and manually filling sheets.

Wed 18 Jan 2017, 11:39 Trainer Jens said...

RE: Sheet Select

Hi Sean,

The intermediate VBA course will give you more knowledge and more tools, but Arrays we have on our advanced VBA course.

We can do this as a consultancy, where we get your file and find the right solution and then train you to understand the code so you will be able to amend it, when something change in the future.

I cannot say, if you will be able to do it yourself after having the intermediate course, but you will have more knowledge, which will make you able to do more.

After reading the last you wrote the Indirect function may be useful. If you in F1 have the text "January" and you have a range name "January". Then you can call the Indirect function from your code. The argument is ActiveCell in the Indirect function. The Indirect function will see the argument as a range name.

I do not know, I do not know if this is the right solution in your workbook, but I hope this can help you in the right direction.

Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector