Solved

VBA for copying Active Cell to another worksheet

Posted on 2016-08-25
6
399 Views
Last Modified: 2016-08-26
Okay, I'm looking to see if there's a vba formula that would allow a macro to copy 3 cell values (example B2, C2 and D2) from active cell range ("A1") and paste into another worksheet (inside the same workbook) cell A7/A8/A9 then print the newly structured data, and loop to the next cell and start over again. Just that I never know how many will be on the report so I was originally telling it to "Do While IsEmpty(ActiveCell.Range("A1")) = False" lol but nothing is working so figured i'd start from scratch and ask.

Not sure which direction to go on this one, and just looking to see if anyone knows, any help is greatly appreciated.

Thank you
EETEST.xlsb
0
Comment
Question by:NYANBCNY32
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41771232
Not sure what you mean. You can use something like this to copy to the bottom of the list in AddressCard

 Range(ActiveCell, ActiveCell.End(xlDown)).Copy
    With Sheets("AddressCard")
        .Cells(.Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
                                                      False, Transpose:=True
    End With

Open in new window


If you want to get the address from AddressCard then add a column on the left  for a company id number and use VLOOKUP
0
 
LVL 20

Expert Comment

by:Roy Cox
ID: 41771283
If you want to copy the address from the list here's a way with VLOOKUP. If this is what you mean then it can be adapted to not use empty cells, e.g if no Zip exists.
EETEST.xlsb
0
 

Author Comment

by:NYANBCNY32
ID: 41771718
The macro i'm looking to do would do the following steps:

1.) Copy/Transfer on a line by line basis Company Name/Address from the worksheets("AddressCard") to the designated cells in worksheets("Data") A7 = Company Name, A8 = Company Address and A9 = City State Zip. (The City/State/Zip i would Trim & Concatenate into one cell into column C on AddressCard before I ran the macro )

2.) Once it copies/transfers the data it would then print worksheets("Data").

3.) Move on to the next row and loop the whole process once again.

This was originally the coding I started to toy with, but it's really just whatever I've been able to find to put it all together so I know it probably looks horrible lol I know it does because it doesn't work.

Sub CompanyAddTransfer()
'
' CompanyAddTransferMacro
'
Do While IsEmpty(ActiveCell.Range("A1")) = False

Dim LName As Integer
Dim Add1 As Integer
Dim CSZ As Integer
Dim LtrName As Integer
Dim LtrAddl As Integer
Dim LtrCSZ As Integer



LName = ActiveCell.Range("A1").Value
Addl = ActiveCell.Range("B1").Value
CSZ = ActiveCell.Range("C1").Value
LtrName = Worksheets("Sheet1").Range("A7")
LtrAddl = Worksheets("Sheet1").Range("A8")
LtrCSZ = Worksheets("Sheet1").Range("A9")

LtrName = LName
LtrAddl = Addl
LtrCSZ = CSZ

Worksheets("Data").PrintOut

Loop
'
End Sub
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 31

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41771774
Does this work for you?

Sub CompanyAddTransfer()
Dim sws As Worksheet, dws As Worksheet
Dim x
Dim i As Long, lr As Long
Set sws = Sheets("AddressCard")
Set dws = Sheets("Data")
lr = dws.Cells(Rows.Count, 1).End(xlUp).Row
If lr < 2 Then
   MsgBox "There is no Data on AddressCard Sheet.", vbCritical, "Data Not Found!"
   Exit Sub
End If
x = sws.Range("A1").CurrentRegion.Value
For i = 2 To UBound(x, 1)
   With dws
      .Range("A7").Value = x(i, 1)
      .Range("A8").Value = x(i, 2)
      .Range("A9").Value = x(i, 3) & ", " & x(i, 4) & " " & x(i, 5)
      .PrintOut
   End With
Next i
End Sub

Open in new window

0
 

Author Closing Comment

by:NYANBCNY32
ID: 41771902
That was exactly what I needed.  Thank you.
0
 
LVL 31

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41772024
You're welcome. Glad to help.
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question