Transferring info from one Sheet to another+Clear (Excel 2003)

[Excelnoub]

Good evening everyone,

I am relatively new to macros and Coding with Excel. I started to construct a Report file based on an old template we use in Lotus Pro. Sorry but that system did not cut it for me. So here I am going from Forum to Forum looking for help on missing codes that I cannot figure out. I could explain the full process but it would look like I’m trying to give the job to someone else. This is not the case. I’ve been working my a%# off with this Report sheet that seems practically like baking a cake if you know what you are doing, but the difference is that the ingredients are tougher than I thought. It started with simple VB code then help on a forum gave me a more complex code that would do some of the stuff I need.


Now with request, me and my stupid idea going to show this to one of my co-worker, thought it was a good idea and it would be “COOL” to add more functions. More work for me…


The following Link will get you a sample of my report.

http://forums.techguy.org/business-applications/1060679-transferring-info-one-sheet-another.html#post8408769


You will see that there is a flow/process to each tab. You need to start by inserting information in the first sheet “Pre-Requisition” then work your way to the next. At each end of sheets there is a command drop down menu that will determine where / what the information will do. Each row is its own flow. Once a command is selected you will see the outcome.


The sheet I am having problem with is the “MERX” tab. Once a data entry has been transferred from Sheet1 to Sheet2; in column F the user has 2 options. One is to select “yes”. This will transfer some info to Sheet3 and some to Sheet2 always matching column A.


If the user selects “retender” a refresh is made adding formula to that row in column C and D.


Pretty simple up to now right?


Here is the problem I’m trying to fix.


If I select “cancelled” I need the following functions to be made:


If LCase(Target.Value) = "cancelled" Then

Find the matching value in Sheet5 “Report” and copy Range(Cells(Target.Row, 1), Cells(Target.Row, 10)) and paste it to Sheets("Archives").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues


THEN


Clear the content in Sheet2 Range(Cells(Target.Row, 1), Cells(Target.Row, 6)) and the info in that same row in the “Report” sheet.


Mind blowing… I know it’s hard to understand but explaining from scratch would be to much. I think…


Anyone would have any idea how to?

 

[Excelnoub]

Changes:


Delete:

The sheet I am having problem with is the “MERX” tab. Once a data entry has been transferred from Sheet1 to Sheet2; in column F the user has 2 options. One is to select “yes”. This will transfer some info to Sheet3 and some to Sheet2 always matching column A.


Insert:

The sheet I am having problem with is the “MERX” tab. Once a data entry has been transferred from Sheet1 to Sheet2; in column F the user has 2 options. One is to select “yes”. This will transfer some info to Sheet3 and some to Sheet5 always matching column A.

 

[SirJB7]

Hi, Excelnoub!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


I followed the link and downloaded the file Draftffp.xls (Excel 2003 version).


Firstly, I had a problem with the control Calendar1 in user form frmCalendar, I omitted the library as I didn't find any suitable. I hope this doesn't interfere with the rest of the test.


Secondly, I added 'Option Explicit' statement to all modules and worksheets codes, so as to compile and validate code, and I found the following:

a) in sheet Pre-Requisition, sub Test, there's an error in 'Worksheets(Report).Range("A5:A10000")': it should say "Report" instead of Report.

b) in sheet Contracts, sub Worksheet_Change, there're missing variable declarations:

Dim NumRows As Long, R As Long, I As Long

Dim tmpArr As String


Next I went to sheet MERX, and while testing F column dropdown listbox I found it has embedded blank lines and if you choose a blank line the code crashes, so I delete (shifting up) cells J5 and J7.


I haven't yet checked if the formulas and VBA code works as per you requirements, but let me see if I understand your issue:

1) the only problem is related to what happens when user selects "cancelled" in sheet MERX at column F from rows 5 thru 30

2) if so, you want to perform this described operations:

- Find the matching value in Sheet5 “Report”

- copy Range(Cells(Target.Row, 1), Cells(Target.Row, 10))

- paste it to Sheets("Archives").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

- Clear the content in Sheet2 Range(Cells(Target.Row, 1), Cells(Target.Row, 6))

- clear the info in that same row in the “Report” sheet.

3) you have this code:

-----

[pre]

If LCase(Target.Value) = "cancelled" Then
RptProjRowNum = Application.WorksheetFunction.Match( _
ActiveSheet.Range("A" & TRow).Value, _
Worksheets("Report").Range("A5:A1000"), 0) + 4

With Worksheets("Report").Range("A" & Target.Row)
Sheets("Archives").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(, 18).Value = .Resize(, 18).Value
.Resize(, 19).ClearContents
End With

