Code to insert data from another workbook

[Guest]

In Workbook A, I have a macro that imports data from another source. This
data is added into the next available column. Up to now I have been going
into Workbook A and copying and pasting 2 cells from this new data into
pasting it into Workbook B. Prior to pasting this data into Workbook B, I
also manually insert 2 new cells so that the old data is moved one column to
the right. I'm looking for suggestions of code to automate this. Thank
you!!!!!!!!!!!

 

[Guest]

Mary:
Assuming both workbooks are open when the macro runs, you could add
something like this to the macro code (where CopySheet and CopyRange give the
sheet name and range of cells you want to copy, PasteSheet and PasteRange
give the sheet name/column where you want to insert the cells, and using
"Workbook A and "Workbook B" as your workbook names - this really needs to be
the name as it appears in the title bar):

Workbooks("Workbook A").Sheets(CopySheet).Range(CopyRange).Copy
Workbooks("Workbook
B").Sheets(PasteSheet).Range(PasteRange).Insert(xlShiftToRight)
Workbooks("Workbook
B").Sheets(PasteSheet).Range(PasteRange).PasteSpecial(xlPasteValues)

Hope this does what you need.
K Dales

 

[Guest]

Thanks! This looks close. My only question is that the data that is being
copied is always going to be one more column to the right than the last time
this was run. Is there a way to tell it to look at the next available column?
It would be in the same row each time.

Thanks

 

[Guest]

There are a few ways to do what you need, it depends on what else (if
anything) as adjacent to the cells you will be using.
Possibilities:
- The CurrentRegion property of the range will give a new range that
consists of all adjacent non-empty cells. Getting this to count your columns
will only work if there are no other adjacent cells that extend further, but
assuming that is OK you can count the columns in the CurrentRegion like this:
UsedColumns = Range(CopyRange).CurrentRegion.Columns.Count
- You could perhaps use the CountA worksheet function if there is nothing
else (ever) to the right of your column in that particular row:
UsedColumns = WorksheetFunction.CountA(CopyRange.EntireRow)
- You could use VBA to step to the right until it finds a blank:
ColNo = 1
While Not IsEmpty(CopyRange.Offset(0,ColNo)).Range("A1")
ColNo = ColNo + 1
Wend
....

Hopefully one of these methods can be adapted to your workbook.