MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Creating a stock control sheet

  Asked By: Jodon    Date: Nov 12    Category: MS Office    Views: 1608

I have a problem, i cant get my head round. I have a sheet "Daily"
and i want to copy the data from there to another sheet "4 week
stock" At the top of the Daily sheet is the day and week number,
these 2 cells are combined into one cell eg Mon,1 for data that is
added Monday of week 1. I then want my excel spreadsheet to reconise
that the data in cells D5:G5 need to be copied, transposed and
pasted in the corresponding column in "4 week stock" that refers to

I have at the moment got, the following code, which will do it right
for Mon,1 but nothing else. I can email copy of spreadsheet if


'Attempt at copying info from one sheet to another Macro recorded
6/6/2006 by CarmenN

Dim LDate As String
Dim LColumn As Integer
Dim LFound As Boolean

On Error GoTo Err_Execute

'Retrieve date value to search for

LDate = Sheets("Daily").Range("e2").Value

Sheets("4 Week Stock").Select

'Start at Column C
LColumn = 3
LFound = False

While LFound = False

'Encountered blank cell in row 3, terminate search
If Len(Cells(3, LColumn)) = 0 Then
MsgBox "No matching date was found"
Exit Sub

'Found match in row 3
ElseIf Cells(3, LColumn) = LDate Then

Sheets("4 Week stock").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True
Application.CutCopyMode = False
Sheets("4 Week stock").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=True

LFound = True
MsgBox "The data has been successfully copied"

'Continue searching
LColumn = LColumn + 1
End If

On Error GoTo 0

Exit Sub

MsgBox "An error occurred."

End Sub



2 Answers Found

Answer #1    Answered By: Arnelle Schmidt     Answered On: Nov 12

I can see something that is probably giving you trouble ...

You are using a counter LColumn to track across your sheet  to find the correct
column. However, you then explicitly select C7 and C16 as the destinations for
your pastes. I imagine you want Cells(7, LColumn) - and another one for row  16
- as your destinations.

I also suggest that you remove the CutCopyMode = False statement, or move it
away from where it is between the Select and the Copy. It's probably not
causing trouble, but it's inappropriate to have it where it is.

Notes for future reference (once it's working, these things are worth trying as
improvements) ...

Don't say

While LFound = False


While Not LFound

Don't use Select statements when they're not needed. E.g.

Sheets("4 Week stock").Select
Cells(7, LColumn).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=True

can be replaced with

Sheets("4 Week stock").Cells(7, LColumn).PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks:=False, Transpose:=True

and has the added benefit of running faster, because it doesn't have to make the
two sheets alternately visible.

Answer #2    Answered By: Cleopatra Massri     Answered On: Nov 12

i knew, that to an expert, the problem
would be an easy one, and i had a feeling i was missing something
fairly obvious. I just couldnt see for looking ! I have implemented
your other advice too, and your right, it makes it much quicker and
Many thanks. This is my first posting on this site, I am very
pleased with the response.

Didn't find what you were looking for? Find more on Creating a stock control sheet Or get search suggestion and latest updates.