Select range between blank cells and copy to different worksheet

knittelmail

New Member
Joined
Jun 28, 2023
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hello and thank you in advance.
I am trying to pull information from one sheet "Report" and put it into a different sheet "Formulas". Unfortunately the cell locations in the two sheets are not identical so I can't do a simple copy and paste. I can do a lot of complicated cutting and pasting, but I am sure that VBA can help me simplify the process. I am too new to VBA and just can't seem to figure it out. Please help.
Here is a summary of what I am trying to do:
Please let me know if this isn't clear or is impossible

with a range of A21:A123 in worksheet "Report"
starting with A21 search down to the first blank cell (lets call it A36)
select a range of cells based on the cells in the search that have contents
lets say that A21-A35 have something in them - the range to select would be A21:Z35
copy A21:Z35 and paste it to a worksheet "Formulas"
the upper left cell of the range to paste into is specific in Sheet "Formulas" A28
the paste need to be paste special with values only and skip blanks so I don't overwrite the formulas in "Formulas"

skip down to the next non-blank cell and repeat
start with A37 "Report" and search to the next blank
select the range from A37:Z**
copy and paste those cells into "Formulas" at A55

repeat to the bottom of A21:A123
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hello and thank you in advance.
I am trying to pull information from one sheet "Report" and put it into a different sheet "Formulas". Unfortunately the cell locations in the two sheets are not identical so I can't do a simple copy and paste. I can do a lot of complicated cutting and pasting, but I am sure that VBA can help me simplify the process. I am too new to VBA and just can't seem to figure it out. Please help.
Here is a summary of what I am trying to do:
Please let me know if this isn't clear or is impossible

with a range of A21:A123 in worksheet "Report"
starting with A21 search down to the first blank cell (lets call it A36)
select a range of cells based on the cells in the search that have contents
lets say that A21-A35 have something in them - the range to select would be A21:Z35
copy A21:Z35 and paste it to a worksheet "Formulas"
the upper left cell of the range to paste into is specific in Sheet "Formulas" A28
the paste need to be paste special with values only and skip blanks so I don't overwrite the formulas in "Formulas"

skip down to the next non-blank cell and repeat
start with A37 "Report" and search to the next blank
select the range from A37:Z**
copy and paste those cells into "Formulas" at A55

repeat to the bottom of A21:A123
Could you share a copy of your workbook via Dropbox, Google Drive or similar file sharing platform? You can easily disguise any sensitive data.
 
Upvote 0
It seems that you want to copy all the content within the cells in the range A1:Z till the last row with data in column A of the Report sheet. Then, you want to paste the values into the Formulas sheet, starting from cell A28 and going downwards.
Try below suggestion:
VBA Code:
Dim lr&,i&,j&,k&,rng,res()
With sheets("Report")
   lr = .cells(rows.count,"A").end(xlup).row
   rng = .range("A1:Z" & lr).value
End with
Redim res(1 to ubound(rng),1 to ubound(rng,2))
For i =1 to ubound(rng)
    If Not isempty(rng(i,1)) then
        k=k+1
        For j=1 to ubound(rng,2)
              res(k,j) = rng(i,j)
        Next
    End if
Next
With sheets("Formulas")              
    .range("A28:Z100000").ClearContents
    .range("A8").Resize(k,ubound(res,2)).value = res
End with
 
Upvote 0
It seems that you want to copy all the content within the cells in the range A1:Z till the last row with data in column A of the Report sheet. Then, you want to paste the values into the Formulas sheet, starting from cell A28 and going downwards.
Try below suggestion:
VBA Code:
Dim lr&,i&,j&,k&,rng,res()
With sheets("Report")
   lr = .cells(rows.count,"A").end(xlup).row
   rng = .range("A1:Z" & lr).value
End with
Redim res(1 to ubound(rng),1 to ubound(rng,2))
For i =1 to ubound(rng)
    If Not isempty(rng(i,1)) then
        k=k+1
        For j=1 to ubound(rng,2)
              res(k,j) = rng(i,j)
        Next
    End if
Next
With sheets("Formulas")             
    .range("A28:Z100000").ClearContents
    .range("A8").Resize(k,ubound(res,2)).value = res
End with
Could you share a copy of your workbook via Dropbox, Google Drive or similar file sharing platform? You can easily disguise any sensitive data.
Thank you bebo021999 and kevin9999 for accepting the challenge.

bebo021999. I changed the ranges and got the code to work on my worksheet. But...
The information I need to transfer is in non-continuous chunks separated by blank rows (not always the same number of them). By the end of the transfer, rows were being pasted into the wrong row. The other thing that happened was all the existing formulas in the paste area went away. I can't tell if this was because of the .ClearContents or because something was written where the formulas were. It is very important to me to keep the fomulas intact.

Ultimately, my goal is to have a worksheet (Formulas) that I can enter into the yellow highlighted cells and have the formulas in the other cells do the calculations for me.
Try this link to see my work. It is not elegant, but hey, it's my first try. Hopefully none of the formulas broke during the upload.

Please let me know if I can make anything more clear. I truly appreciate all the assistance with this project. Thank you!
 
Upvote 0
Thank you bebo021999 and kevin9999 for accepting the challenge.

bebo021999. I changed the ranges and got the code to work on my worksheet. But...
The information I need to transfer is in non-continuous chunks separated by blank rows (not always the same number of them). By the end of the transfer, rows were being pasted into the wrong row. The other thing that happened was all the existing formulas in the paste area went away. I can't tell if this was because of the .ClearContents or because something was written where the formulas were. It is very important to me to keep the fomulas intact.

Ultimately, my goal is to have a worksheet (Formulas) that I can enter into the yellow highlighted cells and have the formulas in the other cells do the calculations for me.
Try this link to see my work. It is not elegant, but hey, it's my first try. Hopefully none of the formulas broke during the upload.

Please let me know if I can make anything more clear. I truly appreciate all the assistance with this project. Thank you!
It occurred to me that I probably need to specify the paste location of the copied chunk of data. The paste locations will remain constant but the copy locations may change.
 
Upvote 0
I thank everyone who considered my question! I have decided that what I really have is multiple problems to solve. If I ever manage to break it down into little chunks, I will ask for help with those pieces. In the meantime, I consider my question closed. Thanks again.
 
Upvote 0
Thanks for letting us know that you don't need further help in this thread.

Just to let you know, the "Mark as solution' at the right of a post is to mark the post that actually answered the original question, not to mark the thread as 'Solved' or 'Closed'.
Therefore I have removed the mark from your post 6.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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