Need help with a complex Excel problem

  xania 16:30 23 Jan 09

I am developing a Menu/Stock system using an Excel workbook. The workbook contains a number of worksheets including Menus> and Order sheet> and my problem is to create a VB script to copy the contents of a single column from Menus> to column 4 of Order sheet>. The number of the column to be copied is based upon the value to be found is a cell called Numberofcolumn> which is a calculated field. This is actually stored in cell D7 of a third worksheet called Constants>, so there are two different ways we can capture the number of the column we want to copy.
NB All columns in the workbook are labelled normally, but I have also stored the numeric equivalents as part of this process.

I have tried a number of alternatives without success including using the DGET function, which will only copy a single cell, not a complete column. Given that the worksheet has over 100 columns nearly 500 rows, working with single cells is not an option. I have also tried using the Paste link function, which will work for a numbered column, but fails the moment I replace this with a named cell.

Any help would be most gratefully received.

  Simsy 17:15 23 Jan 09

but I think this will work;


Sub Menupaster()

Dim ColNum As Byte
ColNum = Range("Numberofcolumn")

Sheets("Order Sheet").Select
End Sub




  Simsy 17:18 23 Jan 09

clean up rather by not activating, rather than selecting the sheet, but I got this code by recording a macro and editing it.

(I really just wanted to see if I could beat Vog to a working solution!)



  Graham. 17:28 23 Jan 09

You beat me.

  VoG II 17:36 23 Jan 09

Not faster but shorter and quicker

Sub Menupaster()
Dim ColNum As Byte
ColNum = Range("Numberofcolumn").Value
Sheets("menus").Columns(ColNum).EntireColumn.Copy Destination:=Sheets("Order Sheet").Range("D1")
End Sub

  xania 17:45 24 Jan 09

Hey guys - this is magnificent. Thanks a bundle. I can't try them out until I get back to work on Monday and I'll let you know how I get on then.

Simsy - Yes I agree VOG is the man to beat. I did try once, but he always seems to come up with something better.

At the end of the day - its all good fun.

Have a good one.

  xania 09:43 29 Jan 09

I have only just got round to trying these but unfortunately both are now coming up with the asame error 400. I understand this indicates a locked worksheet, but so far as I can see, the entire workbook is currently unlocked. Any thoughts, please?

  Terry Brown 10:19 29 Jan 09

To unlock cells in Excel
Select the cells you want to unlock
goto TOOls- Protection-Unclick
goto Format -cells- unlock

This should unlock your cells for editing.


  xania 15:20 29 Jan 09

I have already check that there is no protection on the cells or workshet or even the workbook itself. Certainly, when I try entring data from the keybopard into there is no problem. I have checked the error in th workbook at this iswhat the help provides:

The XML page cannot be displayed
Cannot view XML input using style sheet. Please correct the error and then click the Refresh button, or try again later.


Access is denied.

I also checked spelling very carefully, because I notices that there was a slight case difference. Both worksheet and in the macro I now refer to ("Order sheet") rather than ("Order Sheet").

  xania 08:31 30 Jan 09

Ok Guys - progress. I've tinkered about with the layout out VOG's, and its now working after a fashion. Only prblem at present is that it is always copying the same column, so I need to look at this part again.

I'll let you know how I get on.

  xania 08:37 30 Jan 09

No -its now working completely. Problem solved. Thanks all.

This thread is now locked and can not be replied to.

Sniper Elite 4 review: Headshotting Nazis has never felt so good

1995-2015: How technology has changed the world in 20 years

The Best Design, Illustration, Animation and VFX Awards of 2017

WWDC 2017 dates: How to get WWDC 2017 tickets, when is WWDC 2017 and more details announced