Marco Copy Cells one workbook to another

[Kyle82]

Hi,

I have this code to generate a report to a new sheet, from data on a seperate sheet. It all works fine, however it appears too big for excel to handle.

Is there anyway to condense the code I'm using and maybe make it into 1 macro. User input is currently 1 button.

Thanks in advance.

Dim strName As String
    

    strName = Inputbox(Prompt:="Please select the Item which will appear on the Report", Title:="Generate Report", Default:="0")

        If strName = "0" Or _
           strName = vbNullString Then

        Exit Sub

    Else

    Select Case strName

        Case "1"
            Case1
'there are over 100 case's for this register

Sub Case1()

    Dim A As String
    Dim B As String

'goes all the way to Dim P As String

Sheets("Register").Select

        Range("A3").Select

            A = ActiveCell.Offset(0, 27).Value
            B = ActiveCell.Offset(0, 28).Value

'goes all the way to P = ActiveCell ...

    
Sheets("Report").Select

        Range("A1:L43").Select
            Selection.Copy

        Range("A65536").End(xlUp).Select

            ActiveCell.Offset(1, 0).Select
            ActiveSheet.Paste
    
Sheets("Report").Select

            ActiveCell.Offset(8, 1).Value = A
            ActiveCell.Offset(9, 1).Value = B
'goes all the way to .Value = P

End Sub

I have over 100 cases, all in seperate sub's, and I think that is what is too large.

Thanks

 

[Kyle82]

after thought:

would it help if each case sub were in a seperate module? At the moment I have about 30 cases in the 1 module. Not sure if it would enable excel to allow it... will try it tomorrow.

 

[Kyle82]

I think I figured it out.

A2 is a field title "ID N°". The ID number start at 1 and go to whatever (say 100).
A3 is the first Item = ID N° 1. So when the user inputs 1, the strName becomes 1 therefore "ActiveCell.Offset(strName, 0).Select" will select the cell corresponding to the Item ID, meaning i wont need 100 seperate cases, as now I can just use the ActiveCell function.

Sub SelectData()

    Dim strName As String
    Dim A As String
    Dim B As String

    strName = Inputbox(Prompt:="Which Item N° do you want to Generate a Report for?", Title:="Generate Report", Default:="1")

        If strName = "0" Or _
           strName = vbNullString Then

        Exit Sub

    Else

    Select Case strName

        Case 1 To 100
            
            Range("A2").Select
                ActiveCell.Offset(strName, 0).Select

                A = ActiveCell.Offset(0, 27).Value
                B = ActiveCell.Offset(0, 28).Value
        
            Sheets("Report").Select
            Range("A1:L43").Select
                Selection.Copy
                
            Range("A3000").End(xlUp).Select
                
                ActiveCell.Offset(1, 0).Select
                ActiveSheet.Paste
            
            Sheets("Report").Select
            
                ActiveCell.Offset(8, 1).Value = A
                ActiveCell.Offset(9, 1).Value = B
            
    End Select
    
    End If

End Sub