Hi,
I have this macro to copy a row of data from shtB(bkData) to shtA(bkMaster),
if we find the part number

we want shtB. This is fairly straight forward.



If Trim(shtB.Cells(irow2, 1)) = PartNo Then
Fnd = True
shtB.Cells(irow2, 2).Resize(1, 12).Copy
bkMaster.ActiveSheet.Cells(irow + 2, 3).PasteSpecial
Paste:=xlPasteValues
End If



The original format in Excel file looks like this:
--------------------------------------------------
A B C D E F G H I J K
PartNo wk3 wk4 wk5 wk6 wk7 wk8 wk9 wk10 wk11 wk12


New column added in between work weeks:
---------------------------------------
A B C D E F G H I J K ...
PartNo wk3 wk4 Total wk5 wk6 wk7 wk8 Total wk9 wk10 ...



Now the bkMaster has added a few new columns in between. The column heading
is named "Total".

What I would like the program to do is that before the .pastespecial
command, the code will check the

heading in Row1. So that data from ShtB will be copied to the correct
column in shtA.

If heading = "wk" & some numbers, then paste the data.
If heading <> start with "wk", then it will skip/jump to the next column,
check the heading again,
if heading = "wk" & some numbers, then continue to paste the next piece of
data.


I don't know how do this in proper VBA code. Can someone help, please?

Thank you in advance.

Try the below

Dim lngCol As Long, intCount As Integer

If Trim(shtB.Cells(irow2, 1)) = PartNo Then
Fnd = True
lngCol = 2
Do
If bkmaster.ActiveSheet.Cells(1, lngCol) Like "wk*" Then
intCount = intCount + 1
bkmaster.ActiveSheet.Cells(irow + 2, lngCol) = _
shtB.Cells(irow2, 2).Resize(1, 12).Cells(intCount)
End If
lngCol = lngCol + 1
Loop Until bkmaster.ActiveSheet.Cells(1, lngCol) = ""
End If

If this post helps click Yes
---------------
Jacob Skaria


"choo" wrote:

> Hi,
> I have this macro to copy a row of data from shtB(bkData) to shtA(bkMaster),
> if we find the part number
>
> we want shtB. This is fairly straight forward.
>
>
>
> If Trim(shtB.Cells(irow2, 1)) = PartNo Then
> Fnd = True
> shtB.Cells(irow2, 2).Resize(1, 12).Copy
> bkMaster.ActiveSheet.Cells(irow + 2, 3).PasteSpecial
> Paste:=xlPasteValues
> End If
>
>
>
> The original format in Excel file looks like this:
> --------------------------------------------------
> A B C D E F G H I J K
> PartNo wk3 wk4 wk5 wk6 wk7 wk8 wk9 wk10 wk11 wk12
>
>
> New column added in between work weeks:
> ---------------------------------------
> A B C D E F G H I J K ...
> PartNo wk3 wk4 Total wk5 wk6 wk7 wk8 Total wk9 wk10 ...
>
>
>
> Now the bkMaster has added a few new columns in between. The column heading
> is named "Total".
>
> What I would like the program to do is that before the .pastespecial
> command, the code will check the
>
> heading in Row1. So that data from ShtB will be copied to the correct
> column in shtA.
>
> If heading = "wk" & some numbers, then paste the data.
> If heading <> start with "wk", then it will skip/jump to the next column,
> check the heading again,
> if heading = "wk" & some numbers, then continue to paste the next piece of
> data.
>
>
> I don't know how do this in proper VBA code. Can someone help, please?
>
> Thank you in advance.