krisbowls
02-21-2018, 12:57 PM
Hi I'm new to VBA and have been trying to learn looping, but with little success. I created a macro with the "Record Macro" button in Excel, but would like to create a loop instead.
Basically, the Macro I have creates two new rows below data, then two more, then two more etc. I would like to make a loop so that it does this until it encounters the last row with data.
After that, I currently have another line of code copying the data in the first cell of the first row of each set of three rows and pasting into the two newly created cells beneath it.
The reason for this so that the data in a given column can then be copied and pasted into a different workbook and fit in with it's formatting.
Below is an example of what was recorded with the "Record Macro" button:
'Now, add spaces between PRC CTs from wb1:
wb1.Sheets(1).Activate
Range("H5:H6").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H8:H9").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H11:H12").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H14:H15").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
I just have this repeating 12 times, but want a loop until the last row of data instead as I discussed above.
Below is an example of the copy and paste I discussed. I'd like to learn how to convert this into a loop as well.
'Now, repeat PRC ct results in triplicate:
wb1.Sheets(1).Range("H4").Copy
wb1.Sheets(1).Range("H5").PasteSpecial
wb1.Sheets(1).Range("H6").PasteSpecial
wb1.Sheets(1).Range("H7").Copy
wb1.Sheets(1).Range("H8").PasteSpecial
wb1.Sheets(1).Range("H9").PasteSpecial
wb1.Sheets(1).Range("H10").Copy
wb1.Sheets(1).Range("H11").PasteSpecial
wb1.Sheets(1).Range("H12").PasteSpecial
As an example, here is a screen grab of what these two codes work together to do:
21676 Before the codes ran, it was simply three rows that read: 30.5409, 32.2871, 0.
Thanks for any advice or suggestions!
Kris
Basically, the Macro I have creates two new rows below data, then two more, then two more etc. I would like to make a loop so that it does this until it encounters the last row with data.
After that, I currently have another line of code copying the data in the first cell of the first row of each set of three rows and pasting into the two newly created cells beneath it.
The reason for this so that the data in a given column can then be copied and pasted into a different workbook and fit in with it's formatting.
Below is an example of what was recorded with the "Record Macro" button:
'Now, add spaces between PRC CTs from wb1:
wb1.Sheets(1).Activate
Range("H5:H6").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H8:H9").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H11:H12").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("H14:H15").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
I just have this repeating 12 times, but want a loop until the last row of data instead as I discussed above.
Below is an example of the copy and paste I discussed. I'd like to learn how to convert this into a loop as well.
'Now, repeat PRC ct results in triplicate:
wb1.Sheets(1).Range("H4").Copy
wb1.Sheets(1).Range("H5").PasteSpecial
wb1.Sheets(1).Range("H6").PasteSpecial
wb1.Sheets(1).Range("H7").Copy
wb1.Sheets(1).Range("H8").PasteSpecial
wb1.Sheets(1).Range("H9").PasteSpecial
wb1.Sheets(1).Range("H10").Copy
wb1.Sheets(1).Range("H11").PasteSpecial
wb1.Sheets(1).Range("H12").PasteSpecial
As an example, here is a screen grab of what these two codes work together to do:
21676 Before the codes ran, it was simply three rows that read: 30.5409, 32.2871, 0.
Thanks for any advice or suggestions!
Kris