Save data in the respective worksheet with variable using macro

Hi

I'm new to the forum and also a VBA apprentice. I'm building a workbook but as I'm an apprentice and I have little knowledge, I'm asking for your help to build a macro. If it's not possible, I understand and appreciate it. Sorry for the English. It's not my native language. There are several dozen worksheets, but I just illustrate it with three options to explain the problem.

The first worksheet (BASE) is the list of people and their ID. The DATA worksheet is where I enter the data. The other worksheets I named it with each person's name. The criteria is to copy the data from the DATA worksheet, and save it in the worksheet corresponding to the name. For this I need a macro to be able to identify the worksheet where it should save the data that corresponds to the name, which is in the DATA worksheet. The data must be pasted on the last blank line.

To identify which worksheet, I thought of writing “NAME” in a cell and concatenate this cell with the variable that is in the worksheet's name field DATA. This would result in a name equal to the worksheet where the data should be saved. The macro would then use this result as a way to identify the target worksheet. The ID is automatic. PROC. Nº, DATA and PRODUCT CODE are manually entered by me. In the attached example I already include my idea.

Could someone help me to create a Macro with these functions? Can you give me other ideas?

Thanks and sorry for the long text.

Quote from royUK

There's no data in the data sheet and it does not even resemble a data sheet.

Read this

Hi Roy

I don't understand why you don't see the sheet. I can see the sheet perfectly, in my office 2019, when looking at the attachment that is in my post. Anyway, I resend it again. I changed the name to be more understandable. As for the rules, I had already read them. But thanks anyway.

Quote from vcoolio

Hello Intranet,

Based on the information that you have supplied, the following VBA code may help:-

Code

Sub Test()


Application.ScreenUpdating = False


Dim cArr As Variant, pArr As Variant, i As Long
Dim wsD As Worksheet
Dim ws1 As Worksheet
Set ws = Worksheets("Data")
Set wsD = Worksheets(ws.[J1].Value)


nrow = wsD.Cells(Rows.Count, 1).End(xlUp).Row + 1


cArr = Array("B3", "D3", "B5", "D5", "F5")
pArr = Array("A", "B", "C", "D", "E")


For i = LBound(cArr) To UBound(cArr)
ws.Range(cArr(i)).Copy
wsD.Range(pArr(i) & nrow).PasteSpecial xlPasteValues
Next i


Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub

Display More

Place the code into a standard module and assign it to a button.

I've attached your sample workbook with the code implemented. Just click on the "TEST ME" button to see how it works. Play with the data to see how it is transferred to the relevant 'name' sheet.

I hope that this helps.

Cheerio,

vcoolio.

Intranet.xlsm

Display More

vcoolio

YOU ARE THE MAN! Thank you, thank you, thank you!
It works perfectly.

Thanks again

Quote from royUK

The data sheet looks more like a form than a data sheet. The name sheets resemble data sheets but have one sheet per name is a very inefficient way of maintaining data.

I know it doesn't look like a data sheet and that it's possibly not the best way to store data. But I tried to give you an example as simple as possible without loading you up with unnecessary information. This example is part of a much larger workbook that is about 300 MB. I'm reformulating everything to be lighter and faster. I understand that it may seem ineffective to you, but that's how the workbook is. And for me, that even didn't know what a macro was two months ago...well...

I am sure that I will perfect this workbook because I find this subject very interesting and I will spend more time absorbing all your knowledge.

Thanks you again

Quote from vcoolio

You're welcome Intranet. I'm glad to have been able to assist and thanks for the feed back.

BTW, I just noticed a little typo in the code and I'm surprised that you didn't receive and error message.

This line:-

Dim ws1 As Worksheet

should be

Dim ws As Worksheet

Cheerio,

vcoolio.

Display More

I appreciate your observation. But I understood and had already made the change.

Thanks.

Quote from royUK

Your time would be better spent learning the basics of Excel and working with data. I build complex reports in Excel with out using VBA.

I understand your observation. I don't have your knowledge but I have some knowledge of excel. But I never dedicated myself to VBA, until now. This is an urgent and complicated case and the person asked me to be able to improve performance in a super short amount of time. When I have time, I will have to rebuild it all over again, with data storage and stuff, in a different way. But for now... it is what it is. However, I have already reduced it in a first phase from 300mb to 100mb and then to 50mb, saving in binary.

But I promise I will learn VBA through the forum. I like it.

Thanks