Macro help

[gbell3587]

Hi

I have 2 workbooks, lets call them x and y. Workbook y contains data in rows a1 to a100, i want to create a macro that will copy data from workboox x and then paste it into the next empty row of workbook y - so as soon as i hit the button, it will copy a row from workboox x and paste it to a101 in workbook y & then the next time it'll paste to a102 etc. I can only get it to paste to the same cell everytime using a recorded macro. Can anyone help?

Thanks
Graham

 

[VoG]

Try like this

Sub atest()
Workbooks("y.xls").Sheets("Sheet1").Range("A1:A100").Copy Destination:=Workbooks("y.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

 

[gbell3587]

Try like this

Sub atest()
Workbooks("y.xls").Sheets("Sheet1").Range("A1:A100").Copy Destination:=Workbooks("y.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

Awesome!

Thanks for this.

 

[VoG]

You are welcome. It should of course have been

Sub atest()
Workbooks("x.xls").Sheets("Sheet1").Range("A1:A100").Copy Destination:=Workbooks("y.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

 

[gbell3587]

You are welcome. It should of course have been

Sub atest()
Workbooks("x.xls").Sheets("Sheet1").Range("A1:A100").Copy Destination:=Workbooks("y.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

lol yeah, ive got it modified and working beautifully in my file(s).

Thanks again

 

[gbell3587]

Hi

Another quicky on top of this, i would like to test if the value already exists before pasting it into the new sheet.

So lets say in workbook x, the value i will be copying into workbook y is "door".......i want to test if the value "door" already exists in workbook y - if it does, i dont want to paste it.

Anyone able to help out with this one?

I guess its testing for duplicates using vba, i can do it manually using conditional formating but i really want it as part of my macro to reduce the work.

thanks alot!

 

[VoG]

Perhaps like this

Sub btest()
If IsError(Application.Match(Workbooks("x.xls").Sheets("Sheet1").Range("A1").Value, Workbooks("y.xls").Sheets("Sheet1").Columns("A"), 0)) Then
    Workbooks("x.xls").Sheets("Sheet1").Range("A1").Copy Destination:=Workbooks("y.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End Sub

 

[gbell3587]

Perhaps like this

Sub btest()
If IsError(Application.Match(Workbooks("x.xls").Sheets("Sheet1").Range("A1").Value, Workbooks("y.xls").Sheets("Sheet1").Columns("A"), 0)) Then
    Workbooks("x.xls").Sheets("Sheet1").Range("A1").Copy Destination:=Workbooks("y.xls").Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1)
End If
End Sub

Thanks, i have a working solution. Although now on reflection, im not sure whether duplicates should be allowed........will need to wait until im back in office on tuesday to find out. Either way, its a nice bit of code to add to my library!

Cheers
Graham

 

[raydeep1347]

Hi All

I am new to the forum and kindly help if I am not posting this query at the right place.

I have a sheet that has 2 buttons - "Start" & "End" which capture the time one clicks these buttons. the start click time is recorded in the Col. P & End click time on col. Q. There's a formula in col. R which calculates the difference of the end & start time and gives me the "Time Taken".

Problem arises when someone 'by mistake' someone clicks the end button first. So i need a msgbox to give out an error message whenever someone clicks the "End" button before clicking the "Start" button and simultaneously not record the time in that case. So basically when a time gets recorded in the col. Q and there's no time entry in col. P, the error msg should fire and this needs to work for the entire Col. P & Q.

Will be great if I could get some help on this.... need to roll out the stuff on priority. Thanks in Advance.

 

[VoG]

Hi All

I am new to the forum and kindly help if I am not posting this query at the right place.

I have a sheet that has 2 buttons - "Start" & "End" which capture the time one clicks these buttons. the start click time is recorded in the Col. P & End click time on col. Q. There's a formula in col. R which calculates the difference of the end & start time and gives me the "Time Taken".

Problem arises when someone 'by mistake' someone clicks the end button first. So i need a msgbox to give out an error message whenever someone clicks the "End" button before clicking the "Start" button and simultaneously not record the time in that case. So basically when a time gets recorded in the col. Q and there's no time entry in col. P, the error msg should fire and this needs to work for the entire Col. P & Q.

Will be great if I could get some help on this.... need to roll out the stuff on priority. Thanks in Advance.

You need to start your own thread.