read and copy line from master tab to newly created tab

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
173
Office Version
  1. 365
Platform
  1. Windows
I'm trying to take a master tab,
1. read column A,
2. create a tab in that workbook with the name found in column a,
3. then copy that one line from the master into the newly created tab.

I have the code (below) that cycles thru column A and creates the tabs but having issue with copying the first line into the newly created workbook. Any suggestions?

Thanks

VBA Code:
Sub NewSheets()
Dim rng As Range
Dim cell As Range
Dim wb As Workbook
Dim ws As Worksheet
On Error GoTo Errorhandling
Set rng = Application.InputBox(Prompt:="Select cell range:", _
Title:="Create sheets", _
Default:=Selection.Address, Type:=8)
'Iterate through cells in selected cell range
For Each cell In rng
    'Check if cell is not empty
    If cell <> "" Then
        'Insert worksheet and name the worksheet based on cell value
        Sheets.Add.Name = cell 
    End If
'Continue with next cell in cell range
Next cell
'Go here if an error occurs
Errorhandling:
'Stop macro
End Sub

'code to move only the line from master tab where we got the name of the tab to newly created tab

' Master Tab
'A B C
'A01 Cars Red
'B01 Trucks Blue
'C01 Boats White

' New tabs
' A01 <-tabname
'A B C
'A01 Cars Red
' B01 <-tabname
'A B C
'B01 Trucks Blue
' C01 <-tabname
'A B C
'C01 Boats White
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,219
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
    If cell <> "" Then
        'Insert worksheet and name the worksheet based on cell value
        Sheets.Add.Name = cell
        cell.EntireRow.Copy ActiveSheet.Range("A1")
    End If
 
Solution

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
173
Office Version
  1. 365
Platform
  1. Windows
Thanks so much Fluff.
That worked fantastically. What if i wanted to copy the first line and then the line the row was read from?
I tried this but no luck

ThisWorkbook.Sheets(Master).Range("A1").EntireRow.Copy ActiveSheet.Range("A1")
cell.EntireRow.Copy ActiveSheet.Range("A2") '<-- this was your line
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,219
Office Version
  1. 365
Platform
  1. Windows
That should work. In what way didn't it work?
 

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
173
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

the Errorhandling is envoked on that line. As far as I can tell, in the Locals (Master) is defined as Empty
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,219
Office Version
  1. 365
Platform
  1. Windows
If Master is the name of the sheet it should be wrapped in quotes.
 

Danny54

Board Regular
Joined
Jul 3, 2019
Messages
173
Office Version
  1. 365
Platform
  1. Windows
I overlooked the quotes.

Thanks for your help.. Have a Great Day!
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,219
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,140,997
Messages
5,703,633
Members
421,307
Latest member
morrden86

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
Top