Loop problem

[Guest]

Hi, Below is my poor attempt of copying a range (invoices) to another page.
The idea is:- the macro changes a number (from 1) copies a range onto
another sheet, then changes the number 1 to 2 (reformulates) copies the same
range and places beneath the last data. I want it to keep doing this until it
reaches 60 then stops.
Can someone please help and correct the code for me?
John

Sub Macro2()
Application.ScreenUpdating = False
Sheets("Delivery Note").Select
' On the worksheet this cell will say Stop when another number reaches a
certain number
Range("R5").Select
Do Until ActiveCell.Text = "Stop"
' Cell L3 increments by 1 using the next bit of code
Range("L3").Select
Selection.Copy
Range("R3").Select
Selection.PasteSpecial Paste:=xlPasteValues
' The next piece is to wait for the formula's to catch up
Application.Wait Now + TimeValue("00:00:01")

Range("S2").Select
If ActiveCell = 1 Then
Range("R2").Select
If ActiveCell = 1 Then
Application.Goto Reference:="inv_a"
Selection.Copy
Sheets("Inv").Select
iFreeRow = Cells(Rows.count, "B").End(xlUp).Row
Cells(iFreeRow + 1, "B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Delivery Note").Select
End If

If ActiveCell = 2 Then
Application.Goto Reference:="inv_b"
Selection.Copy
Sheets("Inv (2)").Select
iFreeRow = Cells(Rows.count, "B").End(xlUp).Row
Cells(iFreeRow + 1, "B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("Delivery Note").Select
End If

End Sub

 

[pisanichris]

Hi, what is the problem is gives you (the error screen).
Also, I am not that good at QBasic, and I also need some help on usin
programming on excel.

I saw your thread and it seems like you want to do the same thing as
want to do.
I need a way that every week, I will start up a macro that will updat
certain cells and copy values from one area to another sheet so that a
the end of the year, I will have a list of all the necessary totals t
calculate on.
The problem is, that every week it will need to paste the value
under/next to the previous ones.

Can you help?
If possible, please send your reply to (e-mail address removed)

Thanks,
Chri

 

[damorrison]

heres a couple lines of code to get you started in cleaning this up:
this is not option explicit, so you need to delete option explict from
the modual before you enter this code.
the for next
Sub ForNextTest ()
Range("yourRangeHere").select
For varCounter = 1 to 60
'enter code here
Next
End Sub
This will repeat the code 60 times,

a simple copy/paste code
Sub CopyRange()
Range("A1:B3").Copy Destination:=ActiveCell
End Sub
the code is actually one line
If you are pasting special values like
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=
_
False, Transpose:=False
this means the same:
..PasteSpecial xlPasteValues -you don't need all the other garbage that
the macro reader records
you can name your ranges so then you won't have to enter the sheet
name,
hightlite the cell or range you want to name click on the arrow beside
the formula "="
type in a one word name and enter
now when you want to select a range in code enter it something like
this.
Range("namedrange").select Destination:= active cell
Once you have cleaned this up some , lets see it again
Dave!

 

[damorrison]

Qbasic is history!
I have an idea;
If you want to keep a weekly list in another sheet and have each column
as week 1,week 2..ect
to start, creat your Weekly total worksheet and find a range in your
weekly worksheet to copy and paste to your weekly columns in the total
worksheet.
Work with your macro recorder to get your code, use absolute and
relative modes when required thats the square box the shows when you
are recording a macro: absolute takes you directly to a cell
relative you start at one place and move up/down/left/right from where
the cell is . Practice the macro recorder, make a test copy of your
workbook and start recording
dave

 

[Guest]

Hi Dave,
I havnt forgotten about you, its just that I wont be able to have the time
to spend on this now until later in the week. Many thanks for your help I
will let you know how I get on
Regards
John

 

[Guest]

Dave,
I got there in the end, and it works, but I am sure you could find a way to
slim it down further (seeing that I am only a novice)
Let me know what you think
Regards
John

Application.ScreenUpdating = False
Dim count As Long
Number = Range("Number").Value
Sheets("Database").Select
On Error Resume Next
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
On Error GoTo 0
On Error Resume Next
ActiveSheet.PivotTables("PivotTable2").PivotCache.Refresh
On Error GoTo 0
On Error Resume Next
With ActiveSheet.PivotTables("PivotTable2").PivotFields("Name")
.PivotItems("Count of Store Name").Visible = False
.PivotItems("Store Name").Visible = False
.PivotItems("(blank)").Visible = False
End With
On Error GoTo 0
Sheets("Inv").Visible = True
Application.DisplayStatusBar = True
Application.StatusBar = "Please wait whilst the program is creating the
Delivery Note list........................"
For varCounter = 1 To 100
Sheets("Delivery Note").Select
Sheets("Delivery Note").Select
ActiveSheet.Unprotect
Sheets("Inv").Select
ActiveSheet.Unprotect
Sheets("Delivery Note").Select
Range("J5").Select
Selection.ClearContents
Range("S3").Select
If ActiveCell = "Stop" Then
MsgBox "" & Number & " Delivery Notes processed"
Range("R3").Select
ActiveCell.FormulaR1C1 = "0"
Sheets("Delivery Note").Select
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlNoSelection
Sheets("Inv").Select
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlNoSelection
Sheets("Inv").Visible = False
Sheets("Delivery Note").Select
Range("J5").Select
ActiveSheet.Protect
ActiveSheet.EnableSelection = xlUnlockedCells
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Application.DisplayStatusBar = False
Exit Sub
End If
Range("L3").Select
If ActiveCell = 1 Then
Range("S2").Select
If ActiveCell = 1 Then
Range("R2").Select
If ActiveCell = 1 Then
Application.Goto Reference:="inv_a"
Selection.Copy
Sheets("Inv").Select
iFreeRow = Cells(Rows.count, "B").End(xlUp).Row
Cells(iFreeRow + 1, "B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Selection.PasteSpecial Paste:=xlPasteFormats
Sheets("Delivery Note").Select
End If
If ActiveCell = 2 Then
Application.Goto Reference:="inv_b"
Selection.Copy
Sheets("Inv").Select
iFreeRow = Cells(Rows.count, "B").End(xlUp).Row
Cells(iFreeRow + 1, "B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Selection.PasteSpecial Paste:=xlPasteFormats
Sheets("Delivery Note").Select
End If
End If
End If
Range("L3").Select
Selection.Copy
Range("R3").Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("S2").Select
If ActiveCell = 1 Then
Range("R2").Select
If ActiveCell = 1 Then
Application.Goto Reference:="inv_a"
Selection.Copy
Sheets("Inv").Select
iFreeRow = Cells(Rows.count, "B").End(xlUp).Row
Cells(iFreeRow + 1, "B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Selection.PasteSpecial Paste:=xlPasteFormats
Sheets("Delivery Note").Select
End If
If ActiveCell = 2 Then
Application.Goto Reference:="inv_b"
Selection.Copy
Sheets("Inv").Select
iFreeRow = Cells(Rows.count, "B").End(xlUp).Row
Cells(iFreeRow + 1, "B").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Selection.PasteSpecial Paste:=xlPasteFormats
Sheets("Delivery Note").Select
End If
End If
Next
End Sub

 

[damorrison]

Sheets("Delivery Note").Select
Range("J5").Select
Selection.ClearContents
Range("S3").Select
If ActiveCell = "Stop" Then
Here's an example of naming ranges go to Sheet "Delivery Note" and
select cell J5
click on the box beside the "=" formula sign. that is the place where
you name your ranges(cell)
for an example we will name this cell JayFive(in real life you will
probably give it a name that means something to its subject.)it has to
be a one word name
Now instead of typing
Sheets("Delivery Note").Select
Range("J5").Select
You can just type:
Range("JayFive").Select
If you name all the ranges that are in the code you will save more
lines
At the start you can go like this:
Sheets("Delivery Note").Select
ActiveSheet.Unprotect
Sheets("Inv").Select
ActiveSheet.Unprotect
and later
Sheets("Delivery Note").Select
ActiveSheet.protect
Sheets("Inv").Select
ActiveSheet.protect
keep these two codes together, just for organization:
you can probably delete scroll ups and downs that is just what the
recorder recorded, the macro doesn't care unless it is at a spot that
you need to see

 

[Guest]

Dave,
I use the range names a lot, but hadn’t thought of using them like that, and
as you say: it will shorten the code and make it more efficient.
Many thanks for your help, greatly appreciated
Regards
John