Copying my raw data across to my presentable sheet

**JDOD** · 04-24-2013, 05:52 AM

Hi,

I have a program that outputs columns of data in a fairly ugly way.

I have a tidy sheet, but I don't want my staff to have to copy and paste each column individually between the ugly sheet and the presentable one.

I want to set up my presentable sheet so that it will automatically copy from the ugly sheet. The problem I have is that the number of entries in the column in the ugly sheet is variable. Sometimes it could be 1000 rows in the column, sometimes it could be 10. So I need a function that will copy from the top to the bottom of the data regardless of how many rows there are.

**:) Sixthsense :)** · 04-24-2013, 05:58 AM

Below inputs are required:-

How many columns? Is it constant? or variable? From Which column to which column?
What is the top row number?
Which column data determines the last row?
From which sheet to which sheet?
Where it needs to be pasted?
Paste area is constant? or variable?

**JDOD** · 04-24-2013, 06:08 AM

The Number of columns is constant.

I want column A to copy into column B. Starting with A2 in the ugly sheet and copying to B10 in the presentable sheet.
Column B, starting at B2, needs to copy into Column C starting at C10.
Column D, starting at D2, needs to copy into Column E starting at E10
Column G, starting at G2, needs to copy into Column D starting at D10

With the exception of column G, there are no gaps in the data in the columns. Column G has frequent gaps in it but the gaps need to be copied across also so that the data still matches up.

**:) Sixthsense :)** · 04-24-2013, 06:32 AM

Try this... change the sheet names in the below code to your file sheet names.

Sub CopyDataToAnotherLocation()
Dim nEndRw As Long, wRaw As Worksheet, wMaster As Worksheet

Set wRaw = Sheets("ugly")
Set wMaster = Sheets("presentable")

With wRaw
    nEndRw = .Range("A2").CurrentRegion.Rows.Count-1
    .Range("A2:A" & nEndRw).Copy wMaster.Range("B10")
    .Range("B2:B" & nEndRw).Copy wMaster.Range("C10")
    .Range("D2:D" & nEndRw).Copy wMaster.Range("E10")
    .Range("G2:G" & nEndRw).Copy wMaster.Range("D10")
End With

End Sub