View Full Version : [SLEEPER:] Copying Data to Specific Tab
Hoopsah
05-21-2008, 04:15 AM
Hi,
I have a worksheet created for data input by the user.
One of the questions will ask if what region the work is to be carried out, there are 8 possibilites.
Is it possible (And if so how) to use this input to copy all the appropriate cells into the tab that bears the name of the region????????
DoLoop
05-21-2008, 04:26 AM
Yes it is. With VBA.
Maybe specify question more in detail?
Hoopsah
05-21-2008, 04:47 AM
Aye! Cheers for that DOLOOP!!
OK, so try and explain it a bit more,
On my user input page one of the options is to select a region, once a specific region is selected and the user "Submits Details" to the main spreadsheet/database I want it to only copy the details into the specific tab/worksheet that already holds the name of that region.
mdmackillop
05-21-2008, 05:47 AM
You don't say if you want to write to a specific range or to append below existing data. A sample workbook would assist.
Hoopsah
05-21-2008, 06:56 AM
Hi Guys,
please find attached a copy of the workbook I am struggling with.
I hope it is self-explanatory, but, you will see that each input equates to a column in the specific sheet.
The new info should be added to the bottom of the sheet unless the unique code (PO Ref) already exists within the worksheet - if that is the case then it amends the existing line.
XLD helped me with one half of this program (Appending/Amending the list) but since then I now have to split it up by regions.
Any further question, I will do my best to answer, if you require to see the original work submitted by XLD I will be happy to post.
Cheers
DoLoop
05-21-2008, 07:06 AM
You could give cell J17 a range name, like "REGION".
Put the value of that range in a variable
Dim strRegion as string
strRegion = UCase(Thisworkbook.Worksheets("Input DIA").Range("REGION"))
In the section of your VBA code where you Append/Amend you can set the worksheet to write in with next statement:
Set ws = Thisworkbook.Worksheets(strRegion)
And then use the ws variable in your original code that applies on the ws to Append/Amend in.
Cheers,
DoL:bug: p
mdmackillop
05-22-2008, 05:26 AM
Hi Gerry,
I'll leave you to add all the fields in the correct order
Sub Button1_Click()
Data = Array([D10], [D13], [D17], [G17])
With Sheets(Range("J17").Value)
Set tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
End With
tgt.Resize(, UBound(Data) + 1).Value = Data
MsgBox "Copied"
End Sub
Hoopsah
05-23-2008, 04:07 AM
Thanks Guys.
Unfortunately, I am working on something else at the moment so I won't have a chance to try these options.
However, I will hopefully be back on it next week, so don't be surprised if you see this question arising again.
Cheers
Hoopsah
05-23-2008, 06:04 AM
Right!
Sorry Guys, I have been working on this using the above and I'm afraid I am totally gubbed!
I have tried tweaking it to fit what I want to do and I cannot for the life of me work it out.
Could you possibly help me work this out, where should I be putting the text etc.
Sorry to sound so dumb but sometimes this stuff just gets way beyond me,
Cheers
Hoopsah
05-23-2008, 06:27 AM
Hawd The Bus!!!!
Sorry again guys, I have kept clicking away and trying to get this and lo and behold it does work.
Once again, pure cheers for your help guys this is brilliant.
Hoopsah
05-23-2008, 07:18 AM
arrrgghhhh!!!!!
Hi again,
I have attached a copy of some of my new spreadsheet, now, utilising the codes I have been given I have managed to get it to work a bit for me.
If you try and input an Ellipse reference that is already in the spreadsheet it will bring up the data on screen, however, when I then click submit details in stead of ammending the spreadsheet it creates a new line instead.
Do you know how I can get it to amend the existing entry other than creating a new line?
mdmackillop
05-24-2008, 10:28 AM
Sub Button1_Click()
Data = Array([D17], [G17], [D10], [G10], [D13], [G13], [G22], [D19], [G19], [D22])
With Sheets(Range("J17").Value)
Set tgt = .Columns(3).Find(Range("D10").Value)
If tgt Is Nothing Then
Set tgt = .Cells(.Rows.Count, 1).End(xlUp).Offset(1)
Else
Set tgt = tgt.Offset(, -2)
End If
End With
tgt.Resize(, UBound(Data) + 1).Value = Data
MsgBox "Copied"
End Sub
Hoopsah
05-27-2008, 06:43 AM
Set tgt = .Columns(3).Find(Range("D10").Value).Offset(, -2)
I might be missing something or just being plain stupid, but, when I try this code it will work if the reference number quoted (Value of D10) already exists in the database but if it is not already there it comes up with a compile error on this line.
Object Variable or With block variable not set
?????????
mdmackillop
05-27-2008, 12:41 PM
Lack of testing! Previous code corrected.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.