My apologies up front if this post contains too much information. I am new to VBA and this site.
After some difficulties trying to run a Macro I recorded, I have tried to break it up into smaller portions. One of those portions includes appending two columns of data, which will then be used to create a table. The data in these columns is coming from two other worksheets in the same workbook.
When the data is transferred directly by me, the shortened Macro works fine. However, when I use a formula to transfer the data, the Macro does not work. I would appreciate suggestions on how to edit the shortened Macro to either adjust for the formula or edit the VBA to transfer the data and then append, so I might then proceed with attempting to create the table. The code I have been using is:
Dim col As Range, _
found As Range
Dim currRow As Integer
currRow = ActiveSheet.Range("A:A").Find("", after:=ActiveSheet.Range("A1"), lookat:=xlWhole, searchdirection:=xlNext).Row
For Each col In ActiveSheet.UsedRange.Columns
If col.Column <> 1 Then
Set found = col.EntireColumn.Find("", after:=col.Cells(1, 1), lookat:=xlWhole, searchdirection:=xlNext)
Set found = ActiveSheet.Range(col.Cells(1, 1), found)
found.Copy
ActiveSheet.Cells(currRow, 1).PasteSpecial
currRow = currRow + found.Cells.Count - 1
End If
Next col
End Sub
I should have mentioned the columns of data being copied to the worksheet will have headers and vary in the number of rows from workbook to workbook. I will try to attach images of the start and desired endpoints.
The formulas are applied to 200 rows in each column. When I use the Macro on the data copied using the formula, it seems to append the 200 cells of formula from column 2 to the 200 cells of formula in column 1. The data stays in each column, and after my last data point in column 1 I now have blank cells down to row 400 that have the formula instead of data.
