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!

 

[VBAProIWish]

I'm not sure if this helps or not, but I need to save this code in the master workbook that I keep all my other modules and subs in, so I can't have this code in the workbook that I want to run the code against.

In other words
I want to run the code from a hidden workbook named "Codeit", but actually apply the code to the newly created and active workbook.

 

[BigBeachBananas]

I'm not sure if this helps or not, but I need to save this code in the master workbook that I keep all my other modules and subs in, so I can't have this code in the workbook that I want to run the code against.

In other words
I want to run the code from a hidden workbook named "Codeit", but actually apply the code to the newly created and active workbook.

Where does the Data tab live? In the Codeit or the new workbook?

 

[VBAProIWish]

The new workbook

 

[VBAProIWish]

So I confirmed that is the issue. I pasted your code into the new workbook, ran the code from there and it worked!
So the question now is how can we make that code run from my hidden "Codeit" workbook but execute the code in the active workbook?

Also, even though all the data was indeed pasted into each blank worksheet as intended, it didn't keep any of the column widths, row heights, etc. Is there a way to keep all that when the macro pastes the data into each worksheet?

Thanks so much!

 

[VBAProIWish]

I figured out part of it!

I changed...

"Set wb = ThisWorkbook"

to

"Set wb = activeworkbook"

The only issue left is to keep all the formatting when pasting...

 

[BigBeachBananas]

Change this

sourceRng.Copy Destination:=ws.Range("A1")

to

sourceRng.Copy
ws.Range("A1").PasteSpecial xlPasteAll
Application.CutCopyMode = False

 

[VBAProIWish]

Thanks but it's still not keeping things like column width for example

 

[BigBeachBananas]

Here's an alternative. Run the code from "Codeit", file browser popups for selection for the workbook you want to perform on.

Sub RunMacroOnNewWorkbook()
    Dim FileDialog As FileDialog
    Dim SelectedFile As Variant
    Dim NewWb As Workbook
    Dim sourceRng As Range
    Dim sourceSheet As Worksheet
    Dim ws As Worksheet

    Set FileDialog = Application.FileDialog(msoFileDialogFilePicker)

    If FileDialog.Show = -1 Then
        SelectedFile = FileDialog.SelectedItems(1)
    Else
        Exit Sub
    End If

    If SelectedFile <> "" Then
        Set NewWb = Workbooks.Open(SelectedFile)
        Set wb = NewWb
        Set sourceSheet = wb.Sheets("Data")
        Set sourceRng = sourceSheet.Range("A1").CurrentRegion

        ' Loop through sheets in the opened workbook and copy data, column widths, and row heights.
        For Each ws In wb.Sheets
            If (ws.Name <> "Data") And (ws.Range("A1") ="") Then
                sourceRng.Copy
                ws.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                sourceRng.Copy
                ws.Range("A1").PasteSpecial Paste:=xlPasteColumnWidths
                sourceSheet.Rows.RowHeight = ws.Rows.RowHeight
                sourceSheet.Columns.ColumnWidth = ws.Columns.ColumnWidth
            End If
        Next ws

        NewWb.Close SaveChanges:=True
    Else
        MsgBox "No file selected."
    End If
End Sub

 

[VBAProIWish]

Thanks for all that code, but I don't want to have to browse to anything. I just want to run the macro on the active workbook and be done. It will always be on the active workbook.

With big assistance from you, BBB (Thank you!), we managed to get the code working (see below) for everything except for 2 things...
How can the code below be modified to...
1. Freeze panes on the first row on every worksheet that the data from the "Data" ws was pasted to.
2. Zoom each worksheet that the data was pasted to, to 80% (it defaults to "100%")

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

Set wb = activeworkbook

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
   
ws.Range("A1").PasteSpecial xlPasteColumnWidths
ws.Range("A1").PasteSpecial xlPasteAll

Application.CutCopyMode = False
                
        End If
    Next ws

End Sub

Thanks much!

 

[BigBeachBananas]

ws.Rows("1:1").Select
ActiveWindow.FreezePanes = True
ActiveWindow.Zoom = 80