Copy Range in other Sheet when these Cells are empty.

[eagerbeav3r]

Hello everyone,

i got one Main Sheet in which im planning to use Checkboxes to Copy certain Cells from one Sheet into another.
The Contents I want to Copy are in Sheet "NÖ1" and should appear in "NÖ2". When you use it you should decide which "module" should appear in "NÖ2".

This is my Code for now:

Private Sub CheckBox1_Click()

If ActiveSheet.CheckBox1 = True Then

      Sheets("NÖ1").Range("B2:I23").Copy Destination:=Sheets("NÖ2").Range("A1:H22")
      
      Exit Sub
        End If
        
If ActiveSheet.CheckBox1 = False Then

      Sheets("NÖ2").Range("A1:H22").ClearContents
      Sheets("NÖ2").Range("A1:H22").ClearFormats
      
      Exit Sub
      End If
End Sub

I got a button which saves the Sheet "NÖ2" as an PDF file in a specific folder.

My question now is if there is a way to specificly copy the Range in Sheet "NÖ1" in the next clear cell in "NÖ2"? It shouldnt overwrite any other modules and when "Checkbox1 = False" it should only delete the previously pasted module.

Is there a way to do that?

 

[Joe4]

It is a little hard to understand without seeing what your data looks like, and understanding exactly where you are wanting to copy things.
Do you mean copy to the next available blank row in column A?
Then maybe replace this line:

      Sheets("NÖ1").Range("B2:I23").Copy Destination:=Sheets("NÖ2").Range("A1:H22")

with this block:

If Sheets("NÖ2").Range("A1") = "" Then
    Sheets("NÖ1").Range("B2:I23").Copy Destination:=Sheets("NÖ2").Range("A1")
Else
    Sheets("NÖ1").Range("B2:I23").Copy Destination:=Sheets("NÖ2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If

 

[eagerbeav3r]

It is a little hard to understand without seeing what your data looks like, and understanding exactly where you are wanting to copy things.
Do you mean copy to the next available blank row in column A?
Then maybe replace this line:

      Sheets("NÖ1").Range("B2:I23").Copy Destination:=Sheets("NÖ2").Range("A1:H22")

with this block:

If Sheets("NÖ2").Range("A1") = "" Then
    Sheets("NÖ1").Range("B2:I23").Copy Destination:=Sheets("NÖ2").Range("A1")
Else
    Sheets("NÖ1").Range("B2:I23").Copy Destination:=Sheets("NÖ2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If

I replaced it and it works. But only the first Part of the Code. The second Part "

If ActiveSheet.CheckBox1 = False Then

      Sheets("NÖ2").Range("A1:H22").ClearContents
      Sheets("NÖ2").Range("A1:H22").ClearFormats
      
      Exit Sub
      End If

" should only delete the Previously Pasted Contents. The Range that i selected should not be static (if that makes sense)

As previously mentioned, I got about 8 different Modules which should be pasted in Sheet "NÖ2". If the Checkbox 1 is pressed after Checkbox 2, the Contents of Checkbox 1 are pasted after the Contents of Checkbox 2 (your Code fixed that for me) but now when i uncheck Checkbox 1 it deletes the Contents of Checkbox 2 because i set the Range to

Sheets("NÖ2").Range("A1:H22").ClearContents
      Sheets("NÖ2").Range("A1:H22").ClearFormats

If you need a example Spreadsheet i can do that for you...

 

[Joe4]

Yes, please show us some sample data and walk us through an example of what it is that you want to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[eagerbeav3r]

Yes, please show us some sample data and walk us through an example of what it is that you want to do.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Here are some images of the stuff im working with including a walkthrough. Let me know if you need further informations.

 

[Joe4]

I am afraid you lost me on this one.

But perhaps this will help.
If you want to move rows up, I don't think you want to use "ClearContents", as that wipes out the data, but does not move things up.
You need to physically delete the rows to move things up.

 

[eagerbeav3r]

I am afraid you lost me on this one.

But perhaps this will help.
If you want to move rows up, I don't think you want to use "ClearContents", as that wipes out the data, but does not move things up.
You need to physically delete the rows to move things up.

For example: I check the checkbox "Module 1" so Module 1 appears in "Sheet 3". After that i check "Module 2" so it will appear beneath "Module 1". If I uncheck "Module 1" I only want to delete the rows where "Module 1" is located so "Module 2" will be on top in Row 1.

If its not possible I will look for another solution

 

[Joe4]

For example: I check the checkbox "Module 1" so Module 1 appears in "Sheet 3". After that i check "Module 2" so it will appear beneath "Module 1". If I uncheck "Module 1" I only want to delete the rows where "Module 1" is located so "Module 2" will be on top in Row 1.

If its not possible I will look for another solution

Yes, so when you uncheck that box, you will need to physically delete the rows that make up Module1 so that everything else moves up.

If that is too difficult to do, you may have to take a different approach, such as every time a box is checked or unchecked, wipe out EVERYTHING on the sheet and rebuild it.