How to Copy and Paste Data in Different Sheet But Still Hidden

[MonioDee]

Hi Mentors! I hope someone could help I have created a template that could help me a lot in my work, but sadly I only have a very few knowledge in regards of VBA and all of those codes were just came from internet search, I need to paste the details in a identified sheets and I want them to still hidden so that the report can never be replace nor manipulated, minor or major reconstruction to my template would be a very big help, and very much appreciated. Thank so much in advance!! ^_^

Sub copyPasteData()

Dim strSourceSheet As String
Dim strDestinationSheet As String
Dim LastRow As Long

strSourceSheet = "Data entry"

Sheets(strSourceSheet).Visible = True
Sheets(strSourceSheet).Select

Range("c2").Select
Do While ActiveCell.Value <> ""
strDestinationSheet = ActiveCell.Value
ActiveCell.Offset(0, -2).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
Selection.Copy
Sheets(strDestinationSheet).Visible = True
Sheets(strDestinationSheet).Select
LastRow = LastRowInOneColumn("A")
Cells(LastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets(strSourceSheet).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Offset(1, 0).Select

Loop
End Sub

 

[MonioDee]

here is the sample, re construction of it is very very much appreciated thanks mentors


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[vletm]

Is somewhere any 'hints' like:
What to copy & paste?
Where to copy & paste?
When to copy & paste?

Use [ CODE ] & [ / CODE ] tags to embed your VBA Macros, as has written in Tip.

 

[Belleke]

When I read this

I want them to still hidden so that the report can never be replace nor manipulated

Then you should work with a userform.
See attached as simple example what I mean
There is a sheet called test that is hidden.
Fill in the form and make the sheet test visible to see the result.

 

[MonioDee]

Hi Sir Belleke thanks your userform is great however Im still new in VBA thing I dont even know how to open the details distributed via userform, and the details I need to submit in different sheets was 18 cells in a row, in my template above everytime i click to copy paste button it did copy paste but my problem is their sheets destinations also appears, what I really need is to copy paste them all without appearing the worksheet destination, thanks for the help sir.

 

[MonioDee]

As seen everytime i click enter the destination worksheet based on column C the worksheet also appears, what I need Sir is everytime i clik the enter button they remain hidden, here is their codes

Sub copyPasteData()

Dim strSourceSheet As String
Dim strDestinationSheet As String
Dim lastrow As Long

strSourceSheet = "Data entry"

Sheets(strSourceSheet).Visible = True
Sheets(strSourceSheet).Select

Range("c2").Select
Do While ActiveCell.Value <> ""
strDestinationSheet = ActiveCell.Value
ActiveCell.Offset(0, -2).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
Selection.Copy
Sheets(strDestinationSheet).Visible = True
Sheets(strDestinationSheet).Select
lastrow = LastRowInOneColumn("A")
Cells(lastrow + 1, 1).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets(strSourceSheet).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Offset(1, 0).Select

Loop
End Sub

 

[vletm]

MonioDee
I got some answers like:
You fill Your 'Data Entry'-sheet.
After, it will be ready.
You will press [ENTER]-button and
all filled row would copy to other sheets.

Something like this?
Next do it ..

 

[MonioDee]

You got it Sir Vletm, but why does it took so long every time I put data in every cell it takes 3-4 seconds, and more than a minute on clicking the ENTER button? got any more tricks Sir? thanks a lot sir Many many thanks!! ^_^

 

[vletm]

... so long time ... hmm?
I tested it just and ... of course only with 10 rows ... and
it took ... oh! ... less than 1 second, totally!
Totals: less than 1 second here, over one minute there ... hmm?

What else do You do with that computer?
Do any heavy programs run same time or so?
Could You send that Your file here? ... I could test it too.

Just for verify:
You add those rows like in that Your snapshot ...
and after all added
You will press [ ENTER ] from that Sheet.
... and ... wait ... over one minute ...