Copying Data to Specific Tab

[Hoopsah]

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]

Yes it is. With VBA.
Maybe specify question more in detail?

[Hoopsah]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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]

Lack of testing! Previous code corrected.