jetimmins
10-12-2011, 05:36 AM
Hello there!
Warning before I start, I have very very limited programming experience and only started using VBA a couple days ago, so please forgive what I'm sure is sloppy code, and please be sensitive to this if you're kind enough to reply =)
I'm building a macro that when a button is clicked, it:
- Copies two ranges (to work around a merged cell, excel doesn't seem to like pasting with those around)
- Checks if a second workbook is open
- If it isn't, it opens it and pastes the values and formats into it
- If it is, it creates a new sheet in the second workbook and pastes into that
So far so good, I have this macro working
My problem is that when a new worksheet is created in the second workbook, the cell sizes are all default, and I need them all to match the sizes of the previous sheet, so all new sheets have the same cell sizes. Is there a simple(ish) way of achieving this, or even a horribly meticulous way of doing so?
I'd be grateful for any input <3
Undoubtedly amateur code below:
Sub Export()
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("PMUExport.xlsm") 'Set wb to the export file path
If wb Is Nothing Then GoTo 2 Else 'If the export file is not open, then go to marker 2, Otherwise...
Mybook = ActiveWorkbook.Name 'If the export file is open, create a new sheet and export
wb.Activate
Thatbook = ActiveWorkbook.Name
Worksheets.Add
Workbooks(Mybook).Activate
Range("B1:N82").Select
Selection.Copy
Workbooks(Thatbook).Activate
Range("B1:N82").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Workbooks(Mybook).Activate
Range("A4:A82").Select
Selection.Copy
Workbooks(Thatbook).Activate
Range("A4:A82").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlFormats
Cells(1, 1).Select
Workbooks(Mybook).Activate
Cells(1, 1).Select
Application.CutCopyMode = False
GoTo 3 'Then go to marker 3 (end of sub)
2 'Open export file if it's not open, then export
'Split
Mybook = ActiveWorkbook.Name
Workbooks.Open ("C:\Documents and Settings\jtimmins\My Documents\Quotes Project\PMUExport")
Thatbook = ActiveWorkbook.Name
Workbooks(Mybook).Activate
Range("B1:N82").Select
Selection.Copy
Workbooks(Thatbook).Activate
Range("B1:N82").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Workbooks(Mybook).Activate
Range("A4:A82").Select
Selection.Copy
Workbooks(Thatbook).Activate
Range("A4:A82").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlFormats
Cells(1, 1).Select
Workbooks(Mybook).Activate
Cells(1, 1).Select
Application.CutCopyMode = False
3 'Marker 3
End Sub
Warning before I start, I have very very limited programming experience and only started using VBA a couple days ago, so please forgive what I'm sure is sloppy code, and please be sensitive to this if you're kind enough to reply =)
I'm building a macro that when a button is clicked, it:
- Copies two ranges (to work around a merged cell, excel doesn't seem to like pasting with those around)
- Checks if a second workbook is open
- If it isn't, it opens it and pastes the values and formats into it
- If it is, it creates a new sheet in the second workbook and pastes into that
So far so good, I have this macro working
My problem is that when a new worksheet is created in the second workbook, the cell sizes are all default, and I need them all to match the sizes of the previous sheet, so all new sheets have the same cell sizes. Is there a simple(ish) way of achieving this, or even a horribly meticulous way of doing so?
I'd be grateful for any input <3
Undoubtedly amateur code below:
Sub Export()
Dim wb As Workbook
On Error Resume Next
Set wb = Workbooks("PMUExport.xlsm") 'Set wb to the export file path
If wb Is Nothing Then GoTo 2 Else 'If the export file is not open, then go to marker 2, Otherwise...
Mybook = ActiveWorkbook.Name 'If the export file is open, create a new sheet and export
wb.Activate
Thatbook = ActiveWorkbook.Name
Worksheets.Add
Workbooks(Mybook).Activate
Range("B1:N82").Select
Selection.Copy
Workbooks(Thatbook).Activate
Range("B1:N82").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Workbooks(Mybook).Activate
Range("A4:A82").Select
Selection.Copy
Workbooks(Thatbook).Activate
Range("A4:A82").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlFormats
Cells(1, 1).Select
Workbooks(Mybook).Activate
Cells(1, 1).Select
Application.CutCopyMode = False
GoTo 3 'Then go to marker 3 (end of sub)
2 'Open export file if it's not open, then export
'Split
Mybook = ActiveWorkbook.Name
Workbooks.Open ("C:\Documents and Settings\jtimmins\My Documents\Quotes Project\PMUExport")
Thatbook = ActiveWorkbook.Name
Workbooks(Mybook).Activate
Range("B1:N82").Select
Selection.Copy
Workbooks(Thatbook).Activate
Range("B1:N82").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlPasteFormats
Workbooks(Mybook).Activate
Range("A4:A82").Select
Selection.Copy
Workbooks(Thatbook).Activate
Range("A4:A82").Select
Selection.PasteSpecial Paste:=xlPasteValues
Selection.PasteSpecial Paste:=xlFormats
Cells(1, 1).Select
Workbooks(Mybook).Activate
Cells(1, 1).Select
Application.CutCopyMode = False
3 'Marker 3
End Sub