How to copy data dynamically via VBA

[Howdy1]

Hi,

I am stuck in the VBA coding to figure out how I can copy two rows of data dynamically within the same spreadsheet. I got some suggestion earlier but still can't make it to work. There are two columns of data (ColA and ColB) that I need to copy dynamically depending on the next empty cell. For example, ColA will have the TotalRegion and then the next row will be empty for the same column until it hit another Region. I need to VBA to copy the data all the way down if there is data. In another word, if ColA and ColB have data, the rest of the rows should be filled up as well.

A generous gentleman offered the following code earlier but I try to understand where I can insert the copy and paste data funtion. In another words, how can I determine where I copy the data and paste it with the loops? Sorry, but I am quite new to VBA and just try to learn VBA via recording the macro.

Thanks a million for all of the help!

Here is the code I tried to use

Sub CopyData()
Dim cl As Range
Range("A2: B2").Select
Selection.Copy
For Each cl In Range("$A$2:$A" & Range("$B$24").End(xlUp).Row)
If cl = "" Then
Selection.Paste
cl = Cells(cl.Row - 1, 1)
Else
Range(cl, Cells(cl, 1)).Copy ' this prompts error, how can I determine
the next cell to copy as I need to copy
two columns
End If
Next cl
End Sub

 

[VoG]

Please post a picture of your sheet using Excel Jeanie then explain exactly what you are trying to do.

 

[Howdy1]

Sorry, I didn't have the Admin right in my computer to install the software. But let me try to explain succintly. For column A and Column B, these act as like the subtotal line. For each subtotal, the name for Column A and Column B exists. However, all the rest of the column A and column B are empty before it has the subtotal. When I need to do is to fill in all the name for the column A and column B so that I can import the data into my Access table to run query.

Example


ColA ColB Amount
TotReg

 

[VoG]

Select all of your data in columns A and B.

Press F5.

Click Special.

Tick Blanks and click OK

Press =

Press Up arrow

Hold down CTRL and press Enter.

 

[Howdy1]

Wow.... That is really powerful!!!!

I have been trying to copy and paste 42000 records manually for the paste few hours and it drove my crazy. Can I translate this into the VBA code via recording the Macro?

You save me tons of hours and headache. Many thanks to you!!!!

 

[VoG]

Yes it can be recorded using a macro. Do that and paste the code and we can tidy it up for you.