Trying to use End Down

[Ed Davis]

Does anyone know what is wrong with this code?


The first time I run this macro it works fine and the correct info goes
where it needs to go.
Each and every other time I run it, only the first section works.
If run several times it should give the same results on each line.

This is the code that I am using now.


This section works fine.

Sheets("Sheet1").Select
Range("A4").Select
Range("D4").Select
Selection.Copy
Sheets("Audilla").Select
Range("A34").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

This section works fine. the first time only.

Sheets("Sheet1").Select
Range("AH34").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Audilla").Select
Range("B34").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

This section works fine the first time only.



Sheets("Sheet1").Select
Range("AQ34").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Audilla").Select
Range("C34").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False



This section works fine the first time only

Sheets("Sheet1").Select
Range("AZ34").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Audilla").Select
Range("D34").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

 

[Dave Peterson]

Where is your code located? Is it in a worksheet module or is it in a General
module?

In either case, I'd drop all the .select's. And I'd add some variables to make
it easier (for me!) to understand:

Dim RngToCopy as range
dim DestCell as range

'--------first section---------
set rngtocopy = Sheets("Sheet1").range("D4")
with Sheets("Audilla")
set destcell = .Range("A34").End(xlDown).Offset(1, 0)
end with
rngtocopy.copy
destcell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'--------second section---------
set rngtcopy = Sheets("Sheet1").Range("AH34")
with Sheets("Audilla")
set destcell = .Range("B34").End(xlDown).Offset(1, 0)
end with
rngtocopy.copy
destcell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'--------third section---------
set rngtocopy = Sheets("Sheet1").Range("AQ34")
with Sheets("Audilla")
set destcell = .Range("C34").End(xlDown).Offset(1, 0)
end with
rngtocopy.copy
destcell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

'--------fourth section---------
set rngtocopy = Sheets("Sheet1").Range("Az34")
with Sheets("Audilla")
set destcell = .Range("d34").End(xlDown).Offset(1, 0)
end with
rngtocopy.copy
destcell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

 

[Ed Davis]

The code is located in general module and I will be setting up a button to
run the macro.

I have changed the code somewhat as I needed the sheets it was going to to
come from a word in a cell.

I am trying to use the code left in the message below and will let you know
how it works.