General discussion

Locked

Excel: Mapping fields 2 specifc layout

By michaelh ·
I need some advice on some resources. I have 2 excel documents one is a raw file with a lot of data, the other is a company formatted document. I need to create a mapping between the two documents. The dynamic part of this is the raw data is never the same in respects to the number of rows; however the columns are always the same.

Anyone have any suggestions on how I should approach this?

Thanks in advance

This conversation is currently closed to new comments.

5 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel: Mapping fields 2 specifc layout

by DKlippert In reply to Excel: Mapping fields 2 s ...

You might want to use Offset to create a dynamic range.
See
www.cpearson.com/excel/excelF.htm

Collapse -

Excel: Mapping fields 2 specifc layout

by michaelh In reply to Excel: Mapping fields 2 s ...

Poster rated this answer

Collapse -

Excel: Mapping fields 2 specifc layout

by FirstPeter In reply to Excel: Mapping fields 2 s ...

Let's try a macro for this (put it in the report template):

Sub Gather_Data()
Dim strDataWorkbook, strReportWorkbook
Dim i As Long

' Set the row that data starts on (in both the data and report workbooks)
Const DATA_START_ROW = 2
Const REPORT_START_ROW = 2

i = DATA_START_ROW

' Set the current workbook to the Report Workbook
strReportWorkbook = ActiveWorkbook.Name

' Prompt for the name of the data workbook
strDataWorkbook = InputBox("Please enter worksheet name: ", "Data Sheet")

' Turn off interim screen updates for the sake of speed
Application.ScreenUpdating = False

' Switch over to the data worksheet
Worksheets(strDataWorkbook).Activate

' As long as there is not a row that has a blank in column A,
' go through the rows and copy the data over to the report workbook.
' This assumes that all the columns match between the two. If not,
' you can copy individual cells instead of the rows.
Do While Range("A" & i).Value <> ""
Rows(i).Copy
Worksheets(strReportWorkbook).Worksheets(strReportWorksheet).Rows(i + REPORT_START_ROW - DATA_START_ROW).PasteSpecial xlPasteValues
i = i + 1
Loop

' Turn interim screen updates back on
Application.ScreenUpdating = True
End Sub

This may require a little tweaking - feel free to send an e-mail if you'd like some help with that. Good luck!

Collapse -

Excel: Mapping fields 2 specifc layout

by michaelh In reply to Excel: Mapping fields 2 s ...

Poster rated this answer

Collapse -

Excel: Mapping fields 2 specifc layout

by michaelh In reply to Excel: Mapping fields 2 s ...

This question was closed by the author

Back to Software Forum
5 total posts (Page 1 of 1)  

Related Discussions

Related Forums