• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

copy macro to copy from 1 sheet to another

Guys

I am after a vb scritp that will copy information from sheet 1 to sheet 2 of course this is a sample test sheet but I will be able to import into actual sheet.

Just to add the fields that refer to Name & No need to repeat the info per line were the address is so if omly 2 lines filled in the NAme and No appear on the 2 lines.

I will attach a sample excel file

If any one can help it would be a life saver

Thanks
EE-Test.xlsx
0
DarrenJackson
Asked:
DarrenJackson
  • 3
  • 2
1 Solution
 
jsdrayCommented:
Not sure if I fully understand...  Are you trying to transpose the information from columns into rows on another sheet?
0
 
DarrenJacksonAuthor Commented:
Yes sheet 1 is a form sheet 2 is a list
0
 
jsdrayCommented:
Call the MAINCOPY macro from each form page...each time run, it will make an new row entry...


Sub MainCopy()
'
' MainCopy Macro
'
SheetName = ActiveSheet.Name
WorkbookName = ActiveWorkbook.Name
CopyForm (SheetName)


'

End Sub

Sub CopyForm(MyForm)
'
' CopyForm Macro
'
'

    x = 1
    Sheets("Sheet2").Select

MyLoop:
        If Range("A" & x) = "" Then
   
        Sheets(MyForm).Select
        Range("K1").Select 'No
        Selection.Copy
        Sheets("Sheet2").Select
        Range("A" & x).Select
        ActiveSheet.Paste
       
        Sheets(MyForm).Select 'Name
        Range("B1").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("B" & x).Select
        ActiveSheet.Paste
       
        Sheets(MyForm).Select 'Address1
        Range("B4").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("C" & x).Select
        ActiveSheet.Paste
       
        Sheets(MyForm).Select 'Address2
        Range("B5").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("D" & x).Select
        ActiveSheet.Paste
       
        Sheets(MyForm).Select 'City
        Range("B6").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("Sheet2").Select
        Range("E" & x).Select
        ActiveSheet.Paste
       
        Application.CutCopyMode = False
        Sheets(MyForm).Select
        Range("A1").Select
    Else
        x = x + 1
        GoTo MyLoop
    End If
End Sub
0
 
jsdrayCommented:
Last was quick and dirty... here's a cleaner one...

Sub MainCopy()
'
' MainCopy Macro
'
SheetName = ActiveSheet.Name
WorkbookName = ActiveWorkbook.Name
CopyForm (SheetName)

MsgBox ("Done")
'

End Sub

Sub CopyForm(MyForm)
'
' CopyForm Macro
'
'

    x = 0


MyLoop:
    x = x + 1
    If Sheets("Sheet2").Range("A" & x).Value = "" Then
        Sheets("Sheet2").Range("A" & x).Value = Sheets(MyForm).Range("K1").Value 'No
        Sheets("Sheet2").Range("B" & x).Value = Sheets(MyForm).Range("B1").Value 'Name
        Sheets("Sheet2").Range("C" & x).Value = Sheets(MyForm).Range("B4").Value 'Address1
        Sheets("Sheet2").Range("D" & x).Value = Sheets(MyForm).Range("B5").Value 'Address2
        Sheets("Sheet2").Range("E" & x).Value = Sheets(MyForm).Range("B6").Value 'City
        Sheets(MyForm).Select
        Range("A1").Select
    Else
        GoTo MyLoop
    End If
End Sub
0
 
DarrenJacksonAuthor Commented:
Thankyou jsdray works a treat


Cheers and Thankyou :)
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now