Frederik Grund
New Member
Hi Chandoo.
I am looking for a makro that copies/transposes data from one workbook to another by checking headers and only copies/transposes data from specific columns. In the following example, I use “workbook2” and “workbook3”, but the makro should be accessible from all workbooks.
Problem/Workflow:
1. Program data for 1 client is outputted as “Clientsname.csv” which is opened in Excel workbook2. I have many clients and all clients have their own workbook.
2. Data from workbook2 is then manually copied/transposed to workbook3 which functions as the “master workbook” containing all clients’ data. I hope that you can help me make this step automatic by coding a makro.
3. Program data varies from client to client which is why the code needs to check headers. Furthermore, I only want to copy some data from column B and all data from the column with the lowest value stated in row 2.
4. For a client with all data available, I need the code to copy/transpose data from column B row 3-5 workbook2 till column A-C row 2 workbook3. It also needs to copy/transpose data from workbook2 row 6-123 in “the column with the lowest value stated in row 2” (max 200 columns should be searched) till column D-DQ row 2 workbook3. When there are 2 or more columns with equally low values in workbook2 the first column should be used.
5. For a client with missing data the copy/transpose function should still search column B row 3-5 and row 6-123 in “the column with the lowest value in row 2”, but the code should put a “.” in workbook 3 when data is missing.
6. I have uploaded two pairs of Workbook2 and Workbook3. One pair with all data and one pair with data missing.
7. In both examples, the column with the lowest value in row 2 workbook2 is column H and is together with column B in workbook2 the only two columns being copied to row 2 column A-DQ in workbook3.
8. In the example with missing data, cells named A10-A18 and corresponding data are missing in workbook2 and are therefore filled with a “.” in workbook3.
9. Data is outputted with English punctuation meaning that all numbers containing commas are replaced with full stops e.g. 1,123 becomes 1.123. However, at my office we use Excel with Danish punctuation which may cause trouble when coding?
10. I have previously encountered a similar problem which was solved by Marc from this site. I have attached his code which might be helpful. I do not know how to code myself and I am grateful for whatever help I can get.
Best wishes,
Frederik
MOD EDIT: PLEASE USE CODE TAGS WHILE POSTING CODE SNIPPETS.
I am looking for a makro that copies/transposes data from one workbook to another by checking headers and only copies/transposes data from specific columns. In the following example, I use “workbook2” and “workbook3”, but the makro should be accessible from all workbooks.
Problem/Workflow:
1. Program data for 1 client is outputted as “Clientsname.csv” which is opened in Excel workbook2. I have many clients and all clients have their own workbook.
2. Data from workbook2 is then manually copied/transposed to workbook3 which functions as the “master workbook” containing all clients’ data. I hope that you can help me make this step automatic by coding a makro.
3. Program data varies from client to client which is why the code needs to check headers. Furthermore, I only want to copy some data from column B and all data from the column with the lowest value stated in row 2.
4. For a client with all data available, I need the code to copy/transpose data from column B row 3-5 workbook2 till column A-C row 2 workbook3. It also needs to copy/transpose data from workbook2 row 6-123 in “the column with the lowest value stated in row 2” (max 200 columns should be searched) till column D-DQ row 2 workbook3. When there are 2 or more columns with equally low values in workbook2 the first column should be used.
5. For a client with missing data the copy/transpose function should still search column B row 3-5 and row 6-123 in “the column with the lowest value in row 2”, but the code should put a “.” in workbook 3 when data is missing.
6. I have uploaded two pairs of Workbook2 and Workbook3. One pair with all data and one pair with data missing.
7. In both examples, the column with the lowest value in row 2 workbook2 is column H and is together with column B in workbook2 the only two columns being copied to row 2 column A-DQ in workbook3.
8. In the example with missing data, cells named A10-A18 and corresponding data are missing in workbook2 and are therefore filled with a “.” in workbook3.
9. Data is outputted with English punctuation meaning that all numbers containing commas are replaced with full stops e.g. 1,123 becomes 1.123. However, at my office we use Excel with Danish punctuation which may cause trouble when coding?
10. I have previously encountered a similar problem which was solved by Marc from this site. I have attached his code which might be helpful. I do not know how to code myself and I am grateful for whatever help I can get.
Code:
SubDemo3()
DimF$, oDicAsObject, L&
F = ThisWorkbook.Path &"\workbook1 revised.xlsx"
IfDir(F) =""ThenBeep:ExitSub
SetoDic =CreateObject("Scripting.Dictionary")
WithGetObject(F).Worksheets(1).[A1].CurrentRegion
ForL = 2To.Rows.Count
F = .Cells(L, 1).Value & .Cells(L, 4).Value
IfoDic.Exists(F)ThenF ="#":ExitFor
oDic(F) = .Cells(L, 9).Value
Next
.Parent.Parent.CloseFalse
EndWithIfF ="#"Then
MsgBox"Duplicate in row #"& L, vbExclamation," workbook1 Day1 :"Else
WithRange("DAY1!A1").CurrentRegion
ForL = 2To.Columns.Count
F = .Cells(1, L).Value & .Cells(2, L).Value
IfoDic.Exists(F)Then.Cells(3, L).Value = oDic(F)Else.Cells(3, L).Value ="."
Next
EndWithEndIf
oDic.RemoveAll
SetoDic =NothingEndSubBest wishes,
Frederik
MOD EDIT: PLEASE USE CODE TAGS WHILE POSTING CODE SNIPPETS.
Last edited by a moderator: