Copying Variable Data Ranges to Another Sheet

kirk5370

New Member
Joined
May 14, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

kirk5370

New Member
Joined
May 14, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
60,028
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
ActiveWorkbook.Worksheets("Cycle Data").Select
Range("A8:" & Range("A8").SpecialCells(xlLastCell).Address).ClearContents
 

Forum statistics

Threads
1,147,481
Messages
5,741,400
Members
423,657
Latest member
Medrok2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top