Select range between blank cells and copy to different worksheet

[knittelmail]

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

 

[kevin9999]

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.

 

[bebo021999]

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:

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

 

[knittelmail]

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:

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.

PR test share copy.xlsm

drive.google.com

Please let me know if I can make anything more clear. I truly appreciate all the assistance with this project. Thank you!

 

[knittelmail]

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.

PR test share copy.xlsm

drive.google.com

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.

 

[knittelmail]

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.

 

[Peter_SSs]

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.