Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

copy data into borders to a new sheet

0

hi

I  created   the  headers  and  borders  . when  I  add a new sheet , also  I  add  some  lines  to  code  to  copy  specific   some  values  from specific  cells,  but  the  problem is   lefting    the  cells  contain  borders  and  fill  the  rows  doesn't  contain  borders  in added a new sheet

any  suggestion to fix  this  problem 

Private Sub Workbook_NewSheet(ByVal Sh As Object)
With Sh
    .Move After:=Sheets(Sheets.Count)
    .Range("A1:k12").Borders.Weight = xlMedium
    .Range("A1:k12").HorizontalAlignment = xlCenter
    With .Cells(1).Resize(1, 11)
        .Value = Array("ITEM NUMBER", "ITEM DESC", "QUANTITY", "UNIT PRICE", "TOTAL", "WHSE", "ACOUNT CODE", "BUSINESS UNIT", "DEPARTMENT", "WORK CENTER", "FLOCK")
        .Interior.ColorIndex = 53
        .Font.Bold = True
        .Font.Color = vbWhite
        .EntireColumn.AutoFit      
Dim sh1 As Worksheet
Set sh1 = Sheets("main")
lr = sh1.Range("a" & Rows.Count).End(xlUp).Row
      Sh.Range("a" & lr) = sh1.Range("e3").Value
    Sh.Range("d" & lr) = sh1.Range("e2").Value
    Sh.Range("f" & lr) = "DAT010"
    Sh.Range("g" & lr) = "1141000022"
    Sh.Range("h" & lr) = "JP-PROD."
    Sh.Range("i" & lr) = "JP-WIPDP"
    Sh.Range("j" & lr) = "JP-WIPWC"
    Sh.Range("k" & lr) = "Flock_4"
     End With
End With
End Sub
excel
MS Office Version: 2016
posted by: leap (rep: 46) on Mon Jul 12, 2021 at 10:08 am
Answer
Discuss

Answers

0
Selected Answer

Leap

I think you mean to add the data from the sheet "main" in the next available row in the new sheet (with headers and borders),

If so, the problem is that your variable lr is calculated as the last row of the main sheet (which happens to be ro 13, so outside your border area in the new sheet).

I've replaced lr with a variable Nwr (new row), caclulated as the last row on the new sheet PLUS 1 (so an empty row)- This will work if you add other rows (from columns in sheet main) using a loop around the setting of Nwr but stopping before the new line which autofits the populated table (rather than just the first row- that line is commneted out). Changes are in bold below and within the attached revised file:

Private Sub Workbook_NewSheet(ByVal Sh As Object)


With Sh

    .Move After:=Sheets(Sheets.Count)
    .Range("A1:k12").Borders.Weight = xlMedium
    .Range("A1:k12").HorizontalAlignment = xlCenter
    With .Cells(1).Resize(1, 11)
        .Value = Array("ITEM NUMBER", "ITEM DESC", "QUANTITY", "UNIT PRICE", "TOTAL", "WHSE", "ACOUNT CODE", "BUSINESS UNIT", "DEPARTMENT", "WORK CENTER", "FLOCK")
        .Interior.ColorIndex = 53
        .Font.Bold = True
        .Font.Color = vbWhite
        '.EntireColumn.AutoFit

Dim sh1 As Worksheet, Nwr As Long
Set sh1 = Sheets("main")

        Nwr = .Range("a" & Rows.Count).End(xlUp).Row + 1

        Sh.Range("a" & Nwr) = sh1.Range("e3").Value
        Sh.Range("d" & Nwr) = sh1.Range("e2").Value
        Sh.Range("f" & Nwr) = "DAT010"
        Sh.Range("g" & Nwr) = "1141000022"
        Sh.Range("h" & Nwr) = "JP-PROD."
        Sh.Range("i" & Nwr) = "JP-WIPDP"
        Sh.Range("j" & Nwr) = "JP-WIPWC"
        Sh.Range("k" & Nwr) = "Flock_4"

        .Range("A1:K" & Nwr).EntireColumn.AutoFit
     End With

End With

End Sub
Hope this works for you.
posted by: John_Ru (rep: 6142) on Mon Jul 12, 2021 at 12:26 pm
Discuss

Discussion

John
so  I  was  close .   thanks  so  much  for  your correcting 
leap (rep: 46) Jul 12, '21 at 2:52 pm
Leap

Yes you were very close, well done.

For the other items in Main, my appraoch would be to detect the last used column then create a loop from E to that, with step 5 for columns ab=nd transfer the data from rows 2 and 3 to Mw in the new sheet. Your approach is your choice of course.... you know best what you wanrt to do.

Thanks again for selecting my Answer.
John_Ru (rep: 6142) Jul 12, '21 at 3:13 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login