Copy "A1 Current Region" of WS named "Data" and paste to every worksheet that cell A1 is blank.

[VBAProIWish]

Hello All,

I hope the title was clear enough but here are the logical steps I have in mind to accomplish this...

1. Go to the "Data" WS
2. Select cell "A1"
3. CTRL+A to select the Current Region
4. Copy the Current Region
5. Go to the next worksheet

6. If cell A1 is blank
Paste the data (Current Region from the "Data" WS)

If cell A1 is not blank
Do nothing

7. Repeat 5 and 6 until all worksheets are checked.


Please note that both the number and name of worksheets are dynamic and can change so I can't refer to any worksheets by name.


Thanks much!

 

[BigBeachBananas]

Hello All,

I hope the title was clear enough but here are the logical steps I have in mind to accomplish this...

1. Go to the "Data" WS
2. Select cell "A1"
3. CTRL+A to select the Current Region
4. Copy the Current Region
5. Go to the next worksheet

6. If cell A1 is blank
Paste the data (Current Region from the "Data" WS)

If cell A1 is not blank
Do nothing

7. Repeat 5 and 6 until all worksheets are checked.


Please note that both the number and name of worksheets are dynamic and can change so I can't refer to any worksheets by name.


Thanks much!

Is the "Data" WS name static or it will also be changing?

 

[VBAProIWish]

The "Data" worksheet name is static. Thanks!

 

[BigBeachBananas]

How about this?

Sub copyRegiontoSheets()

Dim wb As Workbook
Dim sourceRng As Range
Dim sourceSheet As Worksheet
Dim ws As Worksheet

Set wb = ThisWorkbook
Set sourceRng = wb.Sheets("Data").Range("A1").CurrentRegion

    For Each ws In wb.Sheets
        If (ws.Name <> "Data") And (ws.Range("A1") <> "") Then
                sourceRng.Copy Destination:=ws.Range("A1")
        End If
    Next ws

End Sub

 

[BigBeachBananas]

There's a typo in the previous code. Updated

Sub copyRegiontoSheets()

Dim wb As Workbook
Dim sourceRng As Range
Dim sourceSheet As Worksheet
Dim ws As Worksheet

Set wb = ThisWorkbook
Set sourceRng = wb.Sheets("Data").Range("A1").CurrentRegion

    For Each ws In wb.Sheets
        If (ws.Name <> "Data") And (ws.Range("A1") = "") Then
                sourceRng.Copy Destination:=ws.Range("A1")
        End If
    Next ws

End Sub

 

[VBAProIWish]

Hi BBB,

Thanks so much for working with me on this! I tried to run the code and even though I have a ws named "Data", it errored on this line here...

Runtime Error 9, Subscript out of range

Set sourceRng = wb.Sheets("Data").Range("A1").CurrentRegion

I don't know if this matters, but I will actually be on the "Data" worksheet when the code is run. I did create a new blank ws and started the code from there just to see if it worked and it still didn't work.

Thanks

 

[BigBeachBananas]

Hi BBB,

Thanks so much for working with me on this! I tried to run the code and even though I have a ws named "Data", it errored on this line here...

Runtime Error 9, Subscript out of range

Set sourceRng = wb.Sheets("Data").Range("A1").CurrentRegion

I don't know if this matters, but I will actually be on the "Data" worksheet when the code is run. I did create a new blank ws and started the code from there just to see if it worked and it still didn't work.

Thanks

It doesn't matter which sheet you're on. Make sure you have a tab called "Data". The error suggests that there's no sheet called "Data" in your workbook.

 

[VBAProIWish]

I triple checked this and there is a worksheet named "Data". I even checked to make sure that there is no space before or after the word "Data" in the worksheet name and there is none.

I even opened up a brand new workbook, created 3 worksheets and manually named one "Data" and I am getting the same error.

Does the workbook name have anything to do with this?

Thanks

 

[BigBeachBananas]

I triple checked this and there is a worksheet named "Data". I even checked to make sure that there is no space before or after the word "Data" in the worksheet name and there is none.

I even opened up a brand new workbook, created 3 worksheets and manually named one "Data" and I am getting the same error.

Does the workbook name have anything to do with this?

Thanks

Run this code look at the Immediate window and see if it prints Data. Also make sure the code is in the right workbook module, if you have multiple open.

Sub printSheetnames()
    Dim wb As Workbook
    Dim ws As Worksheet
  
    Set wb = ThisWorkbook
    For Each ws In wb.Sheets
        Debug.Print ws.Name
    Next ws
End Sub

 

[VBAProIWish]

That is very interesting. It is pulling the worksheet names from the hidden workbook that I have all my modules in. It's still hidden and I made sure the focus was on my "test" workbook with a worksheet named "Data" before trying the macro.