Copy and Paste into New Workbook - Range Determined in another Workbook 1

[Alastair1990]

Hello,

I want to copy a range of rows from workbook 1 into workbook 2.

The range of values I want to copy from workbook 1 into workbook 2 is calculated in workbook 3. My range is being displayed as: 2:173

How can I do this?

Thanks!

 

[SkipVought]

hi,

There are a number of unstated criteria like

what SHEET in each workbook? I'm assuming the FIRST sheet.

What range in workbook3 contains the copy range, which I assume is TEXT? I'm assuming A1

Where does the PASTE go in workbook2? I'm assuming A1.

I'm also assuming that all 3 workbooks are OPEN and are named Workbook1.xlsx, Workbook2.xlsx & Workbook3.xlsx respectively

So with thse asumptions...

Workbooks("Workbook1.xlsx").Sheets(1).Range(Workbooks("Workbook3.xlsx").Sheets(1).Range("A1")).Copy  _
    Workbooks("Workbook2.xlsx").Sheets(1).Range("A1")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Alastair1990]

Apologies,

I meant worksheet not workbook. The data is all contained within the same workbook.

I want to select rows in worksheet 1, the range of rows is determined in worksheet2 and paste them into A1 worksheet 3.

This will be repeated multiple times as there are multiple worksheets and corresponding row ranges, but once I have the code for the first I can manipulate it or the following events.

Thanks,

Alastair

 

[Alastair1990]

My row ranges look like this:

2:173
174:326
327:804
805:1260
1261:1162
1163:1634
1635:1722
1723:2172
2173:2307
2308:2329
2330:2468
2469:2558
2559:2929
2930:3032
3033:3135
3136:3500

Is this the right format? They start from C:20 down to C:36 in worksheet 2.

Your help will be very much appreciated

Alastair

 

[SkipVought]

So, this raises some questions.

No headings? What is a sheet without headings?

What do 2:173 have in common that other rows do not? What I'm driving at is that TODAY 2:173 may be related in some logical way, but TOMORROW the rows that are logically related may be 2:187.

Is there a reason for chopping up your data like this? There may be other methods of presenting your data depending on the ultimate necessities.

Please answer each and every question.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Alastair1990]

I am exporting data from MS project into excel to make charts. The charts graphically monitor the project progress.

The project is concerned with planning the build of a car.

At the moment the data that is exported is not currently grouped into the main car/project areas, therefore my chart shows the entire project, not specifically suspension, engine, bodywork etc. I want to see where the project has deviated from the plan in the individual project areas as opposed to the complete build as already achieved.

I therefore need to select the categories and subsequent tasks and paste them into a separate worksheet so that I can make further formatting changes to create the charts I require.

As the project changes from day to day, the number of tasks may increase or decrease. Using ADDRESS and MATCH functions I can determine the cell reference and subsequently the row no. of the main categories. The row range is therefore the main category row number down to the subsequent main category row number minus one.

I therefore have the row range sorted and this updates automatically.

Mu current workbook headings are:

Worksheet 1: Work_Completed_
Worksheet 2: Row data
Worksheet 3: Front Wing

Does this answer your questions?

Thanks

 

[Alastair1990]

The copy and paste part is simple using macro recorder when selecting the data directly from "Work_Completed_" and subsequently the macro is generated for this.

I do not have the skill to make excel read the range in "row data" rather than the rows physically selected in "Work_Completed_"

Thanks,

 

[SkipVought]

this might work for you...

Sub LoadChartData()
    Dim r As Range, iCopyT0 As Integer
'assumptions:
'   sheets(1) plot data, sheets(2) range data: are the given sheets in your spec
'   the copy to sheets will begin with index 4

    iCopyT0 = 4
    With Sheets(2)
        For Each r In .Range(.Cells(20, "C"), .Cells(20, "C").End(xlDown))
            Sheets(1).Range(r.Value).Copy Sheets(iCopyT0).[A1]
            iCopyT0 = iCopyT0 + 1
        Next
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

sorry, the starting destination sheet should be 3 and not 4

Sub LoadChartData()
    Dim r As Range, iCopyT0 As Integer
'assumptions:
'   sheets(1) plot data, sheets(2) range data: are the given sheets in your spec
'   the copy to sheets will begin with index [s]4[/s] [b]3[/b]

    iCopyT0 = [s]4[/s] [b]3[/b]
    With Sheets(2)
        For Each r In .Range(.Cells(20, "C"), .Cells(20, "C").End(xlDown))
            Sheets(1).Range(r.Value).Copy Sheets(iCopyT0).[A1]
            iCopyT0 = iCopyT0 + 1
        Next
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Alastair1990]

Such a beauty. Works a treat.

Thanks a lot!