Copying Variable Data Ranges to Another Sheet

[kirk5370]

Hi everyone!

Thanks for letting me join your community! I'm very happy to be here, and hope that we can all support one-other with our Excel problems/opportunities. I am relatively new to VBA and am building a process model - but have run into my first major issue. I need help to import some baseline data from another work sheet. My problem is that not all this "baseline data" is required from the one worksheet.

I.E.

- I need to pull 5 non-sequential columns from a worksheet with 10 columns of data
- This baseline data worksheet will not be an object
- I will need to copy data from every row that has data, and the data can not lose relative position to the other columns (i.e. Data from Row 1, must be beside the other data from Row 1 on destination sheet)
- I would like to stop pulling data once the first row without data is identified.
- I cannot use Range("A2").CurrentRegion because this will select more columns than I need - but I think this selects the correct number of rows.
- Ideally I would have the flexibility to copy+paste each column seperately, as their position relative to one-other in the destination sheet is changing.

 

[kirk5370]

Hi everyone - this is what I came up with. Hoping some people can tell me if there is a more efficient way of doing this.



Sub ImportBorderOfLine()


starting by clearing the old work area so old data is not left - this fails when there is a break in data on the A -column? any suggestions?

ActiveWorkbook.Worksheets("Cycle Data").Select
Range("A8").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents

now I start my for loop to identify the relevant columns by column ID

ActiveWorkbook.Worksheets("Border of Line").Select

For i = 1 To 100
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row

'DataRange = Range(Cells(5, i), (Cells(FinalRow, i)))


If Cells(4, i) = "Point of Use ID" Then

Range(Cells(5, i), (Cells(FinalRow, i))).Select
Selection.Copy
Sheets("Cycle Data").Select
Range("B8").Select
ActiveSheet.Paste
ActiveWorkbook.Worksheets("Border of Line").Select

ElseIf Cells(4, i) = "Hourly Rate" Then


Range(Cells(5, i), (Cells(FinalRow, i))).Select
Selection.Copy
Sheets("Cycle Data").Select
Range("E8").Select
Selection.PasteSpecial xlPasteValues
ActiveWorkbook.Worksheets("Border of Line").Select

ElseIf Cells(4, i) = "Part #" Then

Range(Cells(5, i), (Cells(FinalRow, i))).Select
Selection.Copy
Sheets("Cycle Data").Select
Range("C8").Select
ActiveSheet.Paste
ActiveWorkbook.Worksheets("Border of Line").Select

ElseIf Cells(4, i) = "Delivery Route / Forklift Zone" Then

Range(Cells(5, i), (Cells(FinalRow, i))).Select
Selection.Copy
Sheets("Cycle Data").Select
Range("A8").Select
ActiveSheet.Paste
ActiveWorkbook.Worksheets("Border of Line").Select

ElseIf Cells(4, i) = "Standard Pack" Then

Range(Cells(5, i), (Cells(FinalRow, i))).Select
Selection.Copy
Sheets("Cycle Data").Select
Range("D8").Select
ActiveSheet.Paste
ActiveWorkbook.Worksheets("Border of Line").Select

ElseIf Cells(4, i) = "Lineside Max Capacity(Hours)" Then

Range(Cells(5, i), (Cells(FinalRow, i))).Select
Selection.Copy
Sheets("Cycle Data").Select
Range("G8").Select
ActiveSheet.Paste
ActiveWorkbook.Worksheets("Border of Line").Select

ElseIf Cells(4, i) = "Lineside Min Capacity(Hours)" Then

Range(Cells(5, i), (Cells(FinalRow, i))).Select
Selection.Copy
Sheets("Cycle Data").Select
Range("F8").Select
ActiveSheet.Paste
ActiveWorkbook.Worksheets("Border of Line").Select

End If

Next i

Worksheets("Cycle Data").Activate

End Sub

 

[Joe4]

starting by clearing the old work area so old data is not left - this fails when there is a break in data on the A -column? any suggestions?

ActiveWorkbook.Worksheets("Cycle Data").Select
Range("A8").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents

Try this:

ActiveWorkbook.Worksheets("Cycle Data").Select
Range("A8:" & Range("A8").SpecialCells(xlLastCell).Address).ClearContents