Macro help

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

Here is what I have:

Sheet3(pcdata)
data is in rows A3:R19
I need to copy and paste(Macro) this data to
Sheet4(Total)
The first available row is A3 Sheet4(Total)

The Sheet3(pcdata) will be updated daily
the macro needs to paste the data on Sheet4(Total) below the existing data,,
(next available row).
As I am quite a novice at excel. any help.. is very appreciated.

Thanks
Mike
 
Hi Mike

Try this
http://www.rondebruin.nl/copy1.htm
Click to expand...

hmmmm...I would really like to figure out that section with pulling
data from another workbook. Is it possible to have the VBA import it
from a CSV onto a dummy tab and the paste it below existing data on a
data tab? Creating an import macro would not be hard but trying to
get it pasted _below_ the data is one I am stumped on. Unfortunately
the instructions on your site went right over my head.
 
Hi

If you copy this function in a normal module

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

You can use this in your macro then to get the first empty row number

Lr = LastRow(Sheets("Sheet2")) + 1

Look at the macro example on my page and see that this line give you the cell address
Set destrange = Sheets("Sheet2").Range("A" & Lr)

Try it
 
Hi

I'm assuming

1. that when you say that A3 is the first available cell in worksheet Total,
there is some data in cell A2

2. that there is data in cell A19 being copied

Also I'm letting you put your own code in that checks the Total worksheet
has 17 spare rows at the end.

Sub aaa()
Dim m As Long

m = ThisWorkbook.Sheets("Total").Rows.Count

m = ThisWorkbook.Sheets("Total").Range("A" & m).End(xlUp).Row + 1

ThisWorkbook.Sheets("pcdata").[A3:R19].Copy
destination:=ThisWorkbook.Sheets("Total").Range("A" & m)

End Sub


Doug


Mike wrote in message ...
 
Hi, I tried the Lastrow function, I finally got the macro to copy and paste
to the next sheet,,but it always paste's back over itself, it doesn't
advance to the next empty row,,I can't figure out what I am doing wrong.
 
Hi Mike

Open a new workbook
Copy this in a module (in this workbook)

Sub copy_1()
Dim sourceRange As Range
Dim destrange As Range
Dim Lr As Long
Lr = LastRow(Sheets("Sheet2")) + 1
Set sourceRange = Sheets("Sheet1").Range("A1:C1")
Set destrange = Sheets("Sheet2").Range("A" & Lr)
sourceRange.Copy destrange
End Sub

Function LastRow(sh As Worksheet)
On Error Resume Next
LastRow = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
On Error GoTo 0
End Function

Enter something in A1:C1 in "Sheet1"
Run the macro a few times and you will see that every time you run it
it will copy to the next line in "Sheet2"
 
Thank you Ron,,I will give it a try and get back with you. I can send you
the file of the project I am working on if you like,, maybe you can tell me
what I am doing wrong,,


Thanks again Ron, for all the help
Mike
 
I gave it a try,, I made a new wookbook,,put in your code only, it still
does not move to the next line on sheet2 when the macro is run

The macro runs fine,,but it just copies back over itself on sheet2
 
Thank you Doug,, I will give your solution a try also..



Doug said:
Hi

I'm assuming

1. that when you say that A3 is the first available cell in worksheet
Total,
there is some data in cell A2

2. that there is data in cell A19 being copied

Also I'm letting you put your own code in that checks the Total worksheet
has 17 spare rows at the end.

Sub aaa()
Dim m As Long

m = ThisWorkbook.Sheets("Total").Rows.Count

m = ThisWorkbook.Sheets("Total").Range("A" & m).End(xlUp).Row + 1

ThisWorkbook.Sheets("pcdata").[A3:R19].Copy
destination:=ThisWorkbook.Sheets("Total").Range("A" & m)

End Sub


Doug


Mike wrote in message ...
Here is what I have:

Sheet3(pcdata)
data is in rows A3:R19
I need to copy and paste(Macro) this data to
Sheet4(Total)
The first available row is A3 Sheet4(Total)

The Sheet3(pcdata) will be updated daily
the macro needs to paste the data on Sheet4(Total) below the existing data,,
(next available row).
As I am quite a novice at excel. any help.. is very appreciated.

Thanks
Mike
Click to expand...
Click to expand...
 
Back
Top