Enter Range value and get report

[OK]

Hi All,

I hv a excel file with above 203 rows, and 54 columns, It is required
to view these perodically and generate report. I hv tried to make a
userform with two text boxes where user can enter the range row wise
only (e.g A1 and A53), after entering and clicking the command button
the data of this particular row range will be copied and pasted as
transposed in sheet 2 after which the user can print this sheet. Im
not too worried about the formats or print view stuff, just like to
make this work, I'll appreciate if anyone can help...

I tried the following code but getting errors,

Private Sub CommandButton1_Click()
'
'
Sheets("Sheet1").Select
Range("ActiveCell.Value = Cell_txt_1.Value:ActiveCell.Value =
Cell_txt_2.Value").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True



End Sub


Regards

OK

 

[Trevor Shuttleworth]

How about this for a basis:

Private Sub CommandButton1_Click()

Dim Cell_txt_1 As String
Dim Cell_txt_2 As String

Cell_txt_1 = "A1" ' for testing ... needs to come from TextBox
Cell_txt_2 = "A25" ' for testing ... needs to come from TextBox

With Sheets("Sheet1")
.Range(Cell_txt_1 & ":" & Cell_txt_2).Copy
With Sheets("Sheet2")
.Range("A1").PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
End With
End With

Application.CutCopyMode = False

End Sub

Regards

Trevor

 

[OK]

Trevor

Many thanks for the prompt reply, I still cannot manage to get this
code working right. I get erro, and debug points to the following
line...


.Range(Cell_txt_1 & ":" & Cell_txt_2).Copy

The code which I hv entered is as follows;


Private Sub Cell_txt_1_Change()

End Sub

Private Sub Cell_txt_2_Change()

End Sub


Private Sub CommandButton1_Click()

Dim Cell_txt_1 As String
Dim Cell_txt_2 As String


'Cell_txt_1 = "A1" ' for testing ... needs to come from TextBox
'Cell_txt_2 = "m1" ' for testing ... needs to come from TextBox

With Sheets("Sheet1")
.Range(Cell_txt_1 & ":" & Cell_txt_2).Copy
With Sheets("Sheet2")
.Range("A1").PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=True
End With
End With


Application.CutCopyMode = False

End Sub


Let me know where im making mistake.

Regards

OK

 

[Gary Keramidas]

give this a try, just something i threw together quickly. change the form and
sheet names.
i just created a simple form with 2 textboxes and a command button.
the command button calls the macro test in a code module. enter a starting cell
value in textbox1, end cell value in textbox2 and click command button 1.


Sub Test()
Dim ws As Worksheet, ws2 As Worksheet
Dim rng As Range
Dim firstAddress As String, lastAddress As String

Set ws = Worksheets("sheet1")
Set ws2 = Worksheets("sheet2")
ws2.UsedRange.Clear
firstAddress = UserForm1.TextBox1.Value
lastAddress = UserForm1.TextBox2.Value
Set rng = ws.Range(firstAddress & ":" & lastAddress)
rng.Copy _
ws2.Range("A1")

With ws2.PageSetup
.Orientation = xlLandscape
.CenterHorizontally = True
.FooterMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0.35)
.LeftMargin = Application.InchesToPoints(0.35)
.TopMargin = Application.InchesToPoints(0.6)
.BottomMargin = Application.InchesToPoints(0.3)
.FooterMargin = Application.InchesToPoints(0)
.PrintArea = ws2.UsedRange.Address
.HeaderMargin = Application.InchesToPoints(0.25)
.RightHeader = "&B&12 " & Date
.RightFooter = ""
.CenterFooter = ""
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
End With
ws2.PrintPreview
End Sub

 

[OK]

Thank you all...

code now working fine for me....

Thanks again....

Regards

Ok