[help] Excel: Join Macros

goupon · Apr 3, 2014

Everything is working now.

Code:

Sub Macro1()

'
' Macro1 Macro
'
    Sheets("Details").Select
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AN$41"
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$18"
        .PrintTitleColumns = "$A:$G"
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&F"
        .CenterFooter = ""
        .RightFooter = "&D &P of &N"
        .LeftMargin = Application.InchesToPoints(0.2)
        .RightMargin = Application.InchesToPoints(0.2)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 70
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Range("H5").Select
    ActiveWindow.FreezePanes = True
    Columns("H:AN").Select
    Selection.NumberFormat = "#,##0_);[Red](#,##0)"
'
'
Sheets("Summary").Select
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AN$30"
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$18"
        .PrintTitleColumns = "$A:$G"
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        .LeftFooter = "&F"
        .CenterFooter = ""
        .RightFooter = "&D &P of &N"
        .LeftMargin = Application.InchesToPoints(0.2)
        .RightMargin = Application.InchesToPoints(0.2)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 70
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    Range("H5").Select
    ActiveWindow.FreezePanes = True
    Columns("H:AN").Select
    Selection.NumberFormat = "#,##0_);[Red](#,##0)"
End Sub

Code:

Sub Splitbook()
For Each sht In ThisWorkbook.Sheets
sht.Copy
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
ActiveWorkbook.SaveAs "C:\Users\Desktop\macro " & sht.Name & ".xlsx", FileFormat:=51
' Note: 51 = xlOpenXMLWorkbook (without macro's in 2007, .xlsx)
ActiveWorkbook.Close savechanges:=False
Next sht
End Sub

PkaLotus · Apr 3, 2014

it would be great if someone will be able to share it

Rei · Apr 3, 2014

Wish I can help you out Groupon, hopefully the other guys can.

thahappycamper · Apr 3, 2014

Goupon has strong technical abilities.

goupon · Apr 3, 2014

Sweet, I got it to work... and it rename to what I want

PkaLotus · Apr 3, 2014

goupon said:
Sweet, I got it to work... and it rename to what I want

goupon · Apr 3, 2014

Update:
Now fully work:

Code:

Sub Splitbook()
For Each sht In ThisWorkbook.Sheets
sht.Copy
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
ActiveWorkbook.SaveAs "C:\Users\Desktop\macro " & sht.Name & ".xlsx", FileFormat:=51
' Note: 51 = xlOpenXMLWorkbook (without macro's in 2007, .xlsx)
ActiveWorkbook.Close savechanges:=False
Next sht
End Sub

thahappycamper · Apr 3, 2014

@ Goupon

How are you in MS Excel?

goupon · Apr 4, 2014

thahappycamper said:
@ Goupon

How are you in MS Excel?

What u mean? I'm using excel like 95% of my work. 2% MS Access and 1% macro, 3% surfing lol

PkaLotus · Apr 4, 2014

goupon said:
What u mean? I'm using excel like 95% of my work. 2% MS Access and 1% macro, 3% surfing lol

wow you are so great Goupon

how I know who i should ask if i run into an issue

goupon · Apr 4, 2014

theary said:
wow you are so great Goupon how I know who i should ask if i run into an issue

U can ask abt the surfing lol.. sure np if i can help

Hamster · Apr 4, 2014

this reminds me, i need to buy excel tutorial book.

thahappycamper · Apr 5, 2014

Hamster said:
this reminds me, i need to buy excel tutorial book.

me too, I'd love to learn Excel just so damn hard to understand

Goupon, can you make us a simple tutorial, hehe (I'm kididng)

Hamster · Apr 5, 2014

thahappycamper said:
me too, I'd love to learn Excel just so damn hard to understand

Goupon, can you make us a simple tutorial, hehe (I'm kididng)

Maybe Goupon can suggest which book to buy

thahappycamper · Apr 5, 2014

Hamster said:
Maybe Goupon can suggest which book to buy

Goupon knows his shit ...

Goupon, participate in the arcade. I'm trying to get 16 participants. Just click on participation and play a few games man. No more than 5 minutes of your time bro.

goupon · Apr 7, 2014

Hamster: I don't know any excel book to recommend. I studied MS back in college and been using ever since. I use to be good in MS Access but now that i'm no longer using it, my skill is to the drain

Back in my old old job, I create Access database to do reconciliation between one system vs other system. Person prior to me did it manually and took them weeks to finish the work. After i was hired, took me not even an hour

cuz of that i got rise twice in one yr lol. I was famous ever since

Here's at my current job, it's just a relaxing place. No Access, pure excel and creating rpt for improvement/liable.

But if u need help and if i knew, sure i'll do my best

Hamster · Apr 7, 2014

Thanks Goupon. I have Excel book from college as well but it's too elementary. So I'm gonna look for one that's more advanced.

thahappycamper · Apr 8, 2014

goupon said:
Hamster: I don't know any excel book to recommend. I studied MS back in college and been using ever since. I use to be good in MS Access but now that i'm no longer using it, my skill is to the drain

Back in my old old job, I create Access database to do reconciliation between one system vs other system. Person prior to me did it manually and took them weeks to finish the work. After i was hired, took me not even an hour cuz of that i got rise twice in one yr lol. I was famous ever since

Here's at my current job, it's just a relaxing place. No Access, pure excel and creating rpt for improvement/liable.

But if u need help and if i knew, sure i'll do my best

Goupon knows his shit mang....

[help] Excel: Join Macros

goupon

Level 2

PkaLotus

Level 3

Rei

Level 3

thahappycamper

Level 3

goupon

Level 2

PkaLotus

Level 3

goupon

Level 2

thahappycamper

Level 3

goupon

Level 2

PkaLotus

Level 3

goupon

Level 2

Hamster

Level 2

thahappycamper

Level 3

Hamster

Level 2

thahappycamper

Level 3

goupon

Level 2

Hamster

Level 2

thahappycamper

Level 3