1

I am working with a column of data in which the range (e.g. how many rows of data there'll be) will eventually vary for each new sheet that a user creates.

On one sheet, the range in B may be from B3:B7, on another, it may be from B3:B22 for example.

I want a Macro that copies the data from the range B3:Bn, with n being the last row before there is a blank cell in the next row. For example, Copy B4:B7, and in B8 there is a blank cell. I want to paste this range into another workbook, in column C.

I'm only a beginner in VBA and have not gotten far with the code. I have tried several iterations of a loop but it doesn't really work.

This is the weak code i have so far. Any help is appreciated.

Sub main()
    Dim r As Range

    Set r = Range("B3")
    Do While r.Value <> ""
        Range("C").Value = r.Value
        Set r = r.Offset(1)
    Loop
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
LJed
  • 9
  • 1
  • 2
  • 1
    Find the last row and create the range to copy. [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) will get you started. – Siddharth Rout Jul 11 '19 at 03:22

2 Answers2

4

See this is a Simple code to get you started. Change the Names accordingly

Sub main()

Dim wba As Workbook
Dim wbb As Workbook

Set wba = Workbooks("Workbook A")
Set wbb = Workbooks("Workbook B")

with wba.Worksheets("Worksheet in A")
      .Range("B3", .Range("B3").End(xlDown)).Copy
End With

wbb.Worksheets("Worksheet in B").Range("C3").PasteSpecial xlPasteValues

End Sub

A is the workbook with Data, B where you have to copy.

Change the sheet names on both Workbooks, and Range("B3", Range("B1").End(xlDown)) will select the range till a blank cell.

Mikku
  • 6,538
  • 3
  • 15
  • 38
  • I think this is a great start - however, when I try to run it, I get a "Run-time error '9' Subscript out of range" error – LJed Jul 11 '19 at 04:42
  • on which line ? – Mikku Jul 11 '19 at 04:47
  • It doesn't give me a specific line, an error message pops up with "Subscript out of range" – LJed Jul 11 '19 at 04:48
  • When the Error Window pops up, select Debug, it will show you which line is causing the Error. – Mikku Jul 11 '19 at 04:50
  • Line 9: wba.Worksheets("Sheet1").Range("B3", Range("B3").End(xlDown)).Copy – LJed Jul 11 '19 at 05:11
  • line looks correct to me. Make sure the Sheet Name is Correct and there is data in the range B3:Bn – Mikku Jul 11 '19 at 05:16
  • There's an error in line 9. I think it should read `Range(wba.Sheets("Sheet1").Range("B3"),wba.Sheets("Sheet1").Range("B3")).End(xlDown)).Copy` – pgSystemTester Jul 12 '19 at 08:36
  • That should not result in an error, though you are right, i should have used complete reference. I will Edit the Answer now. – Mikku Jul 12 '19 at 08:42
  • @LJed , I have made a change in the line, See if it works now. :) – Mikku Jul 12 '19 at 08:43
1

I just want to add one thing to Mikku's code. Instead of xlDown, in this case xlUp might be better. Because there might be one cell blank and next cell is not. When looking for last row, xlUp is good practice.

wba.Worksheets("Worksheet in A").Range("B3", Range("B" & Rows.Count).End(xlUp)).Copy
Aman
  • 174
  • 3
  • 13
  • You are right, but in the question User is not asking for the `LastRow`, he is asking for the row before the Blank cell. Read the Question again, This is what I Understood. – Mikku Jul 11 '19 at 04:09
  • @Aman: to be on a safer side you have to fully qualify the cells. `wba.Worksheets("Worksheet in A").Range("B3", wba.Worksheets("Worksheet in A").Range("B" & wba.Worksheets("Worksheet in A").Rows.Count).End(xlUp)).Copy` See the link below the question. It explains about qualifying the range. – Siddharth Rout Jul 11 '19 at 04:33
  • @Mikku The user did not specifically said last row but the 1st sentence makes it clear that it is last row. But then I guess lets see what the user has to say :) – Siddharth Rout Jul 11 '19 at 04:35
  • I am not asking for the LastRow, rather, copying the range before there is a blank cell. For example, B3:B23 have values, but if there is a blank cell in B24, I would stop at B23 and copy B3:B23 to the C column of the other workbook. – LJed Jul 11 '19 at 04:51
  • It is clear enough, so Mikku's code fits perfect to your aim. – Aman Jul 11 '19 at 06:43