Include loop in macro until blank cell

ccofmars

New Member
Joined
May 20, 2010
Messages
44
Hi, can someone help me? please.... It may be very easy for you all but i am having an issue including a loop in this macro. I am trying to do the same thing fro each row until it reaches a blank row. then i just want the marco to stop.

the macro is copying a cell from one tab and paste it to another.
there are about 500 rows, so I dont want to record for every row on teh sheet. Besides, from one day to another, i do not have the same numebr of rows. thank you all so much for your assitance with this :)


Sub CopyPasteWithLoop()
Sheets("DATA DUMP").Select
Range("A3").Select
Selection.Copy
Sheets("CALCULATOR").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E5:J5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DATA DUMP").Select
Range("J3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A4").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("CALCULATOR").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E5:J5").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("DATA DUMP").Select
Range("J4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A5").Select

End Sub
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
There is a problem with your code there.

I can see you are pasting into calculator C4 on two occasions so overwriting your first instruction. I presume this isnt intentional.

Maybe if you supplied the tables with what you wanted and where you wanted it it may be easier?
 
Upvote 0
thank you for the reply. It is intentional to paste in cell C4 of the "calculator" tab. once the value is pasted in cell C4, i copy the value E5:J5 from the calculator tab and paste it in column J of the "datadump" tab.

the calculator tab remains the same. what has different rows is the "data dump" tab. hope this makes sense.
 
Upvote 0
What I tried to say is that the cells in the "calculator" tab stays constant. The value in column A of the Data Dump tab gets copied, then pasted in C4 of the calculator tab. The cells E5:J5 from the calculator gets copied and pasted in the column J of the Data Dump tab. I will need the macro to stop when it reaches a blank row. i hope that makes more sense now. thank you in advance for your assistance.
 
Upvote 0
Try this:

Code:
Sub CopyPasteWithLoop()

Dim shData As Worksheet, shCalc As Worksheet, x As Integer, i As Integer
Set shData = Sheets("DATA DUMP")
Set shCalc = Sheets("CALCULATOR")
x = shData.Range("A" & Rows.Count).End(xlUp).Row
For i = 3 To x
    shData.Range("A" & i).Copy
    shCalc.Range("C4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    shCalc.Range("E5:J5").Copy
    shData.Range("J" & i).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
     
Next i
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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
Back
Top