Application.EnableEvents = False
Range(Cells(Target.Row, 1), Cells(Target.Row, 6)).ClearContents
Application.EnableEvents = True
End If

[/pre]
-----


Now my questions are:

- should be if the code doesn't work properly (which I assume it originated this topic) and if not, what is it doing?

- to test this, should I need to populate certain cells with proper data? if so, can you please provide it? (refer to second green sticky post at this forums main page for uploading guidelines)


Regards!

 

[Guest]

Having fish in your home can be a joy. Just to watch them swim up and down your aquarium fish tanks can be priceless. It is our responsibility to make sure that they are given a safe and comfortable environment to survive in. Aquarium Fish tanks can be a great way to keep these pets in your home. All you have to do is be vigilant and make sure that you make observations of the water and the fish from time to time. This will help you see the changes that are taking place in the aquarium.,isabel marant bottes


Fisher is the author of this article on Complete Fish Tanks.Find more information about Discount Fish Tanks here

 

[Excelnoub]

I am in a situation that I cannot download any add-on from my work. Therefore I would need to post it where the original file, with hyperlink, is located.


Sorry about this issue, we have strict admin policies and monitored.


To answer you questions:

- should be if the code doesn't work properly (which I assume it originated this topic) and if not, what is it doing?


Answer: Nothing it’s only doing a clear. I played around a little with the code and it transfers some unwanted data to the “Report” sheet and nothing to the “Archives” sheet.


- to test this, should I need to populate certain cells with proper data? if so, can you please provide it? (refer to second green sticky post at this forums main page for uploading guidelines)


Answer: I will populate data for 4 examples. With these you will be able to have a better perspective of what I am trying to do.

 

[SirJB7]

HI, Excelnoub!

I understand constraints of firewall policies, just add the examples and advise, re-posting link if changed from previous.

Regards!

 

[Excelnoub]

http://forums.techguy.org/business-applications/1060679-transferring-info-one-sheet-another.html#post8409554


Process comming shortly. I will add the process so that you have an idea of what to insert in the fields and the why.


The code mentionned above has been greened out as I am still playing around with the code.


I have made some changes and the calendar is on my personal xl file so, yes it will have an impact on other computers. date format should be MM/DD/YYYY.


Will add the process in a word doc on the other site. This is the only way I can post my documents.

 

[Excelnoub]

http://forums.techguy.org/business-applications/1060679-transferring-info-one-sheet-another.html#post8409565


This link has some of the history and process. Please be advised that the "Contracts" sheet codes were deleted as I can only post less than 200kb. This will not impact the codes process, well it shoudln't as the main code needed is in the MERX tab.

 

[SirJB7]

Hi, Excelnoub!

Gonna check those files and get back to you later this afternoon (I'm at GMT-3).

Regards!

 

[Excelnoub]

I recieved this code but giving me a run-time error 13 (Type mismatch)


If LCase(Target.Value) = "cancelled" Then

xref = Range("A" & Target.Row).Value 'ref no. to find

xDelMERX = Target.Row 'row to be deleted

xDelReport = Sheet5.Range("A:A").Find(xref).Row 'row to be deleted

Sheet5.Range("A" & xDelReport & ":J" & xDelReport).Copy Destination:=Sheet6.Range("A65536").End(xlUp).Offset(1, 0)

Application.EnableEvents = False

Sheet2.Range("A" & xDelMERX & ":F" & xDelMERX).Delete shift:=xlUp

Sheet5.Range("A" & xDelReport & ":V" & xDelReport).Delete shift:=xlUp

Application.EnableEvents = True

End If

 

[Excelnoub]

Posting the answer that I tweeked...


If Not Intersect(Target, Range("F:F")) Is Nothing Then

If Target.Cells.Count = 1 Then

If LCase(Target.Value) = "cancelled" Then

xref = Range("A" & Target.Row).Value 'ref no. to find

xclearMERX = Target.Row 'row to be cleared

xclearReport = Sheet5.Range("A:A").Find(xref).Row 'row to be cleared

Sheet5.Range("A" & xclearReport & ":J" & xclearReport).Copy Destination:=Sheet6.Range("A65536").End(xlUp).Offset(1, 0)

Application.EnableEvents = False

Sheet2.Range("A" & xclearMERX & ":F" & xclearMERX).ClearContents

Sheet5.Range("A" & xclearReport & ":V" & xclearReport).ClearContents

Application.EnableEvents = True

End If

End If

End If

 

[SirJB7]

Hi, Excelnoub!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